Mysql – How to view the number of locks held by MySQL InnoDB, when the total is larger than 10

MySQL

We have a problem where our ORM is locking more tables than I'd expect. This is causing deadlock on our production system which I'm struggling to diagnose.

I can see, using either SHOW ENGINE INNODB STATUS or by using the innotop tool the top 10 locks, but I'm unable to see the rest.

Here's the head and tail of an example output from SHOW ENGINE INNODB STATUS:

--TRANSACTION 1832883, ACTIVE 1728 sec
29 lock struct(s), heap size 3520, 14 row lock(s), undo log entries 10

...

10 LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS

How can I view the locks beyond the first 10?

Perconda has the innodb_show_locks_held variable – is there an alternative for MySQL? I'm using MySQL 5.7 in development if that's relevant.

Thanks,
Ed

Best Answer

I recommend using the SYS schema that ships with MySQL 5.7. This will show you lock waits (more useful than locks) in a pretty easy to diagnose way:

mysql> SELECT * FROM sys.innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2014-11-11 13:39:20
                    wait_age: 00:00:07
               wait_age_secs: 7
                locked_table: `db1`.`t1`
                locked_index: PRIMARY
                 locked_type: RECORD
              waiting_trx_id: 867158
         waiting_trx_started: 2014-11-11 13:39:15
             waiting_trx_age: 00:00:12
     waiting_trx_rows_locked: 0
   waiting_trx_rows_modified: 0
                 waiting_pid: 3
               waiting_query: UPDATE t1 SET val = val + 1 WHERE id = 2
             waiting_lock_id: 867158:2363:3:3
           waiting_lock_mode: X
             blocking_trx_id: 867157
                blocking_pid: 4
              blocking_query: UPDATE t1 SET val = val + 1 + SLEEP(10) WHERE id = 2
            blocking_lock_id: 867157:2363:3:3
          blocking_lock_mode: X
        blocking_trx_started: 2014-11-11 13:39:11
            blocking_trx_age: 00:00:16
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 4
sql_kill_blocking_connection: KILL 4
1 row in set (0.01 sec)
Related Question