获取InnoDB行锁争用情况

方法一：

​ 开启监视器，MySQL会定期将包含锁冲突信息的日志写入 error log;注意长时间打开监视器会导致 error log 文件变得非常巨大。

set global innodb_status_output=on;
set global innodb_status_output_locks=on;

方法二：

​ 使用如下命令查看最新的状态信息。

​ show engine innodb status \G;

mysql> show engine innodb status \G;
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2020-05-30 04:47:03 0x7fc0cc22f700 INNODB MONITOR OUTPUT
=====================================
最近 39 秒内每2秒的平均值(计算出这一平均值的时间间隔，即自上次输出以来的时间，或者是距上次内部复位的时长)
Per second averages calculated from the last 39 seconds
-----------------
# 线程
BACKGROUND THREAD
-----------------
# 可反映数据库的压力情况
srv_master_thread loops: 10 srv_active, 0 srv_shutdown, 21529 srv_idle
srv_master_thread log flush and writes: 21539
----------
# 信号
SEMAPHORES
----------
# os wait 的信息 ，reservation count 表示InnoDB产生了多少次OS WAIT  
OS WAIT ARRAY INFO: reservation count 31
# 进行OS WAIT线程，接收到多少次信号(single)被唤醒，如果这个single数值越大，几十万或者几百万，可能是很多I/0等待或者是InnoDB争用问题
# (关于争用问题可能与OS调度有关，可以尝试减少innodb_thread_concurrency参数)
OS WAIT ARRAY INFO: signal count 30
# Mutex spin线程无法获取锁而进入Spin wait ,rounds是spin wait 进行轮询检查mutextes的次数，os wait 线程放弃spin-wait 进入挂起状态
Mutex spin waits 1664035, rounds 4276317, OS waits 20348
# RW-shared 共享锁
RW-shared spins 0, rounds 32, OS waits 16
# RW-excl 排他锁
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 32.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
# 备注：要明白Innodb如何处理互斥量(Mutexes)，以及什么是两步获得锁(two-step approach)。首先进程，
# 试图获得一个锁，如果此锁被它人占用。它就会执行所谓的spin wait,即所谓循环的查询”锁被释放了吗？”。
# 如果在循环过程中，一直未得到锁释放的信息，则其转入OS WAIT，即所谓线程进入挂起(suspended)状态。
# 直到锁被释放后，通过信号(singal)唤醒线程
# Spin wait的消耗远小于OS waits。Spinwait利用cpu的空闲时间，检查锁的状态，
# OS Wait会有所谓content switch，从CPU内核中换出当前执行线程以供其它线程使用。
# 你可以通过innodb_sync_spin_loops参数来平衡spin wait和os wait
------------------------
LATEST DETECTED DEADLOCK
------------------------
# 死锁发生的时间
2020-05-30 04:46:53 0x7fc0cc271700
# 事务 1 的状态
*** (1) TRANSACTION:
# 事务 1282630 ，ACTIVE 31 sec表示事务处于活跃状态 31s
TRANSACTION 1282630, ACTIVE 31 sec inserting
# 表示事务 1282630 正在使用1个表，且涉及锁的表有1个
mysql tables in use 1, locked 1
# 表示在等待 4 把锁，占用内存 1136 字节，涉及2行记录
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s)
# 事务的线程ID信息，操作系统句柄信息，连接来源、用户
MySQL thread id 9, OS thread handle 140466035021568, query id 246 localhost root update
# 事务涉及的SQL
insert into country(country_id,country) values(110,'TESTa')
# 事务正在等待锁被授予
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
# 表示等待的锁是一个record lock，空间id是 29，页编号为 3，大概位置在页的 184 位处，锁发生在表 `sakila`.`country`的主键上，
# 是一个s锁，但是不是gap lock。 waiting表示正在等待锁
RECORD LOCKS space id 29 page no 3 n bits 184 index PRIMARY of table `sakila`.`country` trx id 1282630 lock mode S locks rec but not gap waiting
# 表示record lock的heap no 位置
Record lock, heap no 111 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
# 以上表示死锁发生时事务1等待的锁，事务想获得 country 表的 PRIMARY 对应的s共享锁(Innodb的锁是与索引相关)
 0: len 2; hex 006e; asc  n;;
 1: len 6; hex 000000139247; asc      G;;
 2: len 7; hex bf000040210110; asc    @!  ;;
 3: len 5; hex 5445535462; asc TESTb;;
 4: len 4; hex 5ed21d6b; asc ^  k;;

