关于mysql一些锁问题的总结

大家都知道,锁是可以阻碍读写的,锁越多,数据库并发性能就越差,所以我们要随时准备监测锁的问题.

当然根本问题还是要开发要控制好锁粒度,把where条件写好,这样才能减少锁的影响.

另外,死锁其实不可怕,因为mysql内部发现死锁会发生回滚操作,所以不会真的"死"了,可怕的是锁等待,并发太高下一大堆语句等着某个语句释放锁,这才是严重阻碍效率的情况.

当然了,也不是说死锁的问题不重要,如果出现大量的死锁,那显然就是业务逻辑的问题了,需要和开发人员好好谈谈,不是随便谁谁能解决的事情.

查看事务隔离级别

首先,我们知道mysql有四种隔离级别,不同隔离级别,锁的等级也不一样,所以,查看锁的信息之前一定要先看当前的隔离级别是什么.

<code>mysql> </code><code>select</code> <code>@@global.tx_isolation,@@tx_isolation; </code>

<code>| @@global.tx_isolation | @@tx_isolation |</code>

<code>| REPEATABLE-READ | REPEATABLE-READ |</code>

当前是RR的隔离级别

当然,除了改配置文件,你是能在线改的,下面是改成RC级别,注意[ global | session ]区别:

<code>mysql></code><code>set</code> <code>global transaction isolation level READ COMMITTED;</code>

<code>mysql></code><code>set</code> <code>global tx_isolation=</code><code>'read-committed'</code><code>;</code>

查看锁信息

在mysql里面有两个记录数据库性能的库information_schema和performance_schema,是专门记录mysql其他库的事务ID、锁信息、锁等待时间、缓存使用情况、sql执行信息等等信息,现在我们只看我们需要的信息。而performance_schema在5.7之前是默认不开启的，因为会占用性能，而在5.7之后是默认开的。

<code>#查看innodb事务ID,会显示是什么操作和一些常规信息,例如是否在运行running,还是等待锁.</code>

<code>SELECT * FROM information_schema.INNODB_TRX\G</code>

<code>#查看当前innodb的锁的信息,会显示是什么锁类型,属于那个事务ID，通常只显示行锁</code>

<code>SELECT * FROM information_schema.innodb_locks\G</code>

<code>#查看innodb锁的等待时间,和等待的是那的是那个事务ID的锁</code>

<code>select</code> <code>* from information_schema.innodb_lock_waits\G</code>

<code>#查看metadata lock信息,即元数据锁,这种锁会让任何语句都不能操作这个表,包括表结构</code>

<code>select</code> <code>* from information_schema.processlist where state = </code><code>'Waiting for table metadata lock'</code><code>;</code>

<code>#上面这些语句要是查不到数据,那就证明当前没有响应的锁信息,查到了那就是有了.</code>

<code>#要想查询历史数据来统计性能,可以用下面这两个语句来计算,计算方法可以另外查查</code>

<code>show status like </code><code>'%lock%'</code><code>;</code>

<code>show variables like </code><code>'%timeout%'</code><code>;</code>

<code>#查看表锁，上面那些sql未必会显示出来</code>

<code>show OPEN TABLES where In_use > 0;</code>

<code>#handles锁，可以理解为要执行写操作的表锁，所以会排除其他所有sql的读写</code>

<code>select</code> <code>* from performance_schema.table_handles where OWNER_THREAD_ID > 0;</code>

<code>#查看那个线程加表锁</code>

<code>select</code> <code>pth.* from performance_schema.table_handles as pt </code><code>join</code> <code>performance_schema.threads as pth on pt.OWNER_THREAD_ID=pth.thread_id where pt.OWNER_THREAD_ID > 0 \G</code>

<code>#当前正在干活的线程在执行什么语句以及相关的信息，也就是确认那些sql在锁数据</code>

<code>select</code> <code>* from performance_schema.events_statements_current\G</code>

<code>#查看当前会话信息，有时候可以分析下会话的操作</code>

<code>select</code> <code>* from sys.session\G</code>

查看死锁信息

注意,查询死锁的信息的语句,记录的是最后一条的死锁信息,之前的是没记录下来的,

查询当前死锁信息的命令:

<code>show engine innodb status\G</code>

如果有DEADLOCK标识,也就是说出现过死锁,如果没有,也就是没出现过.

这个界面只会记录最后一个死锁,不是只记录一个,所以你能看到的死锁语句只有一条而且是最近出现的一条,这个要注意一下.

除非你在配置文件my.cnf里面加入下面参数,那就会将死锁记录到mysql的报错文件中，例如默认的mysql.err里面

<code>innodb_print_all_deadlocks = 1</code>

