row lock cause dead lock in MySQL 5.7

Question

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

Answer 1

Try without update PK:

UPDATE r_room_seat SET status = 1,
    user_id = 62659,
    robot_flag = 1
    WHERE room_play_id = 539
      and status = 0
      and online = 1
      and (SELECT @update_id := id)
    LIMIT 1

hoping MySQL won't lock the index for this update

Answer 2

Changing the PRIMARY KEY of a row is highly abnormal. What is your intent?

Meanwhile, some changes:

BEGIN;
SELECT @update_id := id
    WHERE  ????   -- Which row do you want to change ??
    FOR UPDATE;
UPDATE  r_room_seat SET status = ?, 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;
COMMIT;

Also, beware, it is unclear whether the @update_id row was actually changed. You may need an extra test before the final SELECT.