Understand the basics of locks and deadlocks in MySQL (Part II)
A deadlock is a situation when two or more transactions mutually hold and request a lock that the other needs. As a result, a cycle of dependencies is created and the transactions cannot proceed. By default, InnoDB automatically detects deadlocks and rolls back one transaction (the victim) to break the cycle. Normally, the transaction that infects a smaller number of rows will be picked.
Open two consoles on the command line, or open two SQL editors in DBeaver. Start one transaction in each console/editor and run the queries one by one as indicated below:
-- Transaction A
-- Step 1
SELECT * FROM study.student_scores WHERE id = 10 FOR SHARE;-- Transaction B
-- Step 2
DELETE FROM study.student_scores WHERE id = 10;
Transaction B will be blocked because the record with id equaling 10 is locked in SHARED (S) mode in transaction A. As the S lock is incompatible with the exclusive (X) lock requested by transaction B, it will be blocked until the S lock is released.
Let’s check what locks are currently granted or being waited in the transactions:
You can use this query to find the transaction id for your current transaction:
WHERE trx_mysql_thread_id = connection_id();
Let’s analyze the lock information from table
performance_schema.data_locks as shown above.
For transaction A, an intention shared (IS) lock is added to the whole table, which is required for the row-level S lock as we learn from the last post.
REC_NOT_GAP indicates that the S lock for record with id equaling 10 is a record lock, not a gap lock. The S and IS locks are granted for transaction A.
For transaction B, only the intention exclusive (IX) lock is granted, and the row-level X lock is still waiting. The IX lock can be granted because it is…