PHP —

The LOCK TABLES and UNLOCK TABLES statements in MySQL

The LOCK TABLES statement is used to lock the listed tables in either READ or WRITE mode. As discussed earlier, all tables that are accessed in the transaction must be locked in either READ or WRITE mode and must be listed in a single LOCK TABLES statement.

A script that issues a LOCK TABLES statement is suspended until all locks listed are successfully obtained. There is no time limit in waiting for locks. For locks that can't be immediately obtained-because the lock is held by another user or an operation is running on the table already-the request is placed at the back of either the write- or read-lock queue for the table, depending on the lock required. The write-lock queue has priority over the read-lock queue, so a user who wants a write lock obtains it when it becomes available, regardless of how long another user has been waiting in the read-lock queue. This is a design decision in MySQL that gives priority to database modifications over database queries.

MySQL is designed to give writing priority over reading. Regardless of how long a user has been queued in the READ LOCK queue, any request in the WRITE LOCK queue receives priority. This can lead to a problem called starvation, where a transaction never completes because it can't obtain the required locks. However, since most web database applications read from databases much more than they write, and locks are required in only a few situations, starvation is very uncommon in practice.

If low-priority writing is essential to an application, a LOW_PRIORITY option can be prefixed before the WRITE clause. If a transaction is queued for a LOW_PRIORITY WRITE, it receives the lock only when the READ LOCK queue is empty and no other users are reading from the table. Again, consideration of possible starvation is important.

Locks can't be progressively obtained through several LOCK TABLES statements. Indeed, issuing a second LOCK TABLES is the same as issuing an UNLOCK TABLES to release all locks and then issuing the second LOCK TABLES. There are good reasons for the strictness of this related to a well-known locking problem called deadlock, which we don't discuss here. However, MySQL is deadlock-free because of the strictness and functionality of the LOCK TABLES and UNLOCK TABLES statements.[1]

[1] Deadlock is a difficult problem. As recently as Version 3.22.23 of MySQL, there were bug fixes to MySQL to avoid deadlocking problems in the DBMS.

MySQL has a feature called INSERT DELAYED for insertion that is described in the MySQL manual.

Don't mix locking with INSERT DELAYED for insertion operations. The INSERT DELAYED process is carried out by the MySQL DBMS at a later time-under its own control-and the locks held by the user can't be used by the DBMS. INSERT DELAYED should be used only in situations in which locking isn't required.

Locking for performance

Locking is primarily designed to ensure that concurrent transactions can execute safely. However, locking is also a useful performance tool to optimize the performance of a particular transaction.

Consider, for example, a situation where we urgently require a complex report on the stock in the winestore that uses a slow SELECT statement with a GROUP BY, ORDER BY, and HAVING clause, and that joins together many tables. With other users running queries and using system resources, this query may run even slower. A solution is to use LOCK TABLES with the WRITE option to stop other users running queries or database updates, and to have exclusive access to the database for the query duration. This permits better optimization of the query processing by the DBMS, dedication of all the system resources to the query, and faster disk access.

The downside of locking for performance is the reduction in concurrent access to the database. Users may be inconvenienced by slow responses or timeouts from the web database application. Locking for performance should be used sparingly.