mysql 的子查询会导致锁表？ lock wait timeout exceeded; try restarting transactio的处理

问题

“lock wait timeout exceeded; try restarting transactio” 是非常常见的mysql异常。意思就是获取锁失败了，无法执行操作。

如何查看到底是哪个锁导致的，网上文章一搜一大把，不赘述。其实主要就是以下三张表就可以了。

select * from information_schema.innodb_locks;
select * from information_schema.innodb_trx;
select * from information_schema.innodb_lock_waits;

配合show full prcesslist基本可以定位了

今天说说一个update会锁子查询的问题。SQL如下

update A set afield1 = (select count(1) from B where id = A.id) where id = 123;
UPDATE A  SET afield1 = '有' WHERE bfield1 IN (SELECT DISTINCT bfield1 FROM B WHERE bfield1 = '1');

这两个sql都会导致 B表被锁定，可能有些读者会认为B表只是读取呀，不是只有写入的时候才会锁吗，为什么会锁呢？这牵扯一个事务的概念。

事务

事务是为了保证数据库的一致性和完整性所设立的概念。大学都学过事务有原子性、一致性、隔离性、原子性，其余的都好说好理解，其实容易产生理解问题的是隔离性。

因为隔离性并不好实现，在一个事务往往既有查询又有插入又有删除，在你查询之后，删除之前，数据往往发生改变，这会带来一个一些脏读，不可重复读等问题。因此最完美的隔离当然是从时间上隔离，一个事务执行完了，再执行另外一个事务,肯定完全不会产生问题。

但是而在实际生产过程中为了效率我们往往并不这样做，而是有选择的牺牲了部分隔离的特性来换取并发效率的提升。因此数据库隔离有四个级别分别是：

√: 可能出现 ×: 不会出现

隔离级别

脏读

不可重复读

幻读

Read uncommitted（读取未提交）

√

√

√

Read committed（读取提交）

×

√

√

Repeatable read（可重复读）

×

×

√

Serializable（串行）

×

×

×

上图中的Serializable就是按照时间完全串行执行的，最大程度的保证了隔离级别，但是带来的问题往往是效率很低。而Read uncommitted则因为能读取还未提交的数据，不符合隔离的初衷，应用场景寥寥。因此目前主流的数据库往往都采用了Read committed和Repeatable read。

不同数据库的隔离级别实现

不同的数据库有不同的倾向和实现，甚至于在这些隔离界别上还会再细化分出更细的隔离级别，会跟上面列出会稍有区别。比如db2还有什么Cursor Stability（游标稳定级别）.

但是主流的比如oracle、sql server 默认是下基本上是Read committed，因为这是业界比较公认的合适的，能够满足绝大部分的应用场景。但是，mysql比较特殊，它的隔离级别默认是Repeatable read。这给很多程序员其实造成了一些困扰。因为他有很大可能会造成子查询锁读表的情况，造成生产故障，因为mysql要保证在这个事务中每个select是可以重复读的会产生锁表

因此其实很多mysql的衍生版本比如阿里云的rds已经把隔离级别默认调整为Read committed

你可以使用下命令查看你的mysql的隔离级别:

SELECT @@tx_isolation

可以使用以下命令更改：

SET session TRANSACTION ISOLATION LEVEL READ COMMITTED;

回到问题

结论其实很清楚了，两个解决方案:

1、更改数据库事务隔离级别

2、执行操作前，先创建另外一张表B1，再做相应的更新关联，锁B1表，而不会锁B表。