Review on MYSQL Locks

We have seen so many times locked tables in show process list, obviously it's panic to see locked tables as such cases finally stop your application causes Apache's threads count huge number, In this post we discuss about type of locks and how can we avoid them. Before going start  I’m happy to say I’m not a MySQL expert in every aspect of MySQL, specifically internals where I have not had the detailed time to read the code, and understanding all internal workings.

The main important thing is instrumenting and monitor your mysql in better way.

1. Slow Query Log
2. General Query Log
3. Binary Log
4. Connectors
5. Process-list
6. Engine status
7. Status variables

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

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.


Imagine The Scenario


0.00 seconds A select query (ie. read-only) accesses the table, it will take around 2 seconds to complete.
0.01 seconds Another select query accesses the table, it takes no time and will complete in an instant as it can run in  parallel.
0.02 seconds An insert, delete or update query (ie. write) attempts to write to that very same table before the first select has completed.
0.03 seconds A select query comes in, again waiting for the first select to complete.
0.04 seconds Another select query comes in, again waiting for the first select to complete.
0.05 seconds Yet another select query comes in, again waiting for the first select to complete.
1.99 seconds (~1000 queries later) Yet another select query comes in, again waiting for the first select to complete. And so on and so forth.

The queries in red are blocked and have to wait for the first select to complete before they can be executed. The selects in red are blocked by the write operation in orange which is in turn blocked by
the long-running select in green. This is what causes the table_locks_waited value to grow.

The following items describe some ways to avoid or reduce contention caused by table locking:

• Try to get the SELECT statements to run faster so that they lock tables for a shorter time. You might have to create some summary tables to do this.
• Start mysqld with --low-priority-updates. For storage engines that use only table-level locking (such as MyISAM, MEMORY,and MERGE), this gives all statements that update (modify) a table lower priority than SELECT statements. In this case,the second SELECT statement in the preceding scenario
• would execute before the UPDATE statement, and would not need to wait for the first SELECT to finish.
• To specify that all updates issued in a specific connection should be done with low priority, set the
• low_priority_updates server system variable equal to 1.
• To give a specific INSERT, UPDATE, or DELETE statement lower priority, use the LOW_PRIORITY attribute.
• To give a specific SELECT statement higher priority, use the HIGH_PRIORITY attribute. 
• Start mysqld with a low value for the max_write_lock_count system variable to force MySQL to temporarily elevate the priority of all SELECT statements that are waiting for a table
• after a specific number of inserts to the table occur. This permits READ locks after a certain number of WRITE locks.
• If you have problems with INSERT combined with SELECT, consider switching to MyISAM tables, which support concurrent SELECT and INSERT statements.
• If you mix inserts and deletes on the same table, INSERT DELAYED may be of great help.
• If you have problems with mixed SELECT and DELETE statements, the LIMIT option to DELETE may help.
• Using SQL_BUFFER_RESULT with SELECT statements can help to make the duration of table locks shorter.
• You could change the locking code in mysys/thr_lock.c to use a single queue. In this case, write locks and read locks would have the same priority, which might help some applications.