mysql 事务 定位_如何使用mysql 5.6 information schema定位事务锁信息

1，数据库版本[root@standbygtid ~]# mysql -Vmysql Ver 14.14 Distrib 5.6.25, for Linux (x86_64) using EditLine wrapper2，登陆mysql[root@standbygtid ~]# mysql -uroot -psystem3，显示事务及锁相关的表(注：有ORACLE从业经验的同学，类似于oracle 动态性能视图v$session及locked_objects)mysql> use information_schema;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql>mysql> show tables like 'INNODB%';+----------------------------------------+| Tables_in_information_schema (INNODB%) |+----------------------------------------+| INNODB_LOCKS || INNODB_TRX |略| INNODB_LOCK_WAITS |+----------------------------------------+28 rows in set (0.00 sec)4，上述几个表的含义---锁表(注：锁是什么，就是你需要某种资源，但此时由人家占着，你需要等待，这就是一种锁，锁的目标就是维护数据一致性)mysql> desc innodb_locks;+-------------+---------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------------+---------------------+------+-----+---------+-------+| lock_id | varchar(81) | NO | | | | 锁编号| lock_trx_id | varchar(18) | NO | | | | 锁所属事务| lock_mode | varchar(32) | NO | | | | 锁模式| lock_type | varchar(32) | NO | | | | 锁类型| lock_table | varchar(1024) | NO | | | | 锁对应表| lock_index | varchar(1024) | YES | | NULL | | 锁对应索引| lock_space | bigint(21) unsigned | YES | | NULL | | 锁空间| lock_page | bigint(21) unsigned | YES | | NULL | | 锁对应的页面| lock_rec | bigint(21) unsigned | YES | | NULL | | 锁对应的表记录| lock_data | varchar(8192) | YES | | NULL | |+-------------+---------------------+------+-----+---------+-------+10 rows in set (0.00 sec)---事务表mysql> desc innodb_trx;+----------------------------+---------------------+------+-----+---------------------+-------+| Field | Type | Null | Key | Default | Extra |+----------------------------+---------------------+------+-----+---------------------+-------+| trx_id | varchar(18) | NO | | | | 事务编号| trx_state | varchar(13) | NO | | | | 事务状态| trx_started | datetime | NO | | 0000-00-00 00:00:00 | | 事务开始时间| trx_requested_lock_id | varchar(81) | YES | | NULL | | 事务请求锁编号| trx_wait_started | datetime | YES | | NULL | | 事务等待开始时间| trx_weight | bigint(21) unsigned | NO | | 0 | | 事务权重| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | | 事务对应的线程| trx_query | varchar(1024) | YES | | NULL | | 事务所属的SQL语句| trx_operation_state | varchar(64) | YES | | NULL | || trx_tables_in_use | bigint(21) unsigned | NO | | 0 | || trx_tables_locked | bigint(21) unsigned | NO | | 0 | || trx_lock_structs | bigint(21) unsigned | NO | | 0 | || trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | || trx_rows_locked | bigint(21) unsigned | NO | | 0 | || trx_rows_modified | bigint(21) unsigned | NO | | 0 | || trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | || trx_isolation_level | varchar(16) | NO | | | || trx_unique_checks | int(1) | NO | | 0 | || trx_foreign_key_checks | int(1) | NO | | 0 | || trx_last_foreign_key_error | varchar(256) | YES | | NULL | || trx_adaptive_hash_latched | int(1) | NO | | 0 | || trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | || trx_is_read_only | int(1) | NO | | 0 | || trx_autocommit_non_locking | int(1) | NO | | 0 | |+----------------------------+---------------------+------+-----+---------------------+-------+24 rows in set (0.01 sec)---锁等待表mysql> desc innodb_lock_waits;+-------------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------------------+-------------+------+-----+---------+-------+| requesting_trx_id | varchar(18) | NO | | | | 请求锁事务编号| requested_lock_id | varchar(81) | NO | | | | 请求锁编号| blocking_trx_id | varchar(18) | NO | | | | 持锁事务编号| blocking_lock_id | varchar(81) | NO | | | | 持锁 锁编号+-------------------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)5，为了模拟事务，关闭自动提交(注：生产系统一定要关闭，防止不小心在生产系统产生误操作无法撤回)mysql> show variables like '%autocommit%';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | ON |+---------------+-------+1 row in set (0.00 sec)mysql> set autocommit=off;Query OK, 0 rows affected (0.01 sec)mysql> show variables like '%autocommit%';+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit | OFF |+---------------+-------+1 row in set (0.00 sec)6，产生一个事务--新开一个登陆会话，不提交(注：如果一提交，则事务马上消失)mysql> update zxydb.t_go set a=3;Query OK, 16778789 rows affected (1 min 0.91 sec)Rows matched: 25168933 Changed: 16778789 Warnings: 0--在另一会话查看线程信息mysql> show processlist;+----+------+-----------+--------------------+---------+------+----------+---------------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+-----------+--------------------+---------+------+----------+---------------------------+| 28 | root | localhost | information_schema | Query | 19 | updating | update zxydb.t_go set a=3 || 29 | root | localhost | NULL | Query | 0 | init | show processlist |+----+------+-----------+--------------------+---------+------+----------+---------------------------+2 rows in set (0.00 sec)--查看事务表mysql> select * from information_schema.innodb_trx\G;*************************** 1. row ***************************trx_id: 3996trx_state: RUNNING 事务运行状态trx_started: 2019-11-06 05:46:18 事务开始的时间trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 25224373trx_mysql_thread_id: 28 事务所属的线程，对应上述的show processlist之id列trx_query: NULLtrx_operation_state: NULLtrx_tables_in_use: 0trx_tables_locked: 0trx_lock_structs: 55440trx_lock_memory_bytes: 8042024trx_rows_locked: 25224372trx_rows_modified: 25168933 事务影响的表记录数trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READ 事务隔离级别trx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 10000trx_is_read_only: 0trx_autocommit_non_locking: 01 row in set (0.03 sec)ERROR:No query specified--由上可见如果没有竞争资源时，不会产生锁(注：产生锁的前提条件是必须在2个会话以上，当然不包括mysql自身产生的bug)mysql> select * from information_schema.innodb_locks\G;Empty set (0.04 sec)ERROR:No query specified--没有竞资源，当然也不会产生锁等待mysql> select * from information_schema.innodb_lock_waits\G;Empty set (0.03 sec)ERROR:No query specified7，再开启一个新事务会话(注：更新上述同一个表的记录，即会产生锁等待，因为需要更新同一个表的记录资源)mysql> set autocommit=off;Query OK, 0 rows affected (0.01 sec)mysql> insert into zxydb.t_go select 3,3;----可见产生了锁信息mysql> select * from information_schema.innodb_locks\G;*************************** 1. row ***************************lock_id: 3997:6:55726:1lock_trx_id: 3997lock_mode: X 锁模式，x表示排它锁，s表示共享锁lock_type: RECORDlock_table: `zxydb`.`t_go` 锁定表lock_index: GEN_CLUST_INDEX GEN_CLUST_INDEX表示表级锁lock_space: 6lock_page: 55726lock_rec: 1lock_data: supremum pseudo-record*************************** 2. row ***************************lock_id: 3996:6:55726:1lock_trx_id: 3996lock_mode: Xlock_type: RECORDlock_table: `zxydb`.`t_go`lock_index: GEN_CLUST_INDEXlock_space: 6lock_page: 55726lock_rec: 1lock_data: supremum pseudo-record2 rows in set (0.03 sec)ERROR:No query specified---同时也产生锁等待信息mysql> select * from information_schema.innodb_lock_waits\G;*************************** 1. row ***************************requesting_trx_id: 3997 请求锁的事务idrequested_lock_id: 3997:6:55726:1blocking_trx_id: 3996 持锁的事务idblocking_lock_id: 3996:6:55726:11 row in set (0.03 sec)ERROR:No query specified8，为了方便监控锁等待的信息，可以编写下述SQL语句---获取持锁会话及等待锁会话更详细的信息select trx.trx_mysql_thread_id,trx.trx_id,trx.trx_state,trx.trx_started,trx.trx_query,locks.lock_type,locks.lock_table,lock_waits.requesting_trx_id,lock_waits.blocking_trx_idfrom information_schema.innodb_trx trx inner join information_schema.innodb_locks lockson trx.trx_id=locks.lock_trx_idinner join information_schema.innodb_lock_waits lock_waitson trx.trx_id=lock_waits.requesting_trx_idinner join information_schema.innodb_lock_waits lock_waitson trx.trx_id=lock_waits.blocking_trx_id;可知，3997事务是等待锁，而3996是持锁，所以如果你想让3997可以继续工作，有几种方法：1，继续等待3996事务完成2，完成3996事务3，杀死3996事务(操作语句为:kill 28,28为事务所属的线程)+---------------------+--------+-----------+---------------------+-----------------------------------+-----------+----------------+-------------------+-----------------+| trx_mysql_thread_id | trx_id | trx_state | trx_started | trx_query | lock_type | lock_table | requesting_trx_id | blocking_trx_id |+---------------------+--------+-----------+---------------------+-----------------------------------+-----------+----------------+-------------------+-----------------+| 30 | 3997 | LOCK WAIT | 2019-11-06 05:51:04 | insert into zxydb.t_go select 3,3 | RECORD | `zxydb`.`t_go` | 3997 | 3996 || 28 | 3996 | RUNNING | 2019-11-06 05:46:18 | NULL | RECORD | `zxydb`.`t_go` | NULL | NULL |+---------------------+--------+-----------+---------------------+-----------------------------------+-----------+----------------+-------------------+-----------------+2 rows in set (0.04 sec)