# 事务 2 的状态
*** (2) TRANSACTION:
# 事务 1282631，ACTIVE 18 sec表示事务处于活跃状态 18s, starting index read表示正在使用索引读取数据行
TRANSACTION 1282631, ACTIVE 18 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 140466035562240, query id 247 localhost root statistics
select first_name,last_name from sakila.actor where actor_id=1 for update
# 表示事务 2 持有锁信息
*** (2) HOLDS THE LOCK(S):
# 表示等待的锁是一个record lock，空间id是 29，页编号为 3，大概位置在页的 184 位处，锁发生在表 `sakila`.`country`的主键上，
# 是一个x锁，但是不是gap lock。
RECORD LOCKS space id 29 page no 3 n bits 184 index PRIMARY of table `sakila`.`country` trx id 1282631 lock_mode X locks rec but not gap
Record lock, heap no 111 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 2; hex 006e; asc  n;;
 1: len 6; hex 000000139247; asc      G;;
 2: len 7; hex bf000040210110; asc    @!  ;;
 3: len 5; hex 5445535462; asc TESTb;;
 4: len 4; hex 5ed21d6b; asc ^  k;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 3 n bits 280 index PRIMARY of table `sakila`.`actor` trx id 1282631 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 2; hex 0001; asc   ;;
 1: len 6; hex 000000000945; asc      E;;
 2: len 7; hex c50000013d0110; asc     =  ;;
 3: len 8; hex 50454e454c4f5045; asc PENELOPE;;
 4: len 7; hex 4755494e455353; asc GUINESS;;
 5: len 4; hex 43f2f5a9; asc C   ;;

# 如果两个事务的回滚开销一样，选择了后提交的事务进行回滚，如果两个事务回滚的开销不同(undo 数量不同)，那么就回滚开销最小的那个事务。
# 表示选择了哪个事务回滚，避免无限期死锁等待(这里表示事物1回滚)。
*** WE ROLL BACK TRANSACTION (1)
# innodb有一个内在的死锁检测工具，当死锁超过一定时间后，会回滚其中一个事务,innodb_lock_wait_timeout 可配置死锁等待超时时间
------------
# 包含了InnoDB事务(transaction)的统计信息
TRANSACTIONS
------------
# 当前的transaction id ,这是个系统变量，随着每次新的transaction产生而增加
Trx id counter 1282632
# 正在进行清空的操作操作的transaction ID
Purge done for trx's n:o < 1282611 undo n:o < 0 state: running but idle
# 记录了undo spaces 内unpurged 的事务个数
History list length 8
# Purge的原则就是记录没有被其它事务继续使用了

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421941325900512, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421941325899600, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 1282631, ACTIVE 28 sec
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 140466035562240, query id 247 localhost root
--------
# 显示了I/O  Helper thread d的状态，包含一些统计信息
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
# 以上显示了I/O Helper thread的状态

Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
# 显示各个I/O Helper thread的pending operations,pending的log和buffer pool thread的fsync()调用
Pending flushes (fsync) log: 0; buffer pool: 0
# 显示了reads writes fsync() 调用次数
2242 OS file reads, 135 OS file writes, 69 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.21 writes/s, 0.21 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
# seg size 表示当前插入缓冲的大小为 3094*16KB，大约为 49504KB。free list len代表了空闲列表的长度，merges 表示合并次数
Ibuf: size 1, free list len 3092, seg size 3094, 0 merges
merged operations:
# insert 插入的记录数，delete mark 打上的标记,delete 删除的次数 
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.08 non-hash searches/s
---
# 记录了transaction log 子系统的信息
LOG
---
# 显示当前log sequence number表示有多少字节写入到log文件内(当前的LSN)
Log sequence number 13095550429
# 显示已经被flushed(写入磁盘)的logs(刷新到重做日志文件的LSN)
Log flushed up to   13095550429
Pages flushed up to 13095550429
# 显示最后一个checkpoint 的logs(刷新到磁盘的LSN)
Last checkpoint at  13095550420
# 显示pending log 的统计信息
0 pending log flushes, 0 pending chkp writes
50 log i/o's done, 0.13 log i/o's/second
----------------------
# InnoDB存储引擎缓冲池的使用情况
BUFFER POOL AND MEMORY
----------------------
# 显示分配给innodb 的内存大小，以及additional pool 使用的大小 (0表示没有使用)
Total large memory allocated 137428992
Dictionary memory allocated 506398
# buffer pool size > database pages 因为buffer pool size 还会存放lock index hash index 等一些其他系统信息
# 表示一共有 8192 个缓冲帧，每个buffer frame为16K，即：8192*16K/1024=128M
Buffer pool size   8192
# 剩余空间的缓冲帧
Free buffers       5975
# 已经使用的缓冲帧
Database pages     2217
Old database pages 838
# 脏页的数量
Modified db pages  0
# 显示了pending的reads 和writes
Pending reads      0
# 显示InnoDB读写和创建的页面(pages)
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2182, created 35, written 71
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
# 显示buffer pool 的命中率
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2217, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
# 显示了row operations 及其他一些统计信息
ROW OPERATIONS
--------------
# 显示了有多少个线程在InnoDB内核
0 queries inside InnoDB, 0 queries in queue
# 有多少个read view 被打开，一个read view 是一致性保证MVCC "snapshot"
0 read views open inside InnoDB
# 显示内核main thread的状态信息。
Process ID=7432, Main thread ID=140466049263360, state: sleeping
Number of rows inserted 46, updated 9, deleted 0, read 230
0.03 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.03 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

参考链接

https://www.cnblogs.com/xiaoboluo768/p/5171425.html
https://www.cnblogs.com/olinux/p/5497176.html