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 alsowait.
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
variableinnodb_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.
