Geek Answers Handbook

MySQL Lock in Duplicate Key Error

From docs :

If an error occurs with duplicate keys, a general blocking of the duplicate index record is set. This use of shared locks may result in several sessions trying to insert the same row if another session already has an exclusive lock. This can happen if another session deletes the row.

Following the example in the docs,

Assume that the InnoDB t1 table has the following structure:

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

Now suppose that three sessions perform the following operations:

Session 1:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

Session 1:

ROLLBACK;

1 . 2 3 , . 1 , 2 3. , 2 3 : - , .

:

1) , . , , T1 1, 1. , T2 , INNODB , PK ( = 1) T2 ? T2 , PK.

2) T2 T3 ? ?

+4
3

1) , . , , T1 1, 1. , T2 , INNODB , PK ( = 1) T2 ? T2 .

, /. , , . , :

  • session 1 ,
  • 2 3 , 1, 2 3 .

2) T2 T3 ? ?

, 2 3 , . , 1 , 2 3 . . , . , , .

0

  • 2 3: INNODB , ( = 1), .

    SHOW ENGINE INNODB STATUS 2 3:

    ------------
TRANSACTIONS
------------
Trx id counter 2079155
Purge done for trx n:o < 2079150 undo n:o < 0 state: running but idle
History list length 594
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 2079154, ACTIVE 21 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 540, OS thread handle 0x7ff989386700, query id 1683 localhost root update
INSERT INTO t1 VALUES(1)
------- TRX HAS BEEN WAITING 21 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4190 page no 3 n bits 72 index `PRIMARY` of table `temp`.`t1` trx id 2079154 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000001fb9af; asc       ;;
 2: len 7; hex 9c000001d30110; asc        ;;

------------------
---TRANSACTION 2079153, ACTIVE 43 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 541, OS thread handle 0x7ff989355700, query id 1680 localhost root update
INSERT INTO t1 VALUES(1)
------- TRX HAS BEEN WAITING 43 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 4190 page no 3 n bits 72 index `PRIMARY` of table `temp`.`t1` trx id 2079153 lock mode S locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000001fb9af; asc       ;;
 2: len 7; hex 9c000001d30110; asc        ;;

    ( 1) 2 : ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction, 3: Query OK, 1 row affected

  • 2 3 , , , , . 3 .

    : , , , . .

    : , . , . InnoDB , REPEATABLE READ, concurrency, , , , .

0

2:

2) T2 T3 ? ?

, .

0

More articles:


All Articles