MySQL Locks (and a bit of the Query Cache)

September 10th, 2008 by Leandro Morgado

MySQL uses locks for concurrency control. Whenever a client/thread acquires a lock, it will have exclusive access to that table or row (depending on the granularity of the lock). Other clients however, will be prevented from writing and possibly reading to/from the locked resource. The two main existing locks are:

READ LOCK – A read lock will allow the other clients to read from the locked resource but not write to it.
WRITE LOCK – a write lock will prevent the other clients from reading or writing to the locked resource.

Also, different storage engines have different lock granularity. For example, MyISAM will lock the whole table while InnoDB will only lock the rows it needs.

Let’s try this out using a MyISAM table. We open two concurrent sessions and in the first lock the City table from the world database (get it here).

Session 1:

mysql> LOCK TABLE City READ;
Query OK, 0 rows affected (0.19 sec)

Session 2:

mysql> SELECT * FROM City LIMIT 1;
+----+------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+----+------+-------------+----------+------------+
|  1 |      | AFG         | Kabol    |    1780000 |
+----+------+-------------+----------+------------+
1 row in set (1.09 sec)
 
mysql> DELETE FROM City WHERE ID=1;

Here we can see that in Session 2, we are able to read from the locked table, but the query just hangs when trying to write. This happens because Session 1 has acquired a read lock on the table. As soon as Session 1 releases the lock, Session 2 is able to write to the table.

Session 1:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.05 sec)

Session 2:

mysql> DELETE FROM City WHERE ID=1;
Query OK, 1 row affected (1 min 39.07 sec)

We can see that the lock was in place for over a minute before Session 1 released it and Session 2 was able to grab it. Now let’s see what happens with a WRITE LOCK.

Session 1:

mysql> LOCK TABLE City WRITE;
Query OK, 0 rows affected (0.05 sec)

Session 2:

mysql> DELETE FROM City WHERE ID=2;
Query aborted by Ctrl+C
ERROR 1317 (70100): Query execution was interrupted
mysql> SELECT * FROM City LIMIT 1;

In the example above I tried to write to City, saw that it hung waiting for the lock and then aborted the query. I then tried to do a read. Due to the write lock, Session 2 can’t do a read or a write until Session 1 releases the lock:

Session 1:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

Session 2:

+----+----------+-------------+----------+------------+
| ID | Name     | CountryCode | District | Population |
+----+----------+-------------+----------+------------+
|  2 | Qandahar | AFG         | Qandahar |     237500 |
+----+----------+-------------+----------+------------+
1 row in set (25.64 sec)

One thing that can confuse you is the following situation. Say you read something from a table before issuing any locks:

Session 2:

mysql> SELECT * FROM City LIMIT 1;
+----+----------+-------------+----------+------------+
| ID | Name     | CountryCode | District | Population |
+----+----------+-------------+----------+------------+
|  2 | Qandahar | AFG         | Qandahar |     237500 |
+----+----------+-------------+----------+------------+
1 row in set (0.00 sec)

Then you write lock the table (preventing reads and writes from other sessions) and try the read again:

Session 1:

mysql> LOCK TABLE City WRITE;
Query OK, 0 rows affected (0.00 sec)

Session 2:

mysql> SELECT * FROM City LIMIT 1;
+----+----------+-------------+----------+------------+
| ID | Name     | CountryCode | District | Population |
+----+----------+-------------+----------+------------+
|  2 | Qandahar | AFG         | Qandahar |     237500 |
+----+----------+-------------+----------+------------+
1 row in set (0.00 sec)

Wow! Session 2 was able to read the City table even though Session 1 has write locked it! What the hell is happening? The truth is that MySQL’s query cache is tricking you. The query cache gets checked beforehand and if the query is cached, it won’t even need to look at the table or care about locks. One way to test this is by issuing a different read query that’s not in cache:

Session 2:

mysql> SELECT * FROM City LIMIT 2;

It hangs as expected, waiting for the write lock to be released. Remember that MySQL does a hash on the SQL query and if it’s different (even in case), it won’t use the query cache. We can also try the same query after emptying the query cache:

Session 2:

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT * FROM City LIMIT 1;

Once again, this leaves us waiting on the lock.

Concurrency is complicated stuff and there is a lot of ground to cover. There are other ways to do locks (eg: GET_LOCK(), SELECT … FOR UPDATE) with different purposes and applications. You can find out a little bit more in the excellent MySQL documentation (1 2 3)

The query cache is very handy and allows you to boost performance greatly. As with any cache, it can be confusing, so you should remember that it will serve out cached results even when you don’t expect them (it can turn it off though). You can find more info here.

One thing you can do to get prevent your queries being served from the query cache is to issue them like this:

mysql> SELECT SQL_NO_CACHE * FROM City LIMIT 2;

I’ll close off with some notes about locking:

  1. WRITE locks have higher priority than READ locks
  2. When you issue a second LOCK TABLE, it will implicitly unlock all previously held locks.
  3. To lock mutiple tables, you need to lock them simultaneously. Eg:LOCK TABLE City WRITE, Country READ;

Happy lock picking!

Leave a Reply

You must be logged in to post a comment.