MySQL Transaction Isolation Levels and Locks
Join the DZone community and get the full member experience.Join For Free
Originally written by Lim Han
Recently, an application that my team was working on encountered problems with a MySQL deadlock situation and it took us some time to figure out the reasons behind it. This application that we deployed was running on a 2-node cluster and they both are connected to an AWS MySQL database. The MySQL db tables are mostly based on InnoDB which supports transaction (meaning all the usual commit and rollback semantics) as well as row-level locking that MyISAM engine does not provide. So the problem arose when our users, due to some poorly designed user interface, was able to execute the same long running operation twice on the database.
As it turned out, due to the fact that we have a dual node cluster, each of the user operation originated from a different web application (which in turn meant 2 different transaction running the same queries). The deadlock query happened to be a “INSERT INTO T… SELECT FROM S WHERE” query that introduced shared locks on the records that were used in the SELECT query. It didn’t help that both T and S in this case happened to be the same table. In effect, both the shared locks and exclusive locks were applied on the same table. An attempt to explain the possible cause of the deadlock on the queries could be explained by the following table. This is based on the assumption that we are using a default REPEATABLE_READ transaction isolation level (I will explain the concept of transaction isolation later)
Assuming that we have a table as such
The following is a sample sequence that could possibly cause a deadlock based on the 2 transactions running an SQL query like “INSERT INTO T SELECT FROM T WHERE … “ :
|Time||Transaction 1||Transaction 2||Comment|
|T1||Statement executed||Statement executed. A shared lock is applied to records that are read by selection|
|T2||Read lock s1 on Row 10-20||The lock on the index across a range. InnoDB has a concept of gap locks.|
|T3||Statement executed||Transaction 2 statement executed. Similar shared lock to s1 applied by selection|
|T4||Read lock s2 on Row 10-20||Shared read locks allow both transaction to read the records only|
|T5||Insert lock x1 into Row 13 in index wanted||Transaction 1 attempts to get exclusive lock on Row 13 for insertion but Transaction 2 is holding a shared lock|
|T6||Insert lock x2 into Row 13 in index wanted||Transaction 2 attempts to get exclusive lock on Row 13 for insertion but Transaction 1 is holding a shared lock|
The above scenario occurs only when we use REPEATABLE_READ (which introduces shared read locks). If we were to lower the transaction isolation level to READ_COMMITTED, we would reduce the chances of a deadlock happening. Of course, this would mean relaxing the consistency of the database records. In the case of our data requirements, we do not have such strict requirements for strong consistency. Thus, it is acceptable for one transaction to read records that are committed by other transactions.