Welcome to WebmasterWorld Guest from 34.229.126.29

Forum Moderators: open

Message Too Old, No Replies

MySQL Table Locking

Lock table, or use a view?

     
12:21 am on Sep 23, 2014 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Jan 12, 2006
posts:1309
votes: 0


I have a MySQL database with several tables. One table in particular will receive constant INSERTS. For example, the table will start with 10 rows, but will grow to 500+ rows as users add more content.

This table will also be queried often with a SQL statement to view the contents of the table. The number of users adding content will be small (approximately 25 users), but I am concerned about multiple INSERT operations clashing with multiple SELECT operations. Even though the number of users will be small, I think there will possibly be dozens of simultaneous INSERTS.

Should I leave table-level-locking to MySQL, or explicitly use the LOCK TABLE code to lock the table during INSERTS?

Also, will using a VIEW help avoid conflicts?
12:33 am on Sept 23, 2014 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:4998
votes: 47


Sounds like locking would not be an issue, when you're approaching hundreds or more inserts a second then it can become an issue.

A lot depends on your choice of storage engine, some lock at the table level and some lock more towards a row level.

One simple workaround is to do your inserts in batches. A VIEW would not make any difference I believe, VIEW's act pretty much like a stored SQL query, i.e. they use the same tables and data as regular tables and would hang if any of the tables in the VIEW were locked.
6:08 am on Sept 23, 2014 (gmt 0)

Full Member

5+ Year Member

joined:Aug 16, 2010
posts:257
votes: 21


If you use Innodb as the storage engine there is no problem with this load. You should mysql handle all the locking. I only use lock tables when i create a snapshot.

Perhaps you could enable the slow_log and watch the performance of the select statements?
2:00 pm on Sept 23, 2014 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Jan 12, 2006
posts:1309
votes: 0


My storage engine will be MyISAM which, I believe, uses table-level locking.

@brotherhood of LAN: I considered using a batch insert as a workaround; I just need to come up with a scheme to create it (probably from a temp table).

@bhukkel: I will definitely watch the performance of SELECT and INSERTS once everything is up and running.

Thanks.
2:04 pm on Sept 23, 2014 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:4998
votes: 47


Partitions might be viable for you, if you're able to split the table up on its primary/unique key. It requires one of the more recent versions of MySQL to avoid full table-locks on a MyISAM table.

[dev.mysql.com...]

So one way would be to just insert as normal, preferably per partition and then only a % of your table is locked at any one time.
2:38 pm on Sept 23, 2014 (gmt 0)

Full Member

5+ Year Member

joined:Aug 16, 2010
posts:257
votes: 21


Is there any special reason you use myisam? Specially with many inserts/update innodb is better because of row level locking.
2:47 pm on Sept 23, 2014 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Jan 12, 2006
posts:1309
votes: 0


@brotherhood of LAN, @bhukkel: No special reason. It was the default storage engine, so I didn't bother changing it. I will have complete control of the MySQL installation, so I am able to set it up however I want.

I'll have to consider the partition idea as well; I didn't even think about it before.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members