Description:
I get deadlock when multiple threads try to insert a row into one table.
Unfortunately I am not the owner of the code so I can not paste the exact transaction queries.
From innotop sample deadlock1:
________________________________________________ Deadlock Transactions ________________________________________________
CXN ID Timestring User Host Victim Time Undo LStrcts Query Text
cpmwd3 16582 2009-06-02 23:01:05 dlx cpm No 00:00 0 5 insert into mailpreference (auto_check_in
cpmwd3 19179 2009-06-02 23:01:05 dlx cpm Yes 00:00 0 5 insert into mailpreference (auto_check_in
CXN ID Waiting Mode DB Table Index Special Ins Intent
cpmwd3 4432 1 X dlx mailpreference PRIMARY 1
cpmwd3 10907 0 X dlx mailpreference PRIMARY 0
cpmwd3 10907 1 X dlx mailpreference PRIMARY 1
Info from show innodb status (sample deadlock2)
------------------------
LATEST DETECTED DEADLOCK
------------------------
090602 22:24:47
*** (1) TRANSACTION:
TRANSACTION 0 14934965, ACTIVE 0 sec, process no 24262, OS thread id 1142745408 inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1216
MySQL thread id 4394, query id 2555179 cpm-wd4.pl-lab.lucent.com 10.155.155.14 dlx update
insert into mailpreference (auto_check_interval, auto_insert_signature, block_external_images, play_sound, real_name, reply_quoting, reply_to, save_outgoing_messages, request_receipt_with_new_mail, sending_receipt, spellcheck_on_send, permanent_delete, account_name, user_id) values (0, 0, 1, 1, '', 1, '', 0, 0, 0, 0, 1, null, 83803)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 512 page no 496 n bits 440 index `PRIMARY` of table `dlx/mailpreference` trx id 0 14934965 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 0 14934963, ACTIVE 0 sec, process no 24262, OS thread id 1143810368 inserting, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1216
MySQL thread id 4500, query id 2555188 cpm-wd4.pl-lab.lucent.com 10.155.155.14 dlx update
insert into mailpreference (auto_check_interval, auto_insert_signature, block_external_images, play_sound, real_name, reply_quoting, reply_to, save_outgoing_messages, request_receipt_with_new_mail, sending_receipt, spellcheck_on_send, permanent_delete, account_name, user_id) values (0, 0, 1, 1, '', 1, '', 0, 0, 0, 0, 1, null, 83802)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 512 page no 496 n bits 440 index `PRIMARY` of table `dlx/mailpreference` trx id 0 14934963 lock_mode X
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 512 page no 496 n bits 440 index `PRIMARY` of table `dlx/mailpreference` trx id 0 14934963 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)
mysql> show create table mailpreference\G
*************************** 1. row ***************************
Table: mailpreference
Create Table: CREATE TABLE `mailpreference` (
`user_id` int(11) NOT NULL,
`real_name` varchar(200) collate utf8_unicode_ci NOT NULL default '',
`auto_check_interval` int(11) NOT NULL,
`reply_to` varchar(200) collate utf8_unicode_ci NOT NULL default '',
`reply_quoting` tinyint(1) NOT NULL,
`save_outgoing_messages` tinyint(1) NOT NULL,
`block_external_images` tinyint(1) NOT NULL,
`play_sound` tinyint(1) NOT NULL,
`auto_insert_signature` tinyint(1) NOT NULL,
`sending_receipt` int(11) NOT NULL,
`request_receipt_with_new_mail` tinyint(1) NOT NULL,
`spellcheck_on_send` tinyint(1) NOT NULL,
`permanent_delete` tinyint(1) NOT NULL,
`account_name` varchar(250) collate utf8_unicode_ci default NULL,
PRIMARY KEY (`user_id`),
KEY `fk_userid` (`user_id`),
CONSTRAINT `mailpreference_uid` FOREIGN KEY (`user_id`) REFERENCES `w_user` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
mysql> show create table w_user\G
*************************** 1. row ***************************
Table: w_user
Create Table: CREATE TABLE `w_user` (
`id` int(11) NOT NULL auto_increment,
`fullname` varchar(100) collate utf8_unicode_ci NOT NULL default '',
`email` varchar(150) collate utf8_unicode_ci NOT NULL default '',
`username` varchar(150) collate utf8_unicode_ci NOT NULL default '',
`password` varchar(100) collate utf8_unicode_ci NOT NULL default '',
`icon` varchar(100) collate utf8_unicode_ci default NULL,
`created` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=98392 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
How to repeat:
In my program the following query is run with the ratio of 20/s with multiple threads:
insert into mailpreference (auto_check_interval, auto_insert_signature, block_external_images, play_sound, real_name, reply_quoting, reply_to, save_outgoing_messages, request_receipt_with_new_mail, sending_receipt, spellcheck_on_send, permanent_delete, account_name, user_id) values (0, 0, 1, 1, '', 1, '', 0, 0, 0, 0, 1, null, 83802)
Description: I get deadlock when multiple threads try to insert a row into one table. Unfortunately I am not the owner of the code so I can not paste the exact transaction queries. From innotop sample deadlock1: ________________________________________________ Deadlock Transactions ________________________________________________ CXN ID Timestring User Host Victim Time Undo LStrcts Query Text cpmwd3 16582 2009-06-02 23:01:05 dlx cpm No 00:00 0 5 insert into mailpreference (auto_check_in cpmwd3 19179 2009-06-02 23:01:05 dlx cpm Yes 00:00 0 5 insert into mailpreference (auto_check_in CXN ID Waiting Mode DB Table Index Special Ins Intent cpmwd3 4432 1 X dlx mailpreference PRIMARY 1 cpmwd3 10907 0 X dlx mailpreference PRIMARY 0 cpmwd3 10907 1 X dlx mailpreference PRIMARY 1 Info from show innodb status (sample deadlock2) ------------------------ LATEST DETECTED DEADLOCK ------------------------ 090602 22:24:47 *** (1) TRANSACTION: TRANSACTION 0 14934965, ACTIVE 0 sec, process no 24262, OS thread id 1142745408 inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1216 MySQL thread id 4394, query id 2555179 cpm-wd4.pl-lab.lucent.com 10.155.155.14 dlx update insert into mailpreference (auto_check_interval, auto_insert_signature, block_external_images, play_sound, real_name, reply_quoting, reply_to, save_outgoing_messages, request_receipt_with_new_mail, sending_receipt, spellcheck_on_send, permanent_delete, account_name, user_id) values (0, 0, 1, 1, '', 1, '', 0, 0, 0, 0, 1, null, 83803) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 512 page no 496 n bits 440 index `PRIMARY` of table `dlx/mailpreference` trx id 0 14934965 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 0 14934963, ACTIVE 0 sec, process no 24262, OS thread id 1143810368 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 5 lock struct(s), heap size 1216 MySQL thread id 4500, query id 2555188 cpm-wd4.pl-lab.lucent.com 10.155.155.14 dlx update insert into mailpreference (auto_check_interval, auto_insert_signature, block_external_images, play_sound, real_name, reply_quoting, reply_to, save_outgoing_messages, request_receipt_with_new_mail, sending_receipt, spellcheck_on_send, permanent_delete, account_name, user_id) values (0, 0, 1, 1, '', 1, '', 0, 0, 0, 0, 1, null, 83802) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 512 page no 496 n bits 440 index `PRIMARY` of table `dlx/mailpreference` trx id 0 14934963 lock_mode X 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 512 page no 496 n bits 440 index `PRIMARY` of table `dlx/mailpreference` trx id 0 14934963 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) mysql> show create table mailpreference\G *************************** 1. row *************************** Table: mailpreference Create Table: CREATE TABLE `mailpreference` ( `user_id` int(11) NOT NULL, `real_name` varchar(200) collate utf8_unicode_ci NOT NULL default '', `auto_check_interval` int(11) NOT NULL, `reply_to` varchar(200) collate utf8_unicode_ci NOT NULL default '', `reply_quoting` tinyint(1) NOT NULL, `save_outgoing_messages` tinyint(1) NOT NULL, `block_external_images` tinyint(1) NOT NULL, `play_sound` tinyint(1) NOT NULL, `auto_insert_signature` tinyint(1) NOT NULL, `sending_receipt` int(11) NOT NULL, `request_receipt_with_new_mail` tinyint(1) NOT NULL, `spellcheck_on_send` tinyint(1) NOT NULL, `permanent_delete` tinyint(1) NOT NULL, `account_name` varchar(250) collate utf8_unicode_ci default NULL, PRIMARY KEY (`user_id`), KEY `fk_userid` (`user_id`), CONSTRAINT `mailpreference_uid` FOREIGN KEY (`user_id`) REFERENCES `w_user` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci mysql> show create table w_user\G *************************** 1. row *************************** Table: w_user Create Table: CREATE TABLE `w_user` ( `id` int(11) NOT NULL auto_increment, `fullname` varchar(100) collate utf8_unicode_ci NOT NULL default '', `email` varchar(150) collate utf8_unicode_ci NOT NULL default '', `username` varchar(150) collate utf8_unicode_ci NOT NULL default '', `password` varchar(100) collate utf8_unicode_ci NOT NULL default '', `icon` varchar(100) collate utf8_unicode_ci default NULL, `created` timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `username` (`username`), KEY `email` (`email`) ) ENGINE=InnoDB AUTO_INCREMENT=98392 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci How to repeat: In my program the following query is run with the ratio of 20/s with multiple threads: insert into mailpreference (auto_check_interval, auto_insert_signature, block_external_images, play_sound, real_name, reply_quoting, reply_to, save_outgoing_messages, request_receipt_with_new_mail, sending_receipt, spellcheck_on_send, permanent_delete, account_name, user_id) values (0, 0, 1, 1, '', 1, '', 0, 0, 0, 0, 1, null, 83802)