[Previous] [Contents] [Next]
The LOCK TABLES and UNLOCK TABLES statements in MySQL
TABLES statement is used to lock the listed tables in either
WRITE mode. As discussed earlier, all tables that are accessed in the transaction must be locked in either
WRITE mode and must be listed in a single
A script that issues a
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
LOCK queue, any request in the
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
TABLES statements. Indeed, issuing a second
TABLES is the same as issuing an
TABLES to release all locks and then issuing the second
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.
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
DELAYED for insertion that is described in the MySQL manual.
Don't mix locking with
DELAYED for insertion operations. The
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.
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
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
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.
[Previous] [Contents] [Next]