mysql中的锁的问题_关于mysql一些锁问题的总结

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

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

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

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

查看事务隔离级别

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

当前是RR的隔离级别

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

查看锁信息

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

查看死锁信息

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

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

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

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

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

也正如开头说的，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表结构的信息

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事务有专门的命令提供：

来看看实际例子：

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

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