MySQL Table Locking

Forum Moderators: open

Message Too Old, No Replies

MySQL Table Locking

Lock table, or use a view?

celgins

12:21 am on Sep 23, 2014 (gmt 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?

brotherhood of LAN

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

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.

bhukkel

6:08 am on Sep 23, 2014 (gmt 0)

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?

celgins

2:00 pm on Sep 23, 2014 (gmt 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.

brotherhood of LAN

2:04 pm on Sep 23, 2014 (gmt 0)

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.

bhukkel

2:38 pm on Sep 23, 2014 (gmt 0)

Is there any special reason you use myisam? Specially with many inserts/update innodb is better because of row level locking.

celgins

2:47 pm on Sep 23, 2014 (gmt 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.