is there any way to show MySQL table lock history

Question

I am using MySQL 5.7.26,is there any way to show table lock history?I know how to check current table lock using:

show OPEN TABLES where In_use > 0;

I want to avoid table lock in my SQL query,so view the history may help.Current lock may not get all locked table(Maybe the locked table query not execute right now).

Answer 1

Today I found a way to show this in MySQL 5.7:

show engine innodb status

this is the part of result output:

====================================
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;;

you can get the dead lock information and analysis.