MySQL引擎事务锁冲突分析

一、问题描述

       某次在业务系统中，遇到了一个更新记录时的锁冲突问题。然而，其他会话并没有对这些记录进行任何操作。在MySQL中，更新和删除操作默认是行级别的锁定。如果没有为匹配条件创建索引，那么这些操作最多是全表扫描，也不会导致全表锁。在这种情况下，表锁是从何而来的呢？和业务系统人员对问题沟通后自己测试复现如下：

        a、首先在session1中先插入一条记录test1。
        b、然后在同一个会话中打开事务，插入记录test2，此时事务不提交。

         c、在session2中查询表只有test1的记录，对这条记录进行删除操作，发现存在锁，导致无法正常删除。

        d、按理来说test1这条记录已经提交，session2中不应该存在锁，mysql的事务等级是RC。

        如果刚好有session3会话继续执行insert操作，会等待表锁的释放，此时业务系统很容易产生死锁。

二、原因分析

        检查所有的操作都规范，发现test表的b字段缺少了索引。

        a、首先对test表的b字段创建索引，session1中不开启事务提交一条b=1的记录，开启事务写入一条b=2的记录，此时这个事务不提交。

        b、session2中删除b=1的记录没有锁信息，已经能够规避上文中的异常，符合预期。

        c、session2中删除b=2的记录有锁信息，这种情况能够理解。

三、总结

        匹配字段有索引的情况下，可以成功删除其他记录。当匹配字段没有索引的情况下，其他非锁的记录删不掉导致存在表锁的情况，会影响业务使用。

        事务的删除和更新操作一定要走索引才能避免表锁的产生。因为mysql客户端是悲观事务，一定会在写操作时通过加锁检查是否有冲突的，删除和更新的时候没有索引就会表扫查询（和表锁一个效果），表扫删除的时候有查到insert锁，会等待这个insert锁释放才能继续执行下去。