Bug #43211 Deadlock detected on concurrent delete/insert into same table

• Submitted: 26 Feb 2009 7:14
• Modified: 26 Feb 2009 9:16
• Reporter: Royce Lithgo
• Status: Not a Bug
• Category: MySQL Server: Locking
• Severity: S2 (Serious)
• Version: 5.0.51a-community-log
• OS: Linux
• CPU Architecture: Any

[26 Feb 2009 7:14] Royce Lithgo

Description:
I have 2 transactions performing concurrent inserts and deletes into the same table. I am getting deadlocks detected if the transactions execute in a specific sequence and this is unexpected.

How to repeat:
Table DDL:
CREATE TABLE `ns_custom_app_transaction_lock` (
  `custom_app_id` int(11) NOT NULL default '0',
  `transaction_key` varchar(100) collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`custom_app_id`,`transaction_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Tran A:
start transaction;

Tran B:
start transaction;

Tran A:
delete from ns_custom_app_transaction_lock
where custom_app_id=1
and transaction_key='abc123';

Tran B:
delete from ns_custom_app_transaction_lock
where custom_app_id=1
and transaction_key='abc123';

Tran A:
insert into ns_custom_app_transaction_lock
values(1,'abc123'); 
[ BLOCKED ]

Tran B:
insert into ns_custom_app_transaction_lock
values(1,'abc123');

This last statement causes Tran A to fail with deadlock error:
1213 - Deadlock found when trying to get lock; try restarting transaction

[26 Feb 2009 9:16] Sveta Smirnova

Thank you for the report.

Default transaction isolation level for InnoDB is REPEATABLE READ. In this case "All consistent reads within the same transaction read the snapshot established by the first read." So transaction 2 holds lock "RECORD LOCKS space id 0 page no 50 n bits 72 index `PRIMARY` of table `test`.`ns_custom_app_transaction_lock` trx id 0 771 lock_mode X" (DELETE statement) and at the same time both transaction 1 and transaction 2 wait for the same lock for INSERT statements. So deadlock is expected.

Workaround: use less strict transaction isolation level or innodb_locks_unsafe_for_binlog (you will get timeout instead of deadlock for second transaction in this case).