Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rationale behind difference between unique and non-unique indexes in MySQL InnoDB next-key locking

MySQL InnoDB uses next-key locking on non-unique indexes in transactions whereby both the gap before and after the scanned index(es) are locked (which btw the MySQL manual fails to convey in a clear manner, the manual page on next-key locks says that only the gap preceding the scanned index(es) is locked: http://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html).

However, I fail to understand the entire rationale behind this...

Used setup:

CREATE TABLE test (a int, b int, index (a));
INSERT INTO test VALUES (5,5), (10,10), (15,15);

First client to connect starts transaction A and issues the following UPDATE query:

UPDATE test set b = 10 where a = 10;

Running the following queries from next incoming connection starting transaction B gives the following results:

INSERT INTO test VALUES(5,5); //On hold
INSERT INTO test VALUES(9,9); //On hold
INSERT INTO test VALUES(14,14); //On hold
INSERT INTO test VALUES(4,4); //Works
INSERT INTO test VALUES 15,15); //Works
UPDATE test SET a = 1 WHERE a = 5; //Works
UPDATE test SET a = 8 WHERE a = 5; //On hold
UPDATE test SET a = 7 WHERE a = 15; //On hold
UPDATE test SET a = 100 WHERE a = 15; //Works

It appears transaction B cannot insert rows where a is [5,15) (5 incl. - 15 excl.) nor modify existing rows and set a to be (5, 15) (5 excl. - 15 excl.).

Now, changing the column a to have a PRIMARY KEY instead:

ALTER TABLE test DROP INDEX a;
ALTER TABLE test ADD PRIMARY KEY (a);

Redoing the above running in transaction B now gives the following results (insertions to row 5 and 15 gives an error about duplicate key which is why they're not included):

INSERT INTO test VALUES(9,9); //Works
INSERT INTO test VALUES(14,14); //Works
INSERT INTO test VALUES(4,4); //Works
INSERT INTO test VALUES(10,10); //On hold
UPDATE test SET a = 1 WHERE a = 5; //Works
UPDATE test SET a = 8 WHERE a = 5; //Works
UPDATE test SET a = 7 WHERE a = 15; //Works
UPDATE test SET a = 100 WHERE a = 15; //Works
UPDATE test SET a = 10 WHERE a = 15; //On hold
UPDATE test SET a = 100 WHERE a = 10; //On hold

The behaviour with a primary key seems totally comprehensible and I don't question it (even though the lack of gap locks, using the rationale behind using gap locks to prevent phantom reads, wouldn't prevent phantom reads). I don't question this behaviour at all, I just have a hard time understanding the way regular indexes are dealt with and why they are dealt with in different ways).

Questions:

  1. Is the reason for using next-key locks that we want to prevent phantom reads (which seems to imply adhering to the rule that a SELECT query shouldn't return different results throughout a transaction in isolation level REPEATABLE READ) or is it because InnoDB deduces that the user might want to make an insertion close to the result of the query (which would then be a heuristic service to the user)? A third reason might be that the overall system principle seems to be to lock whatever rows that the query results in and that InnoDB does this without consideration (which would then adhere to some overall principle about rules for concurrency). From http://dev.mysql.com/doc/refman/5.7/en/innodb-next-key-locking.html it seems that next-key locking is used to prevent phantom reads only when the WHEREclause has a condition like a > 10 where it would make sense, but if so, why is next-key locking applied as well when the WHERE clause is specificly addressing certain rows? Maybe there are several disjunct reasons?
  2. Given the reason for next-key locking, why is it necessary to have a different behaviour when the column has a unique versus a non-unique index? At least the first two reasons suggested above don't seem to call for this even though the third might if InnoDB has to search more rows when the column has a non-unique index. Otherwise, to me, it seems as likely that one would want to insert a close-by row no matter if the column has a non-unique index or a unique index... On the other hand, when updating a row, there is no reason to believe that the user would want to insert a row close by so why not lock the entire table why you're at it...?
  3. Why does row a = 5 get locked for INSERT but not for UPDATE? It's as though there are two lock principles at play at the same time, one that locks modification of existing rows and one that locks insertions and that the existing row a = 5 is not locked but the insertion of row a = 5 is locked. Is this correct and if so, why is index 5 included in the gap lock for insertions?

My version of MySQL is 5.5.24 and I used the default isolation level REPEATABLE READ.

like image 570
fast-reflexes Avatar asked Oct 06 '14 11:10

fast-reflexes


People also ask

What is the difference between unique and non unique indexes?

In addition to enforcing the uniqueness of data values, a unique index can also be used to improve data retrieval performance during query processing. Non-unique indexes are not used to enforce constraints on the tables with which they are associated.

What is next key lock?

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record. InnoDB performs row-level locking in such a way that when it searches or scans a table index, it sets shared or exclusive locks on the index records it encounters.

Which of the following level of locking is used by InnoDB?

Shared and Exclusive Locks The two standard row-level locks are share locks(S) and exclusive locks(X). A shared lock is obtained to read a row, and allows other transactions to read the locked row, but not to write to the locked row. Other transactions may also acquire their own shared locks.

How does MySQL locking work?

MySQL enables client sessions to acquire table locks explicitly for the purpose of cooperating with other sessions for access to tables, or to prevent other sessions from modifying tables during periods when a session requires exclusive access to them. A session can acquire or release locks only for itself.


1 Answers

Your questions are too many. :)

I'm not a database expert and I just give you some hints.

a). index constraint is not necessarily a uniqueness one. MySQL uses gap locks when the condition columns have no index or not a uniqueness one. Because primary key is a uniqueness index, so it just locks the selected records.

b). when you update the index column, actually the records need to reindex. Because innodb uses the clustered index, which means that the record is at the leaf of the primary index B+ tree. So when it needs to find a position to place the updated index node, the database needs to grant the lock request.

UPDATE test SET a = 10 WHERE a = 15; //On hold

There is no lock at a = 15, but when you'd like to place the index at a =10, where there is a existing lock. So it holds.

like image 82
Chao Avatar answered Oct 08 '22 16:10

Chao