MySQL resource locking and performance go together much like movies and popcorn. Applying table locking at the right time and in the right way can yield very positive results, specifically in a noticeable speeding up of your SQL statements. By the same token, when improperly executed, locking can slow the database down to a crawl or even cause your entire system to grind to a halt. For this reason, it's crucial to know when and how to perform table locking. This article aims to cover both these points, while highlighting the pros and cons of using table locking under different circumstances.
The Benefits of Employing Table Locking
Every time you make a change to a table, MySQL must eventually commit that change to disk. Making a change to a table that has indexes requires even more work as MySQL needs to perform the additional step of updating the indexes. The effect of this additional step can be compounded when you make numerous changes to the data at one time, such as by performing an UPDATE to key (i.e., code) fields. In this case, MySQL might have to perform hundreds or even thousands of write operations.
When we lock a table, MySQL won't update the index keys for the locked table until we unlock it. That can result in substantial time savings. Depending on the complexity of the indexing, judicious use of table locking can speed things up drastically.
Know Your Storage Engine
How you use table locking depends to some extent on what storage engine you're using. If you're running InnoDB, the default storage engine in MySQL 5.6, MySQL automatically uses row-level locking so that multiple sessions and applications can read from and write to the same table simultaneously, without making each other wait. For this storage engine, it's best to avoid using locks at the table level because they only reduce concurrency, while not offering any extra protection.
For all storage engines other than InnoDB, MySQL does employ table locking. To get the best performance from these storage engines, use table locks for tables that are queried often and rarely inserted into or updated. Even transactions that affect a single row can result in deadlocks because write operations automatically set locks on all records that are part of the same index.
Options to Consider Before Table Locking
There are many ways besides table locking to minimize the frequency of deadlocks. These include committing your transactions often, because small transactions are less prone to collision. Here are a few others:
- Use the SHOW STATUS command to determine the cause of the deadlocks when they occur. That can help you to tune your application to avoid future deadlocks.
As of MySQL 3.23.33, you can analyze the table lock contention on your system by checking the Table_locks_waited and Table_locks_immediate status variables:
mysql> SHOW STATUS LIKE 'Table%';
| Variable_name | Value |
| Table_locks_immediate | 39 |
| Table_locks_waited | 12 |
- Always be prepared to re-execute a transaction if it fails due to a deadlock. Deadlocks are not dangerous since they protect your data.
- Write smaller SELECT statements so that they run faster and hence lock tables for a shorter time.
- Add new data to a temporary table and then add all the new rows in one INSERT.
Here is some code that you could use to perform many INSERT and SELECT operations on a table when concurrent inserts are not possible. It adds rows to a temporary table and only updates the real table with the records from the temporary table once in a while:
mysql> LOCK TABLES my_table WRITE, temp_table WRITE;
mysql> INSERT INTO my_table SELECT * FROM temp_table;
mysql> TRUNCATE TABLE temp_table;
mysql> UNLOCK TABLES;
Comparing Row and Table Level Locking
Like many factors in database performance tuning, whether row or table level locking is preferable depends to a large extent on how the tables are used. As such, different tables may require different lock types. Look at what mix of select and update statements are run against the database and from there you'll be in a good position to decide whether you want to use a storage engine with row-level locking or table locking.
The same applies for applications that interact with a database. For example, Web applications tend to execute a lot of selects. Others may insert into or update tables based on key values. Most applications that I've ever come across perform very few true deletes. Instead, an active_indicator flag is set accordingly. The default MySQL MyISAM engine is coincidentally very well suited to this type of usage.
How MySQL Manages Table Locking
MySQL uses pretty much the same table-locking method for both READ and WRITE locks:
- If there are no locks on the table, put a read/write lock on it.
- Otherwise, put the lock request in the read/write lock queue.
When a lock is released, the lock is first made available to other processes in the write lock queue, then to the threads in the read lock queue. So if you have many updates for a table, SELECT statements will wait until there are no more updates.
Implementing Your Own Table Locking
When handling table locking yourself, it's best to serialize your transactions. The correct way to use the LOCK TABLES command is to begin a transaction with “SET autocommit = 0” followed by LOCK TABLES. Once you've committed the transaction, call UNLOCK TABLES to remove the lock. For example, if you needed to write to table T1 and read from table T2, you could write the following:
LOCK TABLES T1 WRITE, T2 READ;
--Issue your read and write commands here...
Disadvantages of Table Locking
As alluded to above, table-level locking is not a silver bullet. It has both good and bad points. Here are some of their disadvantages:
- Table locking still allows multiple processes to read from a table at the same time, but if a process wants to write to a table, it must first get exclusive access, meaning it might have to wait for other sessions to finish with the table first. During write actions, all other sessions that want to access this particular table must wait until the write is done.
- Table locking runs into trouble when the disk runs out of free space and needs to make room before the session can proceed. When that happens, all sessions that want to access the affected table(s) are made to wait until more disk space becomes available.
- SELECT statements that take a long time to run prevent other sessions from updating the table in the meantime, making the other sessions appear slow or unresponsive. While a session waits for exclusive access to the table for updates, other sessions that issue SELECT statements will queue up behind it, reducing concurrency even for read-only sessions.
On any other storage engine than InnoDB, which utilizes row-level locking by default, issuing locks while updating tables can greatly improve your MySQL database's performance. Our goal here today was to clear up potential confusion around the issue of when and how to employ table locking. In an upcoming article, we'll be looking at the practical aspects of implementing table locking in more detail.
See all articles by Rob Gravelle