This is another article in a series of articles titled “A few notes …” in which I will be posting some important information about locking concepts, different types of locks and what locks table engines support. Just like the previous article, the purpose of this article is to highlight important aspects that you should have in the back of your mind when developing applications.

Classes of locks

There are two classes of locks:

Read Lock (Shared Lock)

Read locks as the name suggests, are locks taken on resources to provide consistent read by blocking writes to that resource. The read locks are also known as shared locks because many clients can acquire read locks on the same resource simultaneously. The other thing to know is that, while read lock is in place, no write lock can be acquired on that resource.

Following is how MySQL will grant read lock:

  • if there is no write lock set on the resource grant the read lock immediately
  • if there is a write lock on the resource, put the lock in the read lock queue

Write Lock (Exclusive Lock)

Write lock is taken on a resource when it needs to be modified. There can be only one write lock on a resource at a given time. While a write lock is held on a resource, all other read lock requests must also wait.

Following is how MySQL will grant write lock:

  • if there is no (read/write) lock on the resource, grant the write lock immediately
  • if there is a lock on the resource, put the write lock in the write lock queue

Priority of Read and Write Lock

Write lock has a higher priority than read lock. When a resource is unlocked, and if there are lock requests waiting in the queue, then the lock is granted in the following manner:

  • grant the lock first to the request waiting in the write lock queue
  • if there is no lock request for the resource in the write lock queue, then grant the lock to the first request in the read lock queue

Now let’s have a brief overview of the locking mechanism of two popular table engines.

Locking in MyISAM

MyISAM supports table-level locking and does not support row-level locking. Because of this behavior, MyISAM is typically well suited for applications which have a high percentage of reads as compared to writes. But because MyISAM supports locking at a high level, the memory needed for locking is typically less, say when you compare it to row-level locks.

However, MyISAM does allow concurrent INSERTs in some situations!

Concurrent INSERTs

If there are no holes in MyISAM table, then INSERTs happen at the end of the table, and in such a situation INSERTs and SELECTs are allowed to happen concurrently on the same table, with INSERT needing no write locks. But there is a caveat, if there are many INSERT requests, then the INSERTs are done serially, which implies that only one INSERT can execute concurrently with SELECTs on the same table. Now what exactly are hole? Holes are produced when rows are deleted and/or updated in the middle of a MyISAM table.

Locking in InnoDB

InnoDB unlike MyISAM supports both table-level locking as well as row-level locking, which allows for a more fine-grained control over the resource to be locked. Row-level locking allows InnoDB to be extremely efficient in case of heavy write-load.
InnoDB also has a very nice feature MVCC, which allows for non-locking consistent reads. This is achieved by having different snapshots of data available to different transactions. However, if you have the transaction isolation level set to SERIALIZABLE then plain SELECTs are automatically converted to locking SELECTs.

However, AUTO_INCREMENT columns need table-level locks.

Table-level AUTO_INC locks

INSERTs into AUTO_INCREMENT column need table-level lock for the duration of the INSERT statement. This is so that the INSERTs are safe for statement-based replication. However, as of MySQL >= 5.1, which also introduced row-based replication, this behavior is configurable using the variable innodb_autoinc_lock_mode, but you still need to be using row-based replication for the INSERTs to be safe for replication. Set the value of innodb_autoinc_lock_mode to 2, and then INSERT like statements will not take any table-level locks.

This post might not have been entirely exhaustive on the topic of locking, but I hope it provided you with enough understanding of locking to help you in designing applications that need them!