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:
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 WHERE
clause 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?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
.
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.
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.
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With