这篇文章将为大家详细讲解有关怎么使用mysql 5.6 information schema定位事务锁信息，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

引子mysql数据库在运行期间，随着业务体量增加及并发会话陡升，可能随时会出现各种性能问题。其中比较常见的一种现象，某一天公司业务人员或客户反馈说某个业务模板突然卡住了，或者开发同学说某个SQL语句不能继续运行了。mysql5.6引入的information_schema数据库，可以完美解决上述的问题。它提供一系列的数据视图或表，便于诊断及分析数据库的各种各样的性能问题，对于运维同学真是大大福利。本文主要介绍information_schema与锁相关的几个表，快速定位是哪些会话或事务导致事务操作不能持续。概念information-schema是一个内置的数据库，通过一系列的表，比如：锁方面的表，字符集相关的表，插件相关的表，进程相关的表，视图相关的表，不一而足。运维人员可以通过不同的表的信息，有助于分析各种各样的性能问题。当然，可以结合另一个数据库performance_schema数据库，更方便诊断数据库的各种各样的性能问题甚至故障情形。mysqlinformation-schema官方手册，请查阅如下链接https://dev.mysql.com/doc/refman/5.6/en/information-schema.htmlmysql锁相关官方手册，请查阅如下链接https://dev.mysql.com/doc/refman/5.6/en/innodb-locking-transaction-model.htmlinformation-schema实践1，数据库版本[root@standbygtid~]#mysql-VmysqlVer14.14Distrib5.6.25,forLinux(x86_64)usingEditLinewrapper2，登陆mysql[root@standbygtid~]#mysql-uroot-psystem3，显示事务及锁相关的表（注：有ORACLE从业经验的同学，类似于oracle动态性能视图v$session及locked_objects）mysql>useinformation_schema;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>mysql>showtableslike'INNODB%';+----------------------------------------+|Tables_in_information_schema(INNODB%)|+----------------------------------------+|INNODB_LOCKS||INNODB_TRX|略|INNODB_LOCK_WAITS|+----------------------------------------+28rowsinset(0.00sec)4，上述几个表的含义---锁表（注：锁是什么，就是你需要某种资源，但此时由人家占着，你需要等待，这就是一种锁，锁的目标就是维护数据一致性）mysql>descinnodb_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||+-------------+---------------------+------+-----+---------+-------+10rowsinset(0.00sec)---事务表mysql>descinnodb_trx;+----------------------------+---------------------+------+-----+---------------------+-------+|Field|Type|Null|Key|Default|Extra|+----------------------------+---------------------+------+-----+---------------------+-------+|trx_id|varchar(18)|NO||||事务编号|trx_state|varchar(13)|NO||||事务状态|trx_started|datetime|NO||0000-00-0000: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||+----------------------------+---------------------+------+-----+---------------------+-------+24rowsinset(0.01sec)---锁等待表mysql>descinnodb_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||||持锁锁编号+-------------------+-------------+------+-----+---------+-------+4rowsinset(0.00sec)5，为了模拟事务，关闭自动提交（注：生产系统一定要关闭，防止不小心在生产系统产生误操作无法撤回）mysql>showvariableslike'%autocommit%';+---------------+-------+|Variable_name|Value|+---------------+-------+|autocommit|ON|+---------------+-------+1rowinset(0.00sec)mysql>setautocommit=off;QueryOK,0rowsaffected(0.01sec)mysql>showvariableslike'%autocommit%';+---------------+-------+|Variable_name|Value|+---------------+-------+|autocommit|OFF|+---------------+-------+1rowinset(0.00sec)6，产生一个事务--新开一个登陆会话，不提交（注：如果一提交，则事务马上消失）mysql>updatezxydb.t_goseta=3;QueryOK,16778789rowsaffected(1min0.91sec)Rowsmatched:25168933Changed:16778789Warnings:0--在另一会话查看线程信息mysql>showprocesslist;+----+------+-----------+--------------------+---------+------+----------+---------------------------+|Id|User|Host|db|Command|Time|State|Info|+----+------+-----------+--------------------+---------+------+----------+---------------------------+|28|root|localhost|information_schema|Query|19|updating|updatezxydb.t_goseta=3||29|root|localhost|NULL|Query|0|init|showprocesslist|+----+------+-----------+--------------------+---------+------+----------+---------------------------+2rowsinset(0.00sec)--查看事务表mysql>select*frominformation_schema.innodb_trx\G;***************************1.row***************************trx_id:3996trx_state:RUNNING事务运行状态trx_started:2019-11-0605:46:18事务开始的时间trx_requested_lock_id:NULLtrx_wait_started:NULLtrx_weight:25224373trx_mysql_thread_id:28事务所属的线程，对应上述的showprocesslist之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:REPEATABLEREAD事务隔离级别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:01rowinset(0.03sec)ERROR:Noqueryspecified--由上可见如果没有竞争资源时，不会产生锁（注：产生锁的前提条件是必须在2个会话以上，当然不包括mysql自身产生的bug）mysql>select*frominformation_schema.innodb_locks\G;Emptyset(0.04sec)ERROR:Noqueryspecified--没有竞资源，当然也不会产生锁等待mysql>select*frominformation_schema.innodb_lock_waits\G;Emptyset(0.03sec)ERROR:Noqueryspecified7，再开启一个新事务会话（注：更新上述同一个表的记录，即会产生锁等待，因为需要更新同一个表的记录资源）mysql>setautocommit=off;QueryOK,0rowsaffected(0.01sec)mysql>insertintozxydb.t_goselect3,3;----可见产生了锁信息mysql>select*frominformation_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_INDEXGEN_CLUST_INDEX表示表级锁lock_space:6lock_page:55726lock_rec:1lock_data:supremumpseudo-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:supremumpseudo-record2rowsinset(0.03sec)ERROR:Noqueryspecified---同时也产生锁等待信息mysql>select*frominformation_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:11rowinset(0.03sec)ERROR:Noqueryspecified8，为了方便监控锁等待的信息，可以编写下述SQL语句---获取持锁会话及等待锁会话更详细的信息selecttrx.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_idfrominformation_schema.innodb_trxtrxinnerjoininformation_schema.innodb_lockslocksontrx.trx_id=locks.lock_trx_idinnerjoininformation_schema.innodb_lock_waitslock_waitsontrx.trx_id=lock_waits.requesting_trx_idinnerjoininformation_schema.innodb_lock_waitslock_waitsontrx.trx_id=lock_waits.blocking_trx_id;可知，3997事务是等待锁，而3996是持锁，所以如果你想让3997可以继续工作，有几种方法：1，继续等待3996事务完成2，完成3996事务3，杀死3996事务（操作语句为:kill28,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|LOCKWAIT|2019-11-0605:51:04|insertintozxydb.t_goselect3,3|RECORD|`zxydb`.`t_go`|3997|3996||28|3996|RUNNING|2019-11-0605:46:18|NULL|RECORD|`zxydb`.`t_go`|NULL|NULL|+---------------------+--------+-----------+---------------------+-----------------------------------+-----------+----------------+-------------------+-----------------+2rowsinset(0.04sec)

培训课件

（收费20元）

关于“怎么使用mysql 5.6 information schema定位事务锁信息”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。