MySQL 行锁该怎么分析

一些可以用于排查的信息：
1.information_schema.innodb_locks
可以看到哪些事务在持有哪些锁，例如：

+----------------------+-------------+-----------+-----------+-----------------------------------+------------+------------+-----------+----------+----------------------------------------+
| lock_id              | lock_trx_id | lock_mode | lock_type | lock_table                        | lock_index | lock_space | lock_page | lock_rec | lock_data                              |
+----------------------+-------------+-----------+-----------+-----------------------------------+------------+------------+-----------+----------+----------------------------------------+
| 421225448:446:163:83 | 421225448   | X         | RECORD    | `test`.`xxx` | PRIMARY    |        446 |       163 |       83 | 'pre-7f27351f85e94cd4a808da1e9638beca' |
| 421225427:446:163:83 | 421225427   | X         | RECORD    | `test`.`xxx` | PRIMARY    |        446 |       163 |       83 | 'pre-7f27351f85e94cd4a808da1e9638beca' |
| 421225386:446:163:83 | 421225386   | X         | RECORD    | `test`.`xxx` | PRIMARY    |        446 |       163 |       83 | 'pre-7f27351f85e94cd4a808da1e9638beca' |
| 421225445:446:219:63 | 421225445   | X         | RECORD    | `test`.`xxx` | PRIMARY    |        446 |       219 |       63 | 'pre-b5aac2d088da4fbebd6004f674859fef' |
| 421225426:446:219:63 | 421225426   | X         | RECORD    | `test`.`xxx` | PRIMARY    |        446 |       219 |       63 | 'pre-b5aac2d088da4fbebd6004f674859fef' |
| 421225390:446:219:63 | 421225390   | X         | RECORD    | `test`.`xxx` | PRIMARY    |        446 |       219 |       63 | 'pre-b5aac2d088da4fbebd6004f674859fef' |
+----------------------+-------------+-----------+-----------+-----------------------------------+------------+------------+-----------+----------+----------------------------------------+
6 rows in set, 1 warning (0.00 sec)

2.information_schema.innodb_trx
可以看到当前有哪些事务在运行，包括空闲事务，输出信息重点关注事务id(trx_id)，会话线程id(trx_mysql_thread_id)，该事务是在running还是在lock wait，在跑什么语句(trx_query)

                    trx_id: 421221575
                 trx_state: RUNNING
               trx_started: 2020-04-22 11:55:22
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 54
       trx_mysql_thread_id: 34343050
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 3
          trx_lock_structs: 4
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 50
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

3.information_schema.innodb_lock_waits
可以看到锁竞争关系，例如在锁冲突中，等待锁的是哪个事务(requesting_trx_id)，持有锁的是哪个事务(blocking_trx_id)

*************************** 1. row ***************************
requesting_trx_id: 421221576
requested_lock_id: 421221576:446:521:77
  blocking_trx_id: 421221512
 blocking_lock_id: 421221512:446:521:77
*************************** 2. row ***************************
requesting_trx_id: 421221576
requested_lock_id: 421221576:446:521:77
  blocking_trx_id: 421221503
 blocking_lock_id: 421221503:446:521:77

4.show engine innodb status
可以看事务在持有哪些锁，等待哪些锁，已经在哪些行上等待锁。

怎么将这些信息利用起来呢？首先行锁，我们一般是从show processlist里看到一个DML执行了很长时间都没完成，例如一条update t set … where id=3的语句长时间处于updating状态。
排除了MDL锁之后，我们三个information_schema表关联，来查出一些信息。
例如，根据thread_id，查出锁住我们的事务在做什么
select c.* from
information_schema.innodb_trx a
join
information_schema.innodb_lock_waits b
on a.trx_id=b.requesting_trx_id
join
information_schema.innodb_trx c
on b.blocking_trx_id=c.trx_id
where a.trx_mysql_thread_id=$lock_thread_id

三个information_schema表之间都可以通过事务id和线程id来关联，查出我们需要的信息，最主要还是要熟悉这三个表，就可以随意join出我们想要的答案。