A strange deadlock in Mysql

Question

I have 1,000 vouchers with 2 different types. I want to give them to some special users on our website. So each user will get 1 vouchers for each type. To do that, I created a table t_voucher_pool :

CREATE TABLE `t_voucher_pool` (
  `iAutoID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `iCrowdID` INT(10) UNSIGNED DEFAULT NULL,
  `sTypeCode` VARCHAR(50) NOT NULL,
  `sCode` VARCHAR(255) NOT NULL,
  `sPassword` VARCHAR(255) NOT NULL,
  `iBindStatus` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
  `iVoucherID` INT(10) UNSIGNED DEFAULT NULL,
  `iBindTime` INT(10) UNSIGNED DEFAULT NULL,
  `iStatus` TINYINT(1) UNSIGNED NOT NULL DEFAULT '1',
  `iCreateTime` INT(10) UNSIGNED DEFAULT NULL,
  `iUpdateTime` INT(10) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (`iAutoID`),
  KEY `idx_crowdid_typecode` (`iCrowdID`,`sTypeCode`,`iBindStatus`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

Here is the sql which will be executed in my logic:

1.START TRANSACTION;
2.SELECT * FROM t_voucher_pool WHERE `iStatus`=1 AND `iBindStatus`=1 AND `iCrowdID`=7 AND `sTypeCode`='LUCKYDRAW' LIMIT 1 FOR UPDATE; //get one available voucher for LUCKYDRAW
3.SELECT * FROM t_voucher_pool WHERE `iStatus`=1 AND `iBindStatus`=1 AND `iCrowdID`=7 AND `sTypeCode`='JD' LIMIT 1 FOR UPDATE; //get one available voucher for JD
4.UPDATE t_voucher_pool SET iBindStatus=2 WHERE iAutoID=401; //update bindStatus for LUCKYDRAW voucher
5.UPDATE t_voucher_pool SET iBindStatus=2 WHERE iAutoID=10401; //update bindStatus for JD
6.COMMIT;

This worked fine when the concurrency level is 1. But when the concurrency level got raised, I found some requests will fail and they were caused by a deadlock. Here the deadlock will be detected if session 2 is pending on step 1 while session 1 tries to execute step 5.

Here is the sequence to reproduce the deadlock:

session1>>START TRANSACTION;
session1>>SELECT * FROM t_voucher_pool WHERE `iStatus`=1 AND `iBindStatus`=1 AND `iCrowdID`=7 AND `sTypeCode`='LUCKYDRAW' LIMIT 1 FOR UPDATE;
session2>>START TRANSACTION;
session2>>SELECT * FROM t_voucher_pool WHERE `iStatus`=1 AND `iBindStatus`=1 AND `iCrowdID`=7 AND `sTypeCode`='LUCKYDRAW' LIMIT 1 FOR UPDATE;(pending)
session1>>SELECT * FROM t_voucher_pool WHERE `iStatus`=1 AND `iBindStatus`=1 AND `iCrowdID`=7 AND `sTypeCode`='JD' LIMIT 1 FOR UPDATE;
session1>>UPDATE t_voucher_pool SET iBindStatus=2 WHERE iAutoID=401;
session1>>UPDATE t_voucher_pool SET iBindStatus=2 WHERE iAutoID=10401;
session2>>ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

I guess an update to the field bindStatus cause this deadlock. But since I've already got X lock for this record on step 3, why this still happen?

Any detailed explantion on this will be very appreciate. Thanks.

UPDATE on 2015/3/6

Sorry guys, it seems the example I posted before can not reproduce the problem. I just updated the example and here is the information from the innodb status:

2015-03-06 09:10:53 7f975a02b700
*** (1) TRANSACTION:
TRANSACTION 264943793, ACTIVE 44 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 6608579, OS thread handle 0x7f98f81b5700, query id 284931572 192.168.10.16 devadmin Sending data
SELECT * FROM t_voucher_pool WHERE `iStatus`=1 AND `iBindStatus`=1 AND `iCrowdID`=7 AND `sTypeCode`='LUCKYDRAW' LIMIT 1 FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10453 page no 156 n bits 792 index `idx_crowdid_typecode` of table `crowd_db`.`t_voucher_pool` trx id 264943793 lock_mode X waiting
Record lock, heap no 320 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000007; asc     ;;
 1: len 9; hex 4c55434b5944524157; asc LUCKYDRAW;;
 2: len 1; hex 01; asc  ;;
 3: len 4; hex 00000191; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 264941874, ACTIVE 374 sec updating or deleting, thread declared inside InnoDB 4999
mysql tables in use 1, locked 1
9 lock struct(s), heap size 2936, 7 row lock(s), undo log entries 2
MySQL thread id 6608580, OS thread handle 0x7f975a02b700, query id 284931884 192.168.10.16 devadmin updating
update t_voucher_pool set iBindStatus=2 where iAutoID=10401
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10453 page no 156 n bits 792 index `idx_crowdid_typecode` of table `crowd_db`.`t_voucher_pool` trx id 264941874 lock_mode X
Record lock, heap no 320 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000007; asc     ;;
 1: len 9; hex 4c55434b5944524157; asc LUCKYDRAW;;
 2: len 1; hex 01; asc  ;;
 3: len 4; hex 00000191; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10453 page no 156 n bits 792 index `idx_crowdid_typecode` of table `crowd_db`.`t_voucher_pool` trx id 264941874 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 320 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
 0: len 4; hex 00000007; asc     ;;
 1: len 9; hex 4c55434b5944524157; asc LUCKYDRAW;;
 2: len 1; hex 01; asc  ;;
 3: len 4; hex 00000191; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

Answer 1

Assuming default isolation level (repeatable read).

Session 1

Without an index to satisfy the WHERE clause, MySQL is going to use the primary key. Without specifying the order, MySQL will go through the primary key in ascending order by default:

SELECT * FROM t_vouchers WHERE type='TYPE1' AND bindStatus=1 LIMIT 1 FOR UPDATE

The above statement starts at the top of the primary key and locks each record until it reaches the first record that matches the WHERE clause. The second query does the same thing, so you end up with all records locked up through the highest id for the result set so far. In you example, that's 501.

Obviously, since those records are locked, the same transaction doesn't need any additional locks to update those records via the update statements.

Session 2

SELECT * FROM t_vouchers WHERE type='TYPE1' AND bindStatus=1 LIMIT 1 FOR UPDATE

The above statement starts at the top of the primary key and locks each record until it reaches the first record that matches the WHERE clause. When it tries to lock the first record, it can't because Session 1 already has it locked. So, Session 2 waits (blocks).

Result

No deadlock. Only one session at a time can lock the first record.

Is there a chance you have 200+ of these instances running at the same time? See LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK.

Use SHOW ENGINE INNODB STATUS to view details on the latest deadlock. Your problem may be elsewhere.

Update

Considering your updated info, with the secondary index, the deadlock is somewhat dependent on your data. You should be able to avoid the deadlock by removing iBindStatus from the secondary index, which will avoid the insert intention gap lock promotion.