Description:
We experience the following deadlock rather often, causing an increased need to re-try transaction and thereby slowing down the system quite a bit. The deadlock info from 'SHOW INNODB STATUS' listed below is an example of an instance of this problem we pulled from the logs today:
TRANSACTION 0 2216969, ACTIVE 0 sec, process no 28501, OS thread id 270403 inser
ting
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1024, undo log entries 2
MySQL thread id 64, query id 3383603 integ-node-03243.sea3.amazon.com 172.21.21.
48 mechturk update
INSERT INTO daily_statistic VALUES(300, 'A6KUHQOZ3E9I8', '2006-04-13', 1) ON DUP
LICATE KEY UPDATE value = value + 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37 page no 3 n bits 288 index `PRIMARY` of table `reqstats
/daily_statistic` trx id 0 2216969 lock mode S locks rec but not gap waiting
Record lock, heap no 83 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bits
0
0: len 4; hex 8000012c; asc ,;; 1: len 13; hex 41364b5548514f5a3345394938; a
sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d407; a
sc ! ;; 4: len 7; hex 00000000330084; asc 3 ;; 5: len 8; hex 8000000000
00049a; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 2216967, ACTIVE 0 sec, process no 28620, OS thread id 286791 inser
ting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1024, undo log entries 12
MySQL thread id 68, query id 3383632 integ-node-03243.sea3.amazon.com 172.21.21.
48 mechturk update
INSERT INTO daily_statistic VALUES(302, 'A6KUHQOZ3E9I8', '2006-04-13', 1) ON DUP
LICATE KEY UPDATE value = value + 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 37 page no 3 n bits 288 index `PRIMARY` of table `reqstats
/daily_statistic` trx id 0 2216967 lock_mode X locks rec but not gap
Record lock, heap no 37 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bits
0
0: len 4; hex 8000012d; asc -;; 1: len 13; hex 41364b5548514f5a3345394938; a
sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d407; a
sc ! ;; 4: len 7; hex 00000000330200; asc 3 ;; 5: len 8; hex 8000000000
0002fb; asc ;;
Record lock, heap no 56 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bits
0
0: len 4; hex 80000136; asc 6;; 1: len 13; hex 41364b5548514f5a3345394938; a
sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d407; a
sc ! ;; 4: len 7; hex 0000000033019c; asc 3 ;; 5: len 8; hex 80000001a1
3b92d1; asc ; ;;
Record lock, heap no 83 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bits
0
0: len 4; hex 8000012c; asc ,;; 1: len 13; hex 41364b5548514f5a3345394938; a
sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d407; a
sc ! ;; 4: len 7; hex 00000000330084; asc 3 ;; 5: len 8; hex 8000000000
00049a; asc ;;
Record lock, heap no 109 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bit
s 0
0: len 4; hex 80000141; asc A;; 1: len 13; hex 41364b5548514f5a3345394938; a
sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d407; a
sc ! ;; 4: len 7; hex 00000000330264; asc 3 d;; 5: len 8; hex 8000000000
000345; asc E;;
Record lock, heap no 131 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bit
s 0
0: len 4; hex 80000137; asc 7;; 1: len 13; hex 41364b5548514f5a3345394938; a
sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d407; a
sc ! ;; 4: len 7; hex 00000000330138; asc 3 8;; 5: len 8; hex 8000000000
0003a5; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 37 page no 3 n bits 288 index `PRIMARY` of table `reqstats
/daily_statistic` trx id 0 2216967 lock mode S locks rec but not gap waiting
Record lock, heap no 107 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bit
s 0
0: len 4; hex 8000012e; asc .;; 1: len 13; hex 41364b5548514f5a3345394938; a
sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d409; a
sc ! ;; 4: len 7; hex 00000000360084; asc 6 ;; 5: len 8; hex 8000000000
000975; asc u;;
*** WE ROLL BACK TRANSACTION (1)
What surprises me is that this would cause a deadlock because transaction 2 is holding an exclusive lock on the entry that both transactions are trying to get a shared lock on. Surely, that shared lock ought to be grantable in this situation. Based on the SQL statements in both transactions (both transactions would have contained anywhere from 1 to 1000 statements), I'd expect that transaction 1 used to have a mode X lock on the entry, which it dropped in order to try to reclaim it as a mode S lock. I just don't see how this can be a deadlock... Two transactions waiting for the same entry to lock in S mode seems perfectly valid.
Or am I reading this info completely wrong?
How to repeat:
The problem seems to be somewhat timing dependent and I have not been able to 100% reproduce it at will in a test environment, but something that tends to cause it to show up is running two threads each issueing a single transaction that contains 1000 statements like the following:
INSERT INTO stat(id, name, value) VALUES(1, 'X', '2006-05-01', 1) ON DUPLICATE KEY UPDATE value = value + 1
The tables involved are defined as:
CREATE TABLE stat_type (
id INT(11) NOT NULL,
name VARCHAR(40) NOT NULL,
PRIMARY KEY(id),
INDEX(name)
);
CREATE TABLE stat (
id INT(11) NOT NULL,
name VARCHAR(40) NOT NULL,
date DATE NOT NULL,
value BIGINT NOT NULL DEFAULT 0,
PRIMARY KEY (id, name, date),
CONSTRAINT FK_stat_id FOREIGN KEY(id) REFERENCES stat_type(id)
);
It seems to be relevant to the problem popping up to already have a fair amount of data in the stat table.
Suggested fix:
Unknown - though it seems like allowing lock conversion from X to S within a transaction ought to be automatically allowed, and not require releasing the X lock and then competing to get a new lock.
Description: We experience the following deadlock rather often, causing an increased need to re-try transaction and thereby slowing down the system quite a bit. The deadlock info from 'SHOW INNODB STATUS' listed below is an example of an instance of this problem we pulled from the logs today: TRANSACTION 0 2216969, ACTIVE 0 sec, process no 28501, OS thread id 270403 inser ting mysql tables in use 1, locked 1 LOCK WAIT 9 lock struct(s), heap size 1024, undo log entries 2 MySQL thread id 64, query id 3383603 integ-node-03243.sea3.amazon.com 172.21.21. 48 mechturk update INSERT INTO daily_statistic VALUES(300, 'A6KUHQOZ3E9I8', '2006-04-13', 1) ON DUP LICATE KEY UPDATE value = value + 1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 37 page no 3 n bits 288 index `PRIMARY` of table `reqstats /daily_statistic` trx id 0 2216969 lock mode S locks rec but not gap waiting Record lock, heap no 83 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bits 0 0: len 4; hex 8000012c; asc ,;; 1: len 13; hex 41364b5548514f5a3345394938; a sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d407; a sc ! ;; 4: len 7; hex 00000000330084; asc 3 ;; 5: len 8; hex 8000000000 00049a; asc ;; *** (2) TRANSACTION: TRANSACTION 0 2216967, ACTIVE 0 sec, process no 28620, OS thread id 286791 inser ting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 9 lock struct(s), heap size 1024, undo log entries 12 MySQL thread id 68, query id 3383632 integ-node-03243.sea3.amazon.com 172.21.21. 48 mechturk update INSERT INTO daily_statistic VALUES(302, 'A6KUHQOZ3E9I8', '2006-04-13', 1) ON DUP LICATE KEY UPDATE value = value + 1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 37 page no 3 n bits 288 index `PRIMARY` of table `reqstats /daily_statistic` trx id 0 2216967 lock_mode X locks rec but not gap Record lock, heap no 37 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bits 0 0: len 4; hex 8000012d; asc -;; 1: len 13; hex 41364b5548514f5a3345394938; a sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d407; a sc ! ;; 4: len 7; hex 00000000330200; asc 3 ;; 5: len 8; hex 8000000000 0002fb; asc ;; Record lock, heap no 56 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bits 0 0: len 4; hex 80000136; asc 6;; 1: len 13; hex 41364b5548514f5a3345394938; a sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d407; a sc ! ;; 4: len 7; hex 0000000033019c; asc 3 ;; 5: len 8; hex 80000001a1 3b92d1; asc ; ;; Record lock, heap no 83 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bits 0 0: len 4; hex 8000012c; asc ,;; 1: len 13; hex 41364b5548514f5a3345394938; a sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d407; a sc ! ;; 4: len 7; hex 00000000330084; asc 3 ;; 5: len 8; hex 8000000000 00049a; asc ;; Record lock, heap no 109 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bit s 0 0: len 4; hex 80000141; asc A;; 1: len 13; hex 41364b5548514f5a3345394938; a sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d407; a sc ! ;; 4: len 7; hex 00000000330264; asc 3 d;; 5: len 8; hex 8000000000 000345; asc E;; Record lock, heap no 131 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bit s 0 0: len 4; hex 80000137; asc 7;; 1: len 13; hex 41364b5548514f5a3345394938; a sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d407; a sc ! ;; 4: len 7; hex 00000000330138; asc 3 8;; 5: len 8; hex 8000000000 0003a5; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 37 page no 3 n bits 288 index `PRIMARY` of table `reqstats /daily_statistic` trx id 0 2216967 lock mode S locks rec but not gap waiting Record lock, heap no 107 PHYSICAL RECORD: n_fields 6; 1-byte offs TRUE; info bit s 0 0: len 4; hex 8000012e; asc .;; 1: len 13; hex 41364b5548514f5a3345394938; a sc A6KUHQOZ3E9I8;; 2: len 3; hex 8fac8d; asc ;; 3: len 6; hex 00000021d409; a sc ! ;; 4: len 7; hex 00000000360084; asc 6 ;; 5: len 8; hex 8000000000 000975; asc u;; *** WE ROLL BACK TRANSACTION (1) What surprises me is that this would cause a deadlock because transaction 2 is holding an exclusive lock on the entry that both transactions are trying to get a shared lock on. Surely, that shared lock ought to be grantable in this situation. Based on the SQL statements in both transactions (both transactions would have contained anywhere from 1 to 1000 statements), I'd expect that transaction 1 used to have a mode X lock on the entry, which it dropped in order to try to reclaim it as a mode S lock. I just don't see how this can be a deadlock... Two transactions waiting for the same entry to lock in S mode seems perfectly valid. Or am I reading this info completely wrong? How to repeat: The problem seems to be somewhat timing dependent and I have not been able to 100% reproduce it at will in a test environment, but something that tends to cause it to show up is running two threads each issueing a single transaction that contains 1000 statements like the following: INSERT INTO stat(id, name, value) VALUES(1, 'X', '2006-05-01', 1) ON DUPLICATE KEY UPDATE value = value + 1 The tables involved are defined as: CREATE TABLE stat_type ( id INT(11) NOT NULL, name VARCHAR(40) NOT NULL, PRIMARY KEY(id), INDEX(name) ); CREATE TABLE stat ( id INT(11) NOT NULL, name VARCHAR(40) NOT NULL, date DATE NOT NULL, value BIGINT NOT NULL DEFAULT 0, PRIMARY KEY (id, name, date), CONSTRAINT FK_stat_id FOREIGN KEY(id) REFERENCES stat_type(id) ); It seems to be relevant to the problem popping up to already have a fair amount of data in the stat table. Suggested fix: Unknown - though it seems like allowing lock conversion from X to S within a transaction ought to be automatically allowed, and not require releasing the X lock and then competing to get a new lock.