Innodb实践总结（四）

8. 排查问题常用指令

8.1. 查看事务隔离级别

- SELECT @@global.tx_isolation;   

- SELECT @@session.tx_isolation;

- SELECT @@tx_isolation;

8.2. 设置隔离级别

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

例如：set session transaction isolation level read uncommitted;

8.3. 查看事务是否自动提交

SHOW VARIABLES LIKE '%AUTOCOMMIT%';   (1或者on为自动提交)

8.4. 查看表状态

- show table status like '表名';

8.5. 查看SQL性能

- set profiling=1;
- show profiles
  或
- show profile for query 1;（show profiles查询出来的id）

8.6. 事务及死锁分析

show engine innodb status;

8.7. 查看事务锁等待状态情况

- select * from information_schema.innodb_locks;

- select * from information_schema.innodb_lock_waits;

- select * from information_schema.innodb_trx;

9. 事务锁表举例分析

9.1. INFORMATION_SCHEMA INNODB_LOCKS表

INNODB_LOCKS表包含有关锁的信息，锁包含两种：InnoDB 事务请求但尚未获取的锁信息，以及事务处理没有的释放锁从而阻塞另一个事务获取该锁。但是只有出现锁竞争时，表中才会有数据，如果有一个或多个事务在同时进行，但并没有出现锁竞争，看不到数据。

• 首先给出查询当前锁的信息，然后对其进行解释：

    mysql> select * from information_schema.innodb_locks\G;
    *************************** 1. row ***************************
        lock_id: 523:0:307:3
    lock_trx_id: 523
      lock_mode: X
      lock_type: RECORD
     lock_table: `test`.`user`
     lock_index: `PRIMARY`
     lock_space: 0
      lock_page: 307
       lock_rec: 3
      lock_data: 5
    *************************** 2. row ***************************
        lock_id: 524:0:307:3
    lock_trx_id: 524
      lock_mode: X
      lock_type: RECORD
     lock_table: `test`.`user`
     lock_index: `PRIMARY`
     lock_space: 0
      lock_page: 307
       lock_rec: 3
      lock_data: 5
    2 rows in set (0.00 sec)

• 对其中的展示项进行分析

列名称	描述
LOCK_ID	唯一的锁ID号，InnoDB的内部ID。LOCK_ID包含TRX_ID，但LOCK_ID随时可能变化。不要编写解析LOCK_ID值的应用程序。
LOCK_TRX_ID	持有锁的事务ID。详细信息参考 INNODB_TRX表。
LOCK_MODE	锁的模式。允许值是 S[,GAP]，X[,GAP]， IS[,GAP]，IX[,GAP]， AUTO_INC，和 UNKNOWN。除了AUTO_INC和UNKNOWN以外，其他都允许包含间隙锁定（如果有）。
LOCK_TYPE	锁的类型。行级锁RECORD，表级锁TABLE。
LOCK_TABLE	已锁定或包含已锁定记录的表的名称。【数据库，表】
LOCK_INDEX	索引名称，前提LOCK_TYPE是 RECORD; 否则 NULL。
LOCK_SPACE	锁定记录的表空间ID，前提LOCK_TYPE是 RECORD; 否则 NULL。
LOCK_PAGE	锁定记录的页码，前提LOCK_TYPE是 RECORD; 否则 NULL。
LOCK_REC	页面内锁定记录的堆号，前提 LOCK_TYPE是RECORD; 否则NULL。
LOCK_DATA	与锁相关的数据（如果有的话）。如果LOCK_TYPE是 RECORD，则值是锁定记录的主键值，否则 NULL。此列包含锁定行中主键列的值，格式为有效的SQL字符串（可以复制到SQL语句）。如果没有主键，LOCK_DATA 则是唯一的InnoDB内部行ID号。

9.2 The INFORMATION_SCHEMA INNODB_TRX表

INNODB_TRX表包含有关当前正在执行的每个事务的信息 ，包括事务是否正在等待锁，事务何时开始以及事务正在执行的SQL语句（如果有）。

• 给出查询当前事务的信息，然后对其进行解释：

    mysql> select * from information_schema.innodb_trx\G;
    *************************** 1. row ***************************
                        trx_id: 524
                     trx_state: RUNNING
                   trx_started: 2017-11-08 22:19:05
         trx_requested_lock_id: NULL
              trx_wait_started: NULL
                    trx_weight: 3
           trx_mysql_thread_id: 52
                     trx_query: NULL
           trx_operation_state: NULL
             trx_tables_in_use: 0
             trx_tables_locked: 0
              trx_lock_structs: 2
         trx_lock_memory_bytes: 376
               trx_rows_locked: 1
             trx_rows_modified: 1
       trx_concurrency_tickets: 0
           trx_isolation_level: REPEATABLE READ
             trx_unique_checks: 1
        trx_foreign_key_checks: 1
    trx_last_foreign_key_error: NULL
     trx_adaptive_hash_latched: 0
     trx_adaptive_hash_timeout: 10000
    *************************** 2. row ***************************
                        trx_id: 523
                     trx_state: LOCK WAIT
                   trx_started: 2017-11-08 22:18:55
         trx_requested_lock_id: 523:0:307:3
              trx_wait_started: 2017-11-08 22:19:33
                    trx_weight: 4
           trx_mysql_thread_id: 51
                     trx_query: update user set k=500 where id=5
           trx_operation_state: starting index read
             trx_tables_in_use: 1
             trx_tables_locked: 1
              trx_lock_structs: 3
         trx_lock_memory_bytes: 376
               trx_rows_locked: 2
             trx_rows_modified: 1
       trx_concurrency_tickets: 0
           trx_isolation_level: REPEATABLE READ
             trx_unique_checks: 1
        trx_foreign_key_checks: 1
    trx_last_foreign_key_error: NULL
     trx_adaptive_hash_latched: 0
     trx_adaptive_hash_timeout: 10000
    2 rows in set (0.01 sec)

• 对其中的展示项进行分析

列名称	描述
TRX_ID	唯一的事务ID
TRX_WEIGHT	事务的权重，反映了（但不一定是确切的计数）改变的行数和事务锁定的行数。
TRX_STATE	事务执行状态。允许值是 RUNNING，LOCK WAIT， ROLLING BACK，和 COMMITTING。
TRX_STARTED	事务开始时间。
TRX_REQUESTED_LOCK_ID	如果TRX_STATE是LOCK WAIT，事务目前正在等待的锁的ID ; 否则NULL。
TRX_WAIT_STARTED	事务开始等待锁的时间，如果 TRX_STATE是LOCK WAIT; 否则NULL。
TRX_MYSQL_THREAD_ID	MySQL线程ID
TRX_QUERY	正在由事务执行的SQL语句。
TRX_OPERATION_STATE	事务当前操作（如果有的话）; 否则 NULL。
TRX_TABLES_IN_USE	InnoDB处理此事务的当前语句时使用的表的数量。
TRX_TABLES_LOCKED	InnoDB当前SQL语句具有行锁的表的数目。
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	自适应散列索引是否被当前事务锁定。（一次只有一个事务可以修改自适应散列索引。）
TRX_ADAPTIVE_HASH_TIMEOUT	是否立即放弃自适应散列索引的搜索锁存，或者跨越来自MySQL的调用保留它。当没有自适应哈希索引争用时，这个值保持为零，并且语句保留锁存器直到它们完成。在争用的时候，它倒数到零，并且在每一行查询之后语句立即释放锁存器。

9.3 The INFORMATION_SCHEMA INNODB_LOCK_WAITS表

INNODB_LOCK_WAITS表包含每个被阻止的InnoDB 事务的一个或多个行，指示它所请求的锁和任何阻止该请求的锁。

• 给出查询当前事务的信息，然后对其进行解释：

    mysql> select * from information_schema.innodb_lock_waits\G;
    *************************** 1. row ***************************
    requesting_trx_id: 523
    requested_lock_id: 523:0:307:3
      blocking_trx_id: 524
     blocking_lock_id: 524:0:307:3
    1 row in set (0.00 sec)

• 对其中的展示项进行分析

列名称	描述
REQUESTING_TRX_ID	请求事务的ID。
REQUESTED_LOCK_ID	事务正在等待的锁的ID
BLOCKING_TRX_ID	阻止事务的ID。
BLOCKING_LOCK_ID	阻止另一个事务继续进行的事务持有的锁的ID。

官方相关资料

https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlocks.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-lock-waits-table.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-table.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-trx-table.html

相关阅读：

Innodb实践总结（一）

Innodb实践总结（二）

Innodb实践总结（三）

本文来自网易实践者社区，经作者王志泳授权发布。