mysql死锁问题分析，锁行与锁表的原因

准备工作，建一张表

CREATE TABLE `testtable` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(500) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '名字',
  `age` int DEFAULT NULL COMMENT '年龄',
  `id_card` int NOT NULL DEFAULT '0' COMMENT '身份证',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='测试数据表';

初始阶段只有id是主键索引，初始数据如下所示

 

1. 死锁是怎样产生的？

以update为例，模拟死锁的产生，首先开启一个事务A，更新id = 323这条数据，但不commit!

1. A操作开启事务：start TRANSACTION; 然后更新id = 323这条数据： update testtable set name = 'xxx' WHERE id =323;
   
2. B操作也开启一个事务，更新id = 328这条数据，不提交事务
   
3. A操作这时也要更新id = 328的数据：update testtable set name = 'xxx' WHERE id =328; ，注意此时的A、B操作的事务还都未提交。结果自然是无法立即执行成功，需要等到事务B释放id = 328的锁，所以右下角的查询时间一直在增加！
   
   但当查询时间大于50秒时（mysql默认值）就会出现Lock wait timeout exceeded
   
4. 假设查询时间小于50秒（主要为了模拟死锁），这个时候事务B开始更新 id = 323这条数据：update testtable set name = 'yyy' WHERE id =323; 执行之后就出现死锁！
   

       原因分析：在事务B开始更新 id = 323这条数据时， id = 323这条数据已经被事务A所占有（A未提交），事务B此时又占用着事务A想要更新的id = 328，两个事务互相占用着对方想要的资源，却都不commit，自然就死锁了！

       需要注意的是：两个事务即使发生了死锁，也只有抛出死锁异常的那个事务B才会更新失败，事务A是会正常更新的！！！ 因为事务A原本在等待事务B释放id = 328 的锁，此时由于事务B先检测到了死锁，自己主动放弃竞争（抛出死锁异常），那么事务A也就拿到了id = 328 的锁，可以正常提交事务！！

 

2. 死锁解决方案

 

3. 锁行与锁表的现象和原因

 

①：行锁

如果在一个事务中，操作的sql使用到了索引，使用的就是行锁。以updateById为例，（id是主键索引）

• A开启事务，更新id = 326 这条数据，不提交事务
  
• B也开启事务，更新id = 323 这条数据，不提交事务
  
• 这种操作用到了索引，A,B两个事务提交后，都可以正常执行。因为他们都走了索引，用到的是行锁（其他字段同理）
  

 

②：表锁

在一个事务中，如果操作的sql未使用到索引，使用的是表锁。会阻塞其他事务的增、删、改操作

前提：表中的age无索引

• A开启事务，更新age = 327 这条数据，但不提交事务
  
• B开启事务，更新age =328 这条数据，由于A已经锁表，B操作需要等到A事务释放锁才可以执行！
  

思考一：已知B事务的更新操作会进行锁等待，那么插入操作呢？可以看到增删改都会受影响，但查询不受影响，因为可重复读隔离级别下的MVCC机制

思考二：如果B事务的更新操作走到了索引呢（updateById）？还是一样，同样需要等待事务A提交