Index deadlock outside of transaction.
Hi,
I'm hoping that someone can explain why MySQL generated a deadlock under a scenario that I didn't think could cause deadlocks.
This is the table that the deadlock is appearing on.
CREATE TABLE `premiumplatform`.`serverrequestqueue` (
`serverRequestQueueID` int(10) unsigned NOT NULL auto_increment,
`serverURL` varchar(255) character set latin1 NOT NULL,
`postContent` varchar(2048) character set latin1 NOT NULL,
`outqueueID` int(10) unsigned NOT NULL,
`status` int(10) unsigned NOT NULL,
`msisdn` bigint(20) unsigned NOT NULL,
`postContentCRC` int(10) unsigned NOT NULL,
PRIMARY KEY (`serverRequestQueueID`),
KEY `Index_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
One thread is doing the query:
insert into premiumplatform.serverrequestqueue (serverURL,
postContent,
outqueueID,
status,
msisdn,
postContentCRC)
The second thread is doing the query
update
premiumplatform.serverrequestqueue
set status = 1
where serverRequestQueueID in ( 103, 104, 105, 106, 107, 108, 109, 110, 111 )
Only the second thread is doing the query inside a transction. I realise the update query is very innefficient and will be doing a full table scan - I will fix it at some point in the future.
The full dump of the deadlockinfo is below but the relevant parts are:
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 602 n bits 216 index `Index_status` of table `premiumplatform/serverrequestqueue` trx id 0 30504012 lock_mode X locks gap before rec insert intention waiting
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2297 n bits 72 index `PRIMARY` of table `premiumplatform/serverrequestqueue` trx id 0 30504009 lock_mode X waiting
The things I'm confused about are:
1) Both the insert and update are being treated as transactions - presumably this is due to the way they work internally?
2) Transaction 1 apparently doesn't hold any locks according to the InnoDB output. Why then doesn't it just wait for the lock on 'Index_status' instead of causing a deadlock straight away. Does it have some sort of phantom lock on the primary index?
3) How do I stop this from happening? I know I could put deadlock detection code around these SQL queries, however I would have to put deadlock code around all inserts which I shouldn't have to do.
btw I should have mentioned the global isolation level is set to REPEATABLE-READ.
cheers
Dan
------------------------
LATEST DETECTED DEADLOCK
------------------------
090306 16:47:14
*** (1) TRANSACTION:
TRANSACTION 0 30504012, ACTIVE 0 sec, process no 2634, OS thread id 1141827936 inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1216, undo log entries 1
MySQL thread id 27306199, query id 209111640 localhost mysqluser update
insert into premiumplatform.serverrequestqueue (serverURL,
postContent,
outqueueID,
status,
msisdn,
postContentCRC) values ( 'http://sms.example.com/cgi-bin/test.php';, '.....the remote request....') )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 602 n bits 216 index `Index_status` of table `premiumplatform/serverrequestqueue` trx id 0 30504012 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00000001; asc ;; 1: len 4; hex 00000003; asc ;;
*** (2) TRANSACTION:
TRANSACTION 0 30504009, ACTIVE 0 sec, process no 2634, OS thread id 1141963104 fetching rows, thread declared inside InnoDB 306
mysql tables in use 1, locked 1
14 lock struct(s), heap size 3024, undo log entries 48
MySQL thread id 27306197, query id 209111643 localhost mysqluser Updating
update premiumplatform.serverrequestqueue
set status = 1
where serverRequestQueueID in ( 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149 )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 602 n bits 216 index `Index_status` of table `premiumplatform/serverrequestqueue` trx id 0 30504009 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 00000001; asc ;; 1: len 4; hex 00000003; asc ;;
Record lock, heap no 98 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000000; asc ;; 1: len 4; hex 00000068; asc h;;
Record lock, heap no 99 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000000; asc ;; 1: len 4; hex 00000066; asc f;;
Record lock, heap no 102 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000000; asc ;; 1: len 4; hex 00000067; asc g;;
Record lock, heap no 104 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000000; asc ;; 1: len 4; hex 00000069; asc i;;
Record lock, heap no 105 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000000; asc ;; 1: len 4; hex 0000006a; asc j;;
...
...[Redundant data snipped]
...
Record lock, heap no 148 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 4; hex 00000000; asc ;; 1: len 4; hex 00000095; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 2297 n bits 72 index `PRIMARY` of table `premiumplatform/serverrequestqueue` trx id 0 30504009 lock_mode X waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
0: len 4; hex 00000096; asc ;; 1: len 6; hex 000001d1744c; asc tL;; 2: len 7; hex 80000008f20110; asc ;; 3: len 30; hex 687474703a2f2f736d732e6469616c6f6775652e6e65742f6367692d6269; asc http://www.example.com;...(truncated); 4: len 30; hex 26582d45332d5265706c792d506174683d485454503a636f6e6e65637469; asc ...theremoterequest...;...(truncated); 5: len 4; hex 00000449; asc I;; 6: len 4; hex 00000000; asc ;; 7: len 8; hex 0000000e4ea7685c; asc N h\;; 8: len 4; hex 2ea60da6; asc . ;;
*** WE ROLL BACK TRANSACTION (1)
Edited 2 time(s). Last edit at 03/08/2009 06:31AM by Daniel Ackroyd.