也正如开头说的，InnoDB中死锁会自动被检测出，并选择代价较小的事务进行回滚以打破死锁。事务完全回滚后其保持的锁被全部释放，若是仅有单条SQL由于错误发生了回滚则语句保持的锁可能不会被释放，因为InnoDB中不保存哪条语句持有哪些锁的信息。若事务中的select调用了存储函数，函数中的SQL执行失败，则该语句被回滚。

因此,死锁并不危险但频繁出现就有问题了。应用中应做好出现死锁导致事务回滚后的后续处理逻辑,频繁出现就应该和开发沟通去修改业务逻辑避免死锁。

查找锁相关的语句的线程号

很多时我们说假如有个锁等待实在太长,已经不想去执行他,但是他严重阻碍了后面重要语句的执行,那怎么办呢?虽然说能kill掉语句,但是又不确定那条跟他有关系,所以就很头痛,这个时候可以用下面这条语句

select `r`.`trx_wait_started` AS `wait_started`,timediff(now(),`r`.`trx_wait_started`) AS `wait_age`,timestampdiff(SECOND,`r`.`trx_wait_started`,now()) AS `wait_age_secs`,`rl`.`lock_table` AS `locked_table`,`rl`.`lock_index` AS `locked_index`,`rl`.`lock_type` AS `locked_type`,`r`.`trx_id` AS `waiting_trx_id`,`r`.`trx_started` AS `waiting_trx_started`,timediff(now(),`r`.`trx_started`) AS `waiting_trx_age`,`r`.`trx_rows_locked` AS `waiting_trx_rows_locked`,`r`.`trx_rows_modified` AS `waiting_trx_rows_modified`,`r`.`trx_mysql_thread_id` AS `waiting_pid`, `r`.`trx_query` AS `waiting_query`,`rl`.`lock_id` AS `waiting_lock_id`,`rl`.`lock_mode` AS `waiting_lock_mode`,`b`.`trx_id` AS `blocking_trx_id`,`b`.`trx_mysql_thread_id` AS `blocking_pid`, `b`.`trx_query` AS `blocking_query`,`bl`.`lock_id` AS `blocking_lock_id`,`bl`.`lock_mode` AS `blocking_lock_mode`,`b`.`trx_started` AS `blocking_trx_started`,timediff(now(),`b`.`trx_started`) AS `blocking_trx_age`,`b`.`trx_rows_locked` AS `blocking_trx_rows_locked`,`b`.`trx_rows_modified` AS `blocking_trx_rows_modified`,concat('KILL QUERY ',`b`.`trx_mysql_thread_id`) AS `sql_kill_blocking_query`,concat('KILL ',`b`.`trx_mysql_thread_id`) AS `sql_kill_blocking_connection` from ((((`information_schema`.`innodb_lock_waits` `w` join `information_schema`.`innodb_trx` `b` on((`b`.`trx_id` = `w`.`blocking_trx_id`))) join `information_schema`.`innodb_trx` `r` on((`r`.`trx_id` = `w`.`requesting_trx_id`))) join `information_schema`.`innodb_locks` `bl` on((`bl`.`lock_id` = `w`.`blocking_lock_id`))) join `information_schema`.`innodb_locks` `rl` on((`rl`.`lock_id` = `w`.`requested_lock_id`))) order by `r`.`trx_wait_started`\G

这条语句会计算出当前锁的关联信息,如果你不想那个锁继续执行,那就执行最后显示的kill命令就可以了.

附录一些information_schema 表结构的信息

<code>information_schema > desc innodb_locks;</code>

<code> </code><code>information_schema > desc innodb_lock_waits;</code>

<code>information_schema > desc innodb_trx ;</code>

<code>| trx_unique_checks | int(1) | NO | | 0 | |</code><code>#是否唯一性检查</code>

<code>| trx_foreign_key_checks | int(1) | NO | | 0 | |</code><code>#是否外键检查</code>

<code>| trx_adaptive_hash_latched | int(1) | NO | | 0 | |</code><code>#</code>

XA事务锁

严格来说，XA事务分为内部XA和外部XA，我们常说的XA事务其实指的是外部XA事务，内部XA一般只涉及binlog提交，和外部XA没关联。

外部xa事务是分布式事务的意思，可以实现不同数据库同步查询和修改数据的目的，mysql很久之前就支持XA事务了，不过仅支持innodb引擎。在5.7之前性能都非常差，并难以保证数据一致性，所以几乎没人用，在5.7较后的版本中，mysql修复了XA事务的bug，数据一致性得到了很大得提高，所以就可以提上使用的日情了。但是，性能还是很差。。。

所谓不同数据库的同步，可以同是mysql，也可以是mysql+sql server或mysql+oracle。不过无论你是什么组合都好，都必须要有一个XA事务控制器，用来统筹XA事务的锁和提交、回滚，可以是用开源的，也可以是你自己写的（技术足够NB）。

而我们用的环境是mysql+sql server外加spring could的XA事务控制器，怎么安装我就不多说了，这篇文章也不是重点介绍这个方面，重点来说XA事务锁的问题。

