Today my app tell me encount a dead lock, error like this:
rror querying database. Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
2020-09-25T02:10:41.962926578Z ### The error may exist in class path resource [mybatis/mapper/room/RoomSeatCustomMapper.xml]
2020-09-25T02:10:41.962930571Z ### The error may involve com.sportswin.soa.room.dao.RoomSeatCustomMapper.updateForFreeSeat-Inline
2020-09-25T02:10:41.962934646Z ### The error occurred while setting parameters
2020-09-25T02:10:41.96294191Z ### SQL: set @update_id := 0; UPDATE r_room_seat SET status = ?, id = (SELECT @update_id := id), user_id = ?, robot_flag = ? WHERE room_play_id = ? and status = 0 and online = 1 LIMIT 1; select id, room_play_id, room_id, user_id, creator, recent_active, deleted, sort, updated_time, created_time, `status`, robot_flag, app_id, app_mark, `online`, version from r_room_seat where id = @update_id;
2020-09-25T02:10:41.962947104Z ### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
2020-09-25T02:10:41.962951479Z ; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
2020-09-25T02:10:41.962961331Z at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267) ~[spring-jdbc-5.1.13.RELEASE.jar!/:5.1.13.RELEASE]
then I login to MySQL and using this command to check lock history:
show engine innodb status
the output like this:
=====================================
2020-09-25 10:36:53 0x7ff4b4c5d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 12 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 762213 srv_active, 0 srv_shutdown, 139997 srv_idle
srv_master_thread log flush and writes: 902210
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 363879865
OS WAIT ARRAY INFO: signal count 448819672
RW-shared spins 0, rounds 425739262, OS waits 209673117
RW-excl spins 0, rounds 4271714439, OS waits 133598673
RW-sx spins 135926, rounds 2757000, OS waits 52788
Spin rounds per wait: 425739262.00 RW-shared, 4271714439.00 RW-excl, 20.28 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-09-25 10:10:41 0x7ff4b4ce1700
*** (1) TRANSACTION:
TRANSACTION 841893940, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 108348, OS thread handle 140688980530944, query id 184994716 172.19.104.233 root Searching rows for update
UPDATE r_room_seat SET status = 1,
id = (SELECT @update_id := id),
user_id = 62659,
robot_flag = 1
WHERE room_play_id = 539 and status = 0 and online = 1
LIMIT 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3493 page no 528 n bits 176 index PRIMARY of table `room_db`.`r_room_seat` trx id 841893940 lock_mode X locks rec but not gap waiting
Record lock, heap no 56 PHYSICAL RECORD: n_fields 20; compact format; info bits 0
0: len 8; hex 000000000000d649; asc I;;
1: len 6; hex 0000322d9b9a; asc 2- ;;
2: len 7; hex 3e000040042eda; asc > @ . ;;
3: len 8; hex 800000000000021b; asc ;;
4: len 8; hex 8000000000000097; asc ;;
5: len 8; hex 800000000000f4d5; asc ;;
6: len 8; hex 7fffffffffffffff; asc ;;
7: len 8; hex 8000017452aa2c5e; asc tR ,^;;
8: len 4; hex 80000000; asc ;;
9: len 4; hex 80000000; asc ;;
10: len 8; hex 8000017452aa2c5e; asc tR ,^;;
11: len 8; hex 8000017452aa2c5e; asc tR ,^;;
12: len 4; hex 80000001; asc ;;
13: len 1; hex 81; asc ;;
14: len 8; hex 8000000000000005; asc ;;
15: len 10; hex 48415050595f5241494e; asc HAPPY_RAIN;;
16: len 4; hex 80000001; asc ;;
17: len 8; hex 8000000000000000; asc ;;
18: len 8; hex 8000000000000074; asc t;;
19: len 4; hex 80000001; asc ;;
*** (2) TRANSACTION:
TRANSACTION 841893905, ACTIVE 0 sec fetching rows
mysql tables in use 2, locked 2
746 lock struct(s), heap size 90320, 128960 row lock(s)
MySQL thread id 100540, OS thread handle 140688982152960, query id 184994612 172.19.104.233 root Sending data
UPDATE r_room_seat s
SET s.status = 1,
s.room_play_id = (SELECT @update_id := max(room_play_id)),
s.user_id = (
case when s.seat_num = 1
then 63182
when s.seat_num = 2
then 16472
else -1 end
),
s.robot_flag = (
case when s.seat_num = 1
then 1
when s.seat_num = 2
then 0
else 0 end
)
WHERE s.status = 0
and s.online = 1
and s.room_play_id in (
select room_play_id
from (
select room_play_id as room_play_id
from r_room_seat
where room_id = 166
and status = 0
group by room_play_id
having count(*) = 2
limit 1
) a
)
and s.room_id = 166
LIMIT 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3493 page no 528 n bits 176 index PRIMARY of table `room_db`.`r_room_seat` trx id 841893905 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;;
the first update command only using one row to update, and the second command only using 2 row for update. the two sql is impossible to update the same record. this is my table index look like:
why it still have dead lock? To my understand, it is impossible because the MySQL using row lock and the two sql update not the same row. What should I do to avoid the dead lock?
this is the table DDL:
CREATE TABLE `r_room_seat` (
`id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`room_play_id` bigint(128) NOT NULL COMMENT '房间ID',
`room_id` bigint(20) NOT NULL COMMENT '房间类型ID',
`user_id` bigint(128) NOT NULL COMMENT '入座用户ID',
`creator` bigint(20) DEFAULT NULL COMMENT '创建者',
`recent_active` bigint(20) NOT NULL COMMENT '最近活跃时间',
`deleted` int(11) NOT NULL DEFAULT '0' COMMENT '座位移除标记',
`sort` int(11) NOT NULL DEFAULT '0' COMMENT '座位显示排序',
`updated_time` bigint(20) NOT NULL COMMENT '更新时间',
`created_time` bigint(20) NOT NULL COMMENT '创建时间',
`status` int(11) NOT NULL DEFAULT '0' COMMENT '座位状态(0:空闲 1:入座 2:即将离座)',
`robot_flag` tinyint(4) NOT NULL DEFAULT '0' COMMENT ' 是否是机器人(1:是 0.否)',
`app_id` bigint(11) NOT NULL COMMENT '当前所属应用的ID',
`app_mark` varchar(128) NOT NULL DEFAULT '' COMMENT '应用枚举值',
`online` int(11) DEFAULT '1' COMMENT '是否在线',
`version` bigint(20) NOT NULL DEFAULT '0' COMMENT '修改版本,并发匹配时,乐观锁控制',
`tenant_id` bigint(20) NOT NULL COMMENT '租户编号',
`seat_num` int(11) NOT NULL COMMENT '座位号',
PRIMARY KEY (`id`),
KEY `r_room_seat_index` (`room_play_id`,`room_id`)
) ENGINE=InnoDB AUTO_INCREMENT=73415 DEFAULT CHARSET=utf8mb4 COMMENT='实际玩的房间座位';
this is the explain output:
1 UPDATE s NULL index NULL PRIMARY 8 NULL 72300 100.00 Using where
3 DEPENDENT SUBQUERY <derived4> NULL system NULL NULL NULL NULL 1 100.00 NULL
4 DERIVED r_room_seat NULL index r_room_seat_index r_room_seat_index 16 NULL 72300 1.00 Using where
2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
Best Answer
Try without update PK:
hoping MySQL won't lock the index for this update