If a thread obtains a READ lock, that thread and all other threads can only read from
those tables.
If a thread obtains a WRITE lock, it becomes the only thread with any access to those
tables. It can read and write, but no other threads can access the tables until it has
released the lock.
Issuing a LOCK TABLES command to request a lock isn't quite the same thing as obtaining one! There's
an important queuing process first, which has to occur before a lock can be granted to a thread.Queuing Lock Requests
A queuing mechanism exists so that, when a thread requests a LOCK, it may have to wait in line until any
other locks on the tables concerned are released. There's a WRITE queue and a READ queue, which work in
subtly different ways.
The following is the order of priorities:
When a WRITE lock is issued
If there are no locks currently on the table, the WRITE lock is granted without
queuing.
Otherwise, the lock is put into the WRITE lock queue.
When a READ lock is issued
If the table has no WRITE locks on it, the READ lock is granted without queuing.
Otherwise, the lock request is put into the READ lock queue.
Whenever a lock is released, threads in the WRITE locks queue are given priority over those in the
READ queue. Therefore, if a thread is requesting a WRITE lock, it will get it with minimal delay.There's a good reason for this. By giving priority to threads wanting to perform a write operation, MySQL
ensures that any updates to the database are processed as quickly as possible.
MySQL only grants a lock to a thread in the READ queue when there are no threads waiting in the
WRITE queue. However, there are ways to override this default behavior.LOW_PRIORITY WRITE
You may have an application for which it's more urgent for READ locks to be granted than WRITE locks.
Issuing a LOW_PRIORITY WRITE makes the queuing system behave the other way around: a WRITE
lock will have to wait until all READ locks have cleared their queue before the WRITE lock is granted.
However, if you write such an application with a busy stream of READs, you should ensure that there is
time in the system for the WRITEs to occur. Beware of performance risks because a WRITE may have to
wait some time until the thread can proceed.
SELECT HIGH_PRIORITY
Another way to influence the queuing policy is to use a SELECT HIGH_PRIORITY. If this statement is
issued, it allows the SELECT to read from the table, even if there is a WRITE lock in the queue.
It's wise to use this only for SELECT queries that must be done at once and are quick to complete.
Unlocking Tables
UNLOCK TABLES will unlock any tables held by the current thread. Tables will also be unlocked if the same
thread issues another LOCK command, or if the connection to the server is closed. Locks will not be released
because of any timeout.
Using LOCK TABLES
In MySQL, there are a couple of reasons you may need to use a LOCK.
Running Several Table Operations
As you've seen, a thread may issue a LOCK when it wants to ensure that nothing can access data in its
chosen tables between a SELECT and an UPDATE. The more SQL statements processed in a sequence
during which your thread needs exclusive access to the database, the more important it is to use LOCK
TABLES. This is probably the most common reason for using locks.
However, note that individual UPDATE statements are atomic. This means that no other thread can
interfere with the statement, no matter how complex it is. Therefore, if your query consists of a single
UPDATE statement, you don't need to LOCK because a single statement cannot be interfered with. Even
if your UPDATE affects 100,000 rows, no other thread can access these rows until the update is
complete.