好了，回归正题，XA事务锁是个让人很头痛的问题，因为动不动就是来个表锁，为什么说性能很差，也正是因为并发大的时候会出现很多表锁，而表锁的粒度太大，直接就影响了并发。

而且XA的bug虽然修复了，但是机制的缺陷还是存在，mysql没有记录外部xa日志，在5.7也只是prepare和commit的时候才记录binlog，粒度还不够。xa事务在你控制器断掉了之后，事务节点会一直死等你控制器起来。如果节点自己有日志可以判断事务是要回滚还是提交就自己做，他自己没办法判断就要等控制器，一直等的结果可能就是挂死一个表的锁，如果是读锁就阻碍alter，如果是写锁就完全拒绝别的表操作，最终导致服务不能使用，这是坑点。

说了那么多，是怎么查XA事务锁呢，上面那些语句是查询一般sql的锁，XA事务的锁，是无能为力的，也就只有查到一些事务在跑，但是没有任何信息，你也不能判断他是否XA事务，因为XA事务有专门的命令提供：

<code>#启动XA事务</code>

<code>XA {START|BEGIN} xid [JOIN|RESUME]</code>

<code>#完成XA事务</code>

<code>XA END xid [SUSPEND [FOR MIGRATE]]</code>

<code>#PREPARE这个XA事务</code>

<code>XA PREPARE xid</code>

<code>#提交这个XA事务</code>

<code>XA COMMIT xid [ONE PHASE]</code>

<code>#回滚这个XA事务</code>

<code>XA ROLLBACK xid</code>

<code>#查看正在运行的XA事务</code>

<code>XA RECOVER [CONVERT XID]</code>

来看看实际例子：

<code>#一般情况下，你用查锁的语句是查不到的，因为他不是mysql的锁，而是XA的锁</code>

<code>mysql> SELECT * FROM information_schema.innodb_locks\G</code>

<code>Empty </code><code>set</code><code>, 1 warning (0.00 sec)</code>

<code>#但是你去查事务的话，倒是能查到出来，但是没任何信息，意义有点比较虚</code>

<code>mysql> SELECT * FROM information_schema.INNODB_TRX\G</code>

<code> </code><code>trx_state: RUNNING</code>

<code> </code><code>trx_started: 2017-09-28 22:58:19</code>

<code> </code><code>trx_requested_lock_id: NULL</code>

<code> </code><code>trx_wait_started: NULL</code>

<code> </code><code>trx_weight: 9</code>

<code> </code><code>trx_mysql_thread_id: 0</code>

<code> </code><code>trx_query: NULL</code>

<code> </code><code>trx_operation_state: NULL</code>

<code> </code><code>trx_tables_in_use: 0</code>

<code> </code><code>trx_tables_locked: 2</code>

<code> </code><code>trx_lock_structs: 2</code>

<code> </code><code>trx_lock_memory_bytes: 1136</code>

<code> </code><code>trx_rows_locked: 0</code>

<code> </code><code>trx_rows_modified: 7</code>

<code> </code><code>trx_concurrency_tickets: 0</code>

<code> </code><code>trx_isolation_level: REPEATABLE READ</code>

<code> </code><code>trx_unique_checks: 1</code>

<code> </code><code>trx_foreign_key_checks: 1</code>

<code>trx_last_foreign_key_error: NULL</code>

<code> </code><code>trx_adaptive_hash_latched: 0</code>

<code> </code><code>trx_adaptive_hash_timeout: 0</code>

<code>trx_autocommit_non_locking: 0</code>

<code>#但是并不是语句有问题，而是根本没用对</code>

<code>#XA就是要用XA的语句</code>

<code>mysql> xa recover;</code>

<code>#连续查几次，这个XA事务还在，基本上可以肯定这个XA事务是挂起的了，怎么办？</code>

<code>#那就回滚吧，注意逗号的位置</code>

<code>mysql> XA ROLLBACK </code><code>'192.168.0.3.tm150617699467628158'</code><code>,</code><code>'192.168.0.3.tm1123145'</code><code>,1096044365;</code>

<code>Query OK, 0 rows affected (0.00 sec)</code>

<code>Empty </code><code>set</code> <code>(0.00 sec)</code>

<code>#彻底清空了</code>

<code>#再去尝试查这个事务，就不存在了</code>

这个时候，锁没了，系统又恢复正常了。一个正常的XA事务，虽然是表锁，但是执行时间一般不会很长，所以长时间挂起的并不多，这类一般就建议回滚，因为你也不知道究竟提交后会影响什么数据，或者说你根本不知道能不能提交。

本文转自arthur376 51CTO博客，原文链接：http://blog.51cto.com/arthur376/1773050，如需转载请自行联系原作者

关于mysql一些锁问题的总结

继续阅读

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

mysql使用source命令导入.sql文件

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述