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实践总结(三)

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