deadlock: a row being locked by a query twice?

[Amit K]

I have got one and only one occurrence of MySQL deadlock which I have not been able to find a reason for.

I would like to know:

1) Why did this deadlock occur? Something wrong with schema/query?
2) Why is the query waiting for gap lock? (I am explicitly using primary key in where clause)

The first line of source code gets an exclusive lock using following query:


SELECT
*
FROM
`active_user`
WHERE `user_id` = 111111
AND `_id` IN (
SELECT
_id
FROM
`active_user`
WHERE `user_id` = 111111
AND `identifier` = '[e-mail]'
AND find_in_set (`type`, '1,11,18') > 0
)
FOR UPDATE



Table create statement

CREATE TABLE `active_user` (
`user_id` int(10) NOT NULL DEFAULT '0',
`id` int(5) NOT NULL DEFAULT '0',
`identifier` varchar(255) NOT NULL,
`name` varchar(255) NOT NULL,
`alias_name` varchar(255) DEFAULT NULL,
`type` int(4) NOT NULL,
`column1` text NOT NULL,
`column2` int(2) NOT NULL,
`column3` tinyint(1) DEFAULT '0',
`column4` tinyint(1) DEFAULT '0',
`column5` text,
PRIMARY KEY (`user_id`,`id`),
UNIQUE KEY `uuid` (`user_id`,`identifier`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8



From output of command show engine innodb status

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-04-07 21:05:35 2b822e806700
*** (1) TRANSACTION:
TRANSACTION 12298419769, ACTIVE 1 sec fetching rows
mysql tables in use 2, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 3714739237, OS thread handle 0x2b822da0f700, query id 31607921072 10.0.28.66 connection1 Sending data
SELECT
*
FROM
`active_user`
WHERE `user_id` = 111111
AND `_id` IN (
SELECT
_id
FROM
`active_user`
WHERE `user_id` = 111111
AND `identifier` = '[e-mail]'
AND find_in_set (`type`, '1,2,8') > 0
)
FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 24486 n bits 96 index `PRIMARY` of table `config`.`active_user` trx id 12298419769 lock_mode X waiting
Record lock, heap no 24 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
0: len 4; hex 803a789b; asc :x ;;
1: len 4; hex 80000003; asc ;;
2: len 6; hex 0002dd0afdd5; asc ;;
3: len 7; hex 050000014e2e72; asc N.r;;
4: len 24; hex 616264616c68616b626e6968657940676d61696c2e636f6d; asc [e-mail];;
5: len 24; hex 616264616c68616b626e6968657940676d61696c2e636f6d; asc [e-mail];;
6: len 15; hex 416264616c68616b20426e69686579; asc john;;
7: len 4; hex 80000001; asc ;;
8: len 30; hex 7b2263726564656e7469616c5f76657273696f6e223a352c226163636573; asc {"version":5,"recces; (total 423 bytes);
9: len 4; hex 80000001; asc ;;
10: len 1; hex 80; asc ;;
11: len 1; hex 80; asc ;;
12: len 30; hex 7b227069635f75726c223a2268747470733a5c2f5c2f6c68332e676f6f67; asc {"url":"https:\/\/aaaaaa.com; (total 113 bytes);

*** (2) TRANSACTION:
TRANSACTION 12298419817, ACTIVE 1 sec starting index read
mysql tables in use 2, locked 1
3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 3714739267, OS thread handle 0x2b822e806700, query id 31607921318 10.0.29.120 connection1 Sending data
SELECT
*
FROM
`active_user`
WHERE `user_id` = 111111
AND `_id` IN (
SELECT
_id
FROM
`active_user`
WHERE `user_id` = 111111
AND `identifier` = '[e-mail]'
AND find_in_set (`type`, '1,2,8') > 0
)
FOR UPDATE
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 76 page no 24486 n bits 96 index `PRIMARY` of table `config`.`active_user` trx id 12298419817 lock_mode X locks rec but not gap
Record lock, heap no 24 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
0: len 4; hex 803a789b; asc :x ;;
1: len 4; hex 80000003; asc ;;
2: len 6; hex 0002dd0afdd5; asc ;;
3: len 7; hex 050000014e2e72; asc N.r;;
4: len 24; hex 616264616c68616b626e6968657940676d61696c2e636f6d; asc [e-mail];;
5: len 24; hex 616264616c68616b626e6968657940676d61696c2e636f6d; asc [e-mail];;
6: len 15; hex 416264616c68616b20426e69686579; asc john;;
7: len 4; hex 80000001; asc ;;
8: len 30; hex 7b2263726564656e7469616c5f76657273696f6e223a352c226163636573; asc {"version":5,"recces; (total 423 bytes);
9: len 4; hex 80000001; asc ;;
10: len 1; hex 80; asc ;;
11: len 1; hex 80; asc ;;
12: len 30; hex 7b227069635f75726c223a2268747470733a5c2f5c2f6c68332e676f6f67; asc {"url":"https:\/\/aaaaaa.com; (total 113 bytes);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 76 page no 24486 n bits 96 index `PRIMARY` of table `config`.`active_user` trx id 12298419817 lock_mode X locks rec but not gap waiting
Record lock, heap no 18 PHYSICAL RECORD: n_fields 13; compact format; info bits 0
0: len 4; hex 803a789b; asc :x ;;
1: len 4; hex 80000002; asc ;;
2: len 6; hex 0002dd0afdd5; asc ;;
3: len 7; hex 050000014e2bfc; asc N+ ;;
4: len 24; hex 616264616c68616b626e6968657940676d61696c2e636f6d; asc [e-mail];;
5: len 24; hex 616264616c68616b626e6968657940676d61696c2e636f6d; asc [e-mail];;
6: len 15; hex 416264616c68616b20426e69686579; asc john;;
7: len 4; hex 80000002; asc ;;
8: len 30; hex 7b2263726564656e7469616c5f76657273696f6e223a352c226163636573; asc {"version":5,"recces; (total 597 bytes);
9: len 4; hex 80000002; asc ;;
10: len 1; hex 80; asc ;;
11: len 1; hex 80; asc ;;
12: len 30; hex 7b227069635f75726c223a2268747470733a5c2f5c2f6c68332e676f6f67; asc {"url":"https:\/\/aaaaaa.com; (total 113 bytes);

*** WE ROLL BACK TRANSACTION (2)