Search code examples
mysqldatabaselockingrelational-databasedatabase-administration

What Phenomena does MySQL try to prevent by locking the whole table upon executing Delete statement with the condition on a non-indexed column

Using the MySQL isolation level of Repeatable Read.

Given table test having non-indexed column quantity:

id    |     quantity
--------------------
1     |      10
2     |      20
3     |      30

Tx1 executes 1st, note it is not committed yet, meaning that all the acquired locks are not released yet.

Tx1:

START TRANSACTION;
DELETE FROM test WHERE quantity=10;

Now executing Tx2

Tx2:

START TRANSACTION;
INSERT INTO test(quantity) VALUES (40);
COMMIT;

For Tx2 I get the following result:

Lock wait timeout exceeded; try restarting transaction

I understand that, as the quantity column is not indexed, the delete statement does a full table scan, locks all the rows( doesn't matter the where condition matches or not) and also applies gap locks before every and after the last index record in a Clustered Index resulting in a fully blocked table hence the insert statement from tx2 cannot acquire a lock for a row to be inserted.

From MySQL manual(for Repeatable Read isolation level):

  • For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it.

  • For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range (this is used in my case).

Taking into account that the locking in any given isolation level is applied for preventing the phenomenas I'm a little bit confused what is the reason to block the whole table in this case, I mean what kind of phenomena is prevented with blocking the whole table in this case ?

Solution

  • By default, InnoDB uses consistent snapshots in Repeatable Read isolation level, meaning that you get repeatable reads for both tuples and ranges.

    Even if the SQL standard says that Phantom Reads are prevented by Serializable and that Repeatable Read might not prevent it.

    For more details about how gap locking works, check out this post written by Percona.