2

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 ?

Community
  • 1
  • 1
Suren Aznauryan
  • 984
  • 10
  • 24
  • Read the manual & some textbook(s) re locking used with MVCC. MVCC uses locking but it doesn't use it the way that non-MVCC DBMSs do. Its locking is not according to the obvious scheme that corresponds to the SQL phenomena prevention regime. Anyway you should be justifying your exectations by referencing the manual, then we can tell you where you went wrong & right; otherwise we have to just rewrite the manual with a bespoke tutorial, which is "too broad". – philipxy Nov 23 '18 at 20:32
  • What I've wrote in my question about how is locking done in this case(record locks on all records in clustered index plus gap locks before every index record and after last index record) i've read in manual where it is written how innodb does locking in Repeatable Read isolation level, but i didn't find why in this particular case locking is done in a way it is done. I'm asking a concrete question about why in this case locking is done that way, what phenomana Innodb tries to prevent ? Can you bring me an example? There should be a reason why is it locking whole table – Suren Aznauryan Nov 24 '18 at 16:25

1 Answers1

1

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.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • So it is done for preventing Phantom Reads, right ? In my case that kind of locking ensures that after the `DELETE` statement execution until the end of my transaction whenever I execute `SELECT FROM test WHERE quantity=10;` I will always see an empty result set. – Suren Aznauryan Nov 25 '18 at 17:08
  • The articles you gave the links for are completely clear to me. The only thing is not clear is why in case I have described the whole table is locked with clustered index row locks and gap locks everywhere. Why some tx2 cannot insert, delete, update any record in a table after tx1 executes`delete` statement with a condition on non-indexed column(assuming that tx1 is still in progress) ? There should be a reason, right ? What could happen otherwise ? I think I need some examples of some possible phenomena(or some bad stuff in general) happening in case if whole table is not locked in this case – Suren Aznauryan Nov 25 '18 at 22:34
  • I'm reading that book already :) Sorry, maybe I'm not clear on my question, but still I didn't get the response for it. What I'm asking is simple: what phenomena does the "locking the whole table" prevent here ? If it is the Phantom Read phenomena, then it is not clear to me how Phantom Read could possibly occur in this case if the whole table would not be locked. – Suren Aznauryan Nov 26 '18 at 16:37
  • It's the Phantom Read yes. According to the Phantom Read diagram found in the book, you need to prevent INSERTS into the range you selected. If you can't lock the index, you need to lock the table. In fact, that's the only solution to prevent this anomaly on Read COMMITTED on Oracle and PostgreSQL since Select For UPDATE will lock the existing tuples only, not future ones. – Vlad Mihalcea Nov 26 '18 at 17:13