Understand the basics of locks and deadlocks in MySQL (Part II)

Let’s not get scared of MySQL deadlock anymore

7 min readNov 1, 2021

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.

Photo by marcos mayer (padlocks in steel cable) from Unsplash.

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
START TRANSACTION;
SELECT * FROM study.student_scores WHERE id = 10 FOR SHARE;-- Transaction B
-- Step 2
START TRANSACTION;
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:

SELECT
  ENGINE,
  ENGINE_TRANSACTION_ID,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  INDEX_NAME,
  LOCK_TYPE,
  LOCK_MODE,
  LOCK_STATUS,
  LOCK_DATA
FROM performance_schema.data_locks;

You can use this query to find the transaction id for your current transaction:

SELECT trx_id
FROM information_schema.innodb_trx
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…