报错到溯源"/>
从报错到溯源
这个项目是个小项目，用户比较少，但是用户在这么少的情况下，还能出现锁等待超时的问题，由于这个项目还是部署在内网的，导致每次解决问题都只能去客户那边，难受。废话不多说，直接开讲。
首先这个报这个错的原因是：假设存在事务A和事务B，事务A一直在占用着某个资源，并且这个资源被上了锁，但是事务B也想要这个资源，那么事务B就等着事务A执行完，好把锁释放掉，把这个资源让给事务B，但是这事务A执行的时间太长了，导致事务B等待的时间也就太长了，等待时间超过了MySQL中默认的锁等待时间（50s），于是就报了这个错。
看过很多博文，大部分都是说的通过下面这条语句，查询出对应的Lock wait的那一行记录，然后找出出现锁等待的那个线程，然后使用kill，将其杀死。
select * from information_schema.INNODB_TRX;kill trx_mysql_thread_id(就是上面那条语句查出来的对应的线程id)然而这种方式只能说暂时的解决了锁等待的问题，难道每次出现锁等待我都去杀一次？这肯定是不现实的。所以为了根本性的解决问题，我决定开始查找出现这个问题的根本原因。
项目场景：
首先，项目中有导入数据的功能，需要各个用户对自己单位的数据进行导入，那么在导入的过程中就需要频繁的更新数据，当然，多个人可以同时导入数据，那么同时导入数据，按照我们的设计，每个人去一张表中更新属于自己的那一行数据，按照想法来说是没有问题的，但是，偏偏就是这里出了问题。
问题描述：
由于报错的异常栈打印的内容太多，所以我截取了关键部分。报错情况如下：
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:Lock wait timeout exceeded;try restarting...
...
...
update table(具体的表) set status="XXX" where project_id = 3 and name = "XX" and status not in("XXXX");
...
...从报错来看，锁等待的问题应该是出现在这条sql语句上，那么为什么这样一条简单的sql，也会出现锁等待的情况呢，接下来分析一下原因。
原因分析：
在这张table表中，我们为project_id这个字段建立了索引，但project_id不是主键，并且在这张表中，project_id的值存在重复的，也就是说，这张表中的多条记录，可能都是project_id=3的记录。但是name字段却没有建立索引，而这条sql的目的是，用project_id和name来唯一确定一条记录进行更新，但是我却只给project_id建立了索引，难道是因为索引导致的锁等待？
于是我在navicat上测试了一下：
//------------------------------------A窗口------------------------------------//首先打开一个查询窗口，查询事务的自动提交是否打开
show variables like 'autocommit'// 关闭事务的自动提交
set autocommit = 0// 把那条有问题的sql拿到这里
update table set status = 'XXX' where project_id = 3 and name = "XX" and status not in ("XXXX");// 提交事务
commit;//------------------------------------B窗口------------------------------------// 注意：A窗口中的更新语句和B窗口中的更新语句，name不同，但是project_id是相同的
update table set status = 'XXX' where project_id = 3 and name = "YY" and status not in ("XXXX");commit;我先执行A窗口中的update的语句，但是不提交事务（也就是不执行commit;），然后再去B窗口执行update语句，但是要提交事务（执行commit;）。然后可以清楚的看到B窗口出现了锁等待 ：
在出现这样的情况时，我打开了C窗口，通过这两条语句查看情况：
# 此表是一个事务表
select * from information_schema.INNODB_TRX;# 查看锁的相关信息
select * from INFORMATION_SCHEMA.INNODB_LOCKS;# 如果需要查看两个表中详细的字段信息，可以看这篇博客
# 首先是查询INNODB_TRX表得到的结果：
可以看到里面有一条处于RUNNING状态，而另一条则出现了LOCK WAIT状态，那么这个线程到底触发了mysql的哪个锁呢？我们继续看下面一条sql带来的信息
查询INNODB_LOCKS表得到的结果：
这张表中显示了，lock_mode(锁模式)是排他锁（也叫写锁），锁类型是记录锁（行锁中算法的一种），那么根据这两个信息，我们可以得知，现在锁的级别是行锁，那么是什么原因导致的这样一个行锁呢？（如果想要了解其他具体锁的内容的，推荐这篇文章： ）
从上面我们知道，现在锁类型是记录锁，他是单个行记录上的锁，这个记录锁总会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。（此段来自MySQL技术内幕 InnoDB存储引擎第二版 第6章-锁 P266）
由于我给project_id建立的索引，并且这个索引列存在重复的数据，那么他就会先将project_id=3的这些行都锁起来，然后再将这些行的记录通过name和status进行筛选，在这个过程中，如果数据量小，可能不会出现锁等待的情况，但是由于项目场景是导入，导入的时候，假设A可能数据很多，几千条数据进行导入，那么这个人就会存在一直更新的情况，占用着project_id=3的这些行进行数据更新，那么除A以外的人导入则只能等待，那么当除A以外的人导入时等待的时间超过了mysql默认的锁等待时间（50s），那么就会出现这个锁等待的报错。
那么到此为止，我已经把出现这个报错的原因讲完了，在我这个项目中，出现锁等待报错的根本原因是索引建立不当导致的，也有可能是表设计不合理导致的，那么在项目中，我们都需要去注意这样一些问题。
解决方案：
对于我们现在这样的情况，我们如果对数据库表进行重新设计，那么会耗费大量的时间，或许还会出现其他新的bug，那么最好的解决方式就是，对project_id和name这两个字段建立联合索引，那么innodb在进行查找更新时就会进行索引优化，通过联合索引来查找对应的那一行数据，就不会锁住project_id=3的所有行了。
结语：
那么文章到这里就结束了，希望大家在做项目的时候都少写点bug哈哈哈哈！！！如果对文章内容有什么意见或者建议的朋友，还麻烦你们在评论区指正，谢谢啦。
更多推荐
从报错到溯源
发布评论