MySQL Forums
Forum List  »  InnoDB

deadlock: a row being locked by a query twice?
Posted by: Amit K
Date: April 09, 2016 07:04AM

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` = 'XXXXXXXXXXXXXX@gmail.com'
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` = 'XXXXXXXXXXXXXX@gmail.com'
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 XXXXXXXXXXXXXX@gmail.com;;
5: len 24; hex 616264616c68616b626e6968657940676d61696c2e636f6d; asc XXXXXXXXXXXXXX@gmail.com;;
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` = 'XXXXXXXXXXXXXX@gmail.com'
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 XXXXXXXXXXXXXX@gmail.com;;
5: len 24; hex 616264616c68616b626e6968657940676d61696c2e636f6d; asc XXXXXXXXXXXXXX@gmail.com;;
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 XXXXXXXXXXXXXX@gmail.com;;
5: len 24; hex 616264616c68616b626e6968657940676d61696c2e636f6d; asc XXXXXXXXXXXXXX@gmail.com;;
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)

Options: ReplyQuote


Subject
Views
Written By
Posted
deadlock: a row being locked by a query twice?
1287
April 09, 2016 07:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.