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

Let’s not get scared of MySQL deadlock anymore

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…