Geek Answers Handbook

The rationale behind the difference between unique and unique indices when locking the next MySQL InnoDB key

MySQL InnoDB uses the next key lock for non-ideal indexes in transactions, which block both before and after the scanned index (which is because the MySQL manual cannot transmit in a clear way, the manual page on the next key lock says that only the gap preceding the scanned index (s) is blocked: http://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html ).

However, I do not understand the whole rationale for this ...

Used setting:

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

The first client to connect starts transaction A and issues the following request UPDATE:

UPDATE test set b = 10 where a = 10;

Executing the following queries from the following incoming connection: starting transaction B yields 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 seems that transaction B cannot insert rows where a - [5,15) (5 on - 15 exclude), as well as modify existing rows and set a (5, 15) (5 excluding - 15 excluding).

Now by changing the column ainstead PRIMARY KEY:

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

Repeated execution of the above in transaction B now gives the following results (inserts in lines 5 and 15 give an error about the duplicate key, therefore they are 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

, ( , phantom, phantom ). , , -).

:

MySQL 5.5.24, REPEATABLE READ.

+4
1

.:)

, .

). . MySQL , . , .

). , . innodb , , B + . , node, .

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

a = 15, a = 10, . .

+1

More articles:


All Articles