Geek Answers Handbook

MySQL concurrent insertion causing deadlock outside of (explicit) transaction

I am trying to debug the following scenario: There are 2 parallel processes, inserting the exact same row into the table with a unique constraint. This is done outside of the explicit transaction (although I assume InnoDB treats this as a single w / auto commit statement inside?)

The scheme is as follows:

CREATE TABLE locks (
    id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    lock_uid varchar(255) NOT NULL,
    count smallint(6) NOT NULL,
    processor_id varchar(255) DEFAULT NULL,
    created_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    updated_at timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    PRIMARY KEY (id),
    UNIQUE INDEX locks_lock_uid_unique (lock_uid)
)

As you can see, there is a unique index in lock_uidto prevent multiple rows with the same value as in the table.

Commands that are executed (for context, they are taken from the general query log for complete sanity, there are no other operators in any of the threads outside the sort command):

Theme 1:

insert into `locks` (`lock_uid`, `count`, `processor_id`, `created_at`, `updated_at`) 
values ('11161567', '0', NULL, '2017-11-07 10:46:36', '2017-11-07 10:46:36')

Theme 2:

insert into `locks` (`lock_uid`, `count`, `processor_id`, `created_at`, `updated_at`) 
values ('11161567', '0', NULL, '2017-11-07 10:46:36', '2017-11-07 10:46:36')

This led to the following deadlock:

LATEST DETECTED DEADLOCK
------------------------
2017-11-07 10:46:36 0x2ac88f791700
*** (1) TRANSACTION:
TRANSACTION 6089510736, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 177584962, OS thread handle 47059008030464, query id 13109086103 ec2-34-232-58-13.compute-1.amazonaws.com 34.232.58.13 appserver update
insert into `locks` (`lock_uid`, `count`, `processor_id`, `created_at`, `updated_at`) values ('11161567', '0', NULL, '2017-11-07 10:46:36', '2017-11-07 10:46:36')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6403 page no 4 n bits 176 index locks_lock_uid_unique of table `core`.`locks` trx id 6089510736 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 107 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 636865636b6f75745265636f7665727950726f636573735f313131363137; asc 111617; (total 32 bytes);
1: len 8; hex 0000000003266637; asc      &f7;;

*** (2) TRANSACTION:
TRANSACTION 6089510734, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 177584971, OS thread handle 47040888903424, query id 13109086092 ec2-34-237-3-244.compute-1.amazonaws.com 34.237.3.244 appserver update
insert into `locks` (`lock_uid`, `count`, `processor_id`, `created_at`, `updated_at`) values ('11161567', '0', NULL, '2017-11-07 10:46:36', '2017-11-07 10:46:36')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6403 page no 4 n bits 176 index locks_lock_uid_unique of table `core`.`locks` trx id 6089510734 lock mode S
Record lock, heap no 104 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 636865636b6f75745265636f7665727950726f636573735f313131363135; asc 111615; (total 32 bytes);
1: len 8; hex 0000000003266632; asc      &f2;;

Record lock, heap no 105 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 636865636b6f75745265636f7665727950726f636573735f313131363135; asc 111615; (total 32 bytes);
1: len 8; hex 0000000003266634; asc      &f4;;

Record lock, heap no 107 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 636865636b6f75745265636f7665727950726f636573735f313131363137; asc 111617; (total 32 bytes);
1: len 8; hex 0000000003266637; asc      &f7;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6403 page no 4 n bits 176 index locks_lock_uid_unique of table `core`.`locks` trx id 6089510734 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 107 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 30; hex 636865636b6f75745265636f7665727950726f636573735f313131363137; asc 111617; (total 32 bytes);
1: len 8; hex 0000000003266637; asc      &f7;;

*** WE ROLL BACK TRANSACTION (2)

(, MySQL Duplicate Key Error), , . .

  • 2 ?
  • T2 S-, X, , T1?
+6
2

2 ?

https://dev.mysql.com/doc/refman/5.7/en/innodb-autocommit-commit-rollback.html

InnoDB . autocommit, SQL

, .

T2 S, X, , T1 ?

. , :-) , . , mysql. heres :

https://github.com/mysql/mysql-server/blob/5.7/storage/innobase/lock/lock0lock.cc#L7236

DeadlockChecker::notify(const lock_t* lock) const
{
    ut_ad(lock_mutex_own());

    start_print();

    print("\n*** (1) TRANSACTION:\n");

    print(m_wait_lock->trx, 3000);

    print("*** (1) WAITING FOR THIS LOCK TO BE GRANTED:\n");

    print(m_wait_lock);

    print("*** (2) TRANSACTION:\n");

    print(lock->trx, 3000);

    print("*** (2) HOLDS THE LOCK(S):\n");

    print(lock);

    /* It is possible that the joining transaction was granted its
    lock when we rolled back some other waiting transaction. */

    if (m_start->lock.wait_lock != 0) {
        print("*** (2) WAITING FOR THIS LOCK TO BE GRANTED:\n");

        print(m_start->lock.wait_lock);
    }

    DBUG_PRINT("ib_lock", ("deadlock detected"));
}

, , -, . Heres :

:

create table test (id int primary key, val int not null unique) engine=innodb;
insert into test values (1, 1), (2, 2), (3, 3);

3 :

1> begin;
2> begin;
3> begin;


1>insert into test values (1, 1);
2>insert into test values (1, 1); (will hang)
3>insert into test values (1, 1); (will hang)


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

1>show engine innodb status;
...
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-11-15 23:21:47 0x700000d95000
*** (1) TRANSACTION:
TRANSACTION 2336, ACTIVE 8 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 8, OS thread handle 123145316831232, query id 58 localhost 127.0.0.1 root update
insert into test values (1, 1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 3 n bits 72 index PRIMARY of table `test`.`test` trx id 2336 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 2335, ACTIVE 13 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 5, OS thread handle 123145316552704, query id 57 localhost root update
insert into test values (1, 1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 26 page no 3 n bits 72 index PRIMARY of table `test`.`test` trx id 2335 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 26 page no 3 n bits 72 index PRIMARY of table `test`.`test` trx id 2335 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

+2

- .

: :

 CREATE TABLE `temp_table` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `u_id` INT NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `u_id` (`u_id`)
 )
 COLLATE='latin1_swedish_ci'
 ENGINE=InnoDB;

2,4,7 u_id . .

.

1:

T1: temp_table (u_id) ('5');

T2: temp_table (u_id) ('6');

, (S X). 5 6 (4-7), T1 T2.

2:

T1: temp_table (u_id) ('5');

T2: temp_table (u_id) ('5');

T1 (4-7), , T2 , (5-7), T1, T2 T1.

, T1 , T2 , - T1 , T2 .

Mysql: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

0

More articles:


All Articles