mysql-lock-transaction

471次阅读 | 发布于3年以前

事务、锁相关表详解

对于每个表中包含了哪些字段，以及它们之间的关系，这里就不做详细展开，这里只重点说一下平时排查问题时常用的四个表

PROCESSLIST
INNODB_TRX
INNODB_LOCKS
INNODB_LOCK_WAITS

对其他表的使用，感兴趣或者用到的可以根据需要查文档。

PROCESSLIST

PROCESSLIST 表包含了运行中的线程信息。

列名	说明
ID	连接标识符
USER	数据库用户
HOST	用户的主机地址
DB	默认数据库，如果没有则为 `NULL`
COMMAND	运行中的命令类型，取值参考 Thread Command Values
TIME	线程处于当前状态的时间，单位是秒
STATE	线程当前的状态，参考 Thread States
INFO	线程正在执行的语句，如果没有则为 `NULL`
TIME_MS	线程处于当前状态的时间(以毫秒为单位，精度为微秒)
STAGE	该过程当前所处的阶段
MAX_STAGE	最大阶段的数
PROGRESS	当前阶段内的过程进度(0-100%)
MEMORY_USED	线程使用的内存，单位是字节
EXAMINED_ROWS	线程检查的行数，只对 UPDATE, DELETE 以及相似的命令有效，对 SELECT 命令等这个值为 0
QUERY_ID	查询 ID
INFO_BINARY	二进制的数据信息

其中，COMMAND 类型的取值参看下面的表格

值	说明
Binlog Dump	主线程发送 binlog 到从库
Change user	执行改变用户操作
Close stmt	正在关闭 Prepared statement
Connect	从库已经连接到主库
Connect Out	从库正在连接主库
Create DB	执行创建数据库的操作
Daemon	内部服务线程，非客户端连接
Debug	正在生成调试信息
Delayed insert	延迟插入处理器
Drop DB	执行 DROP 数据库操作
Error	错误
Execute	正在执行 Prepared statement
Fetch	正在获取已经执行完成的 Prepared statement 的结果
Field List	检索表的列信息
Init DB	正在选择默认数据库
Kill	正在 Kill 其它线程
Long Data	正在从 Prepared statement 的结果中加载很长的数据
Ping	服务器 Ping 请求处理器
Prepare	准备 Prepared statement
Processlist	正在准备关于服务器线程的 processlist 信息
Query	正在执行语句
Quit	线程正在终止
Refresh	Flush 表、日志、缓存，或者刷新副本服务器或者状态变量信息
Register Slave	正在注册从库
Reset stmt	正在重置 Prepared statement
Set option	正在设置或者重置客户端执行语句的选项
Sleep	等待客户端发送新的语句
Shutdown	正在关闭服务器
Statistics	正在准备关于服务器的状态信息
Table Dump	正在发送表的内容给从库
Time	没有使用

INNODB_TRX

INNODB_TRX 表存储了当前正在执行的所有事务信息，下面是每一列的说明

列名	说明
TRX_ID	唯一的事务 ID
TRX_STATE	事务的执行状态： `RUNNING`, `LOCK WAIT`, `ROLLING BACK`, `COMMITTING`
TRX_STARTED	事务开始时间
TRX_REQUESTED_LOCK_ID	如果 `TRX_STATE`=`LOCK_WAIT`, 则为正在等待的锁的 INNODB_LOCKS.LOCK_ID；其他状态为 `NULL`
TRX_WAIT_STARTED	如果 `TRX_STATE`=`LOCK_WAIT`, 则为事务开始等待锁的时间；其它状态为 `NULL`.
TRX_WEIGHT	基于锁定的行数和更改的行数得出的事务权重，要解决死锁，首先会回滚低权重的事务。如果事务中包含了非事务表，则该事务拥有更高的权重
TRX_MYSQL_THREAD_ID	表 PROCESSLIST 中的线程 ID（注意的是，锁和事务信息表使用的是 processlist 的快照，因此这两者中的记录可能是不同的）
TRX_QUERY	当前事务正在执行的 SQL 语句
TRX_OPERATION_STATE	当前事务的状态或者 `NULL`
TRX_TABLES_IN_USE	当前 SQL 语句所使用的 InnoDB 表的数量
TRX_TABLES_LOCKED	当前 SQL 语句持有行锁的 InnoDB 表的数量
TRX_LOCK_STRUCTS	事务保留的锁的数量
TRX_LOCK_MEMORY_BYTES	用于保存当前事务的锁的结构体总大小（字节）
TRX_ROWS_LOCKED	当前事务锁的数据行数，这是一个近似值，可能包含了对当前事务不可见的行（这些行已经标记为删除了，但是物理位置上还存在）
TRX_ROWS_MODIFIED	当前事务新增或者修改的行数
TRX_CONCURRENCY_TICKETS	标识出当前事务在被换出之前还能做多少工作，详情参考 innodb_concurrency_tickets 系统变量
TRX_ISOLATION_LEVEL	当前事务的隔离级别
TRX_UNIQUE_CHECKS	当前事务的唯一性检查是否开启
TRX_FOREIGN_KEY_CHECKS	外键约束检查是否开启
TRX_LAST_FOREIGN_KEY_ERROR	最后一次外键错误，没有的话为 `NULL`
TRX_ADAPTIVE_HASH_LATCHED	I don't know what this means
TRX_ADAPTIVE_HASH_TIMEOUT	I don't know what this means
TRX_IS_READ_ONLY	只读事务为 `1`，否则为 `0`
TRX_AUTOCOMMIT_NON_LOCKING	如果事务只包含了一个语句，则为 `1`（一个没有使用 `FOR UPDATE` 或者 `LOCK IN SHARED MODE` 的 `SELECT` 语句，并且 autocommit 是开启的）；如果这个值和 `TRX_IS_READ_ONLY` 同时为 `1`，则事务可以由存储引擎优化以减少一些开销

INNODB_LOCKS

这个表在 MySQL 5.7.14 中已经弃用，并且在 MySQL 8.0 中已经移除了（使用 performance_schema.data_lock_waits 代替，下篇文章将会讲解）。

列名	说明
LOCK_ID	锁的 ID，格式并不固定
LOCK_TRX_ID	持有锁的事务 ID，与 INNODB_TRX.TRX_ID 对应
LOCK_MODE	锁模式： S (共享锁), X (排它锁), IS (意向共享锁), IX (意向排它锁), S_GAP (共享间隙锁), X_GAP (排他间隙锁), IS_GAP (意向共享间隙锁), IX_GAP (意向排他间隙锁), AUTO_INC (自增的表级锁)
LOCK_TYPE	锁的类型：RECORD 或者 TABLE
LOCK_TABLE	被锁的表或者包含被锁的行的表
LOCK_SPACE	如果 LOCK_TYPE=RECORD，为表空间 ID，否则为 `NULL`
LOCK_INDEX	如果 LOCK_TYPE=RECORD，为索引名，否则为 `NULL`
LOCK_PAGE	如果 LOCK_TYPE=RECORD，为被锁的记录所在的页号，否则为 `NULL`
LOCK_REC	如果 LOCK_TYPE=RECORD，为被锁的记录的堆号，否则为 `NULL`
LOCK_DATA	如果 LOCK_TYPE=RECORD，为被锁的记录的主键（作为 SQL 字符串），否则为 `NULL`。如果没有主键，则使用 InnoDB 内部的 row_id。为了避免不必要的 IO，如果被锁记录的页不在缓冲池中，则个字段也为 `NULL`

INNODB_LOCK_WAITS

INNODB_LOCK_WAITS 表包含了关于阻塞的 InnoDB 事务的信息。