浅析MySql事务中死锁和行级锁

最新推荐文章于 2024-08-01 13:40:44 发布

charthyf

最新推荐文章于 2024-08-01 13:40:44 发布

阅读量4.6k

点赞数 3

文章标签： mysql 死锁行级锁

本文链接：https://blog.csdn.net/charthyf/article/details/82703105

版权

数据库的事务功能是所有开发人员都会遇到和使用的，当初开发windows基于ado接口，unix下基于OCI接口，都需要直接写sql和自己控制事务，随着mybatis等更加功能丰富的封装接口出现，sql基本上会被封装屏蔽，事务功能也被很好的隐藏。虽然降低了开发难度，提升开发效率，但是降低了我们开发人员对数据库一些深度了解的机会。这里简单通过测试的方法介绍一下死锁以及在处理消费类扣款问题中使用行级锁进行互斥。
数据库的事务介绍很多，这里简单介绍一下：一个事务本质上有四个特点ACID：
1.Atomicity原子性（这个好理解，在commit时，要么全做，要么不做）
2.Consistency一致性（这个理解可以复杂一点，稍后详细探讨）
3.Isolation隔离性（这个简单，事务之间彼此互不干扰，隔离是通过用悲观或乐观锁机制实现的）
4.Durability耐久性（成功的事务必须在数据库中能够保存，只要提交返回成功了，不管数据还在缓存中或是已经到存储上，就算数据库宕机也可以完整恢复，这是对数据库底层实现的要求）

下面来谈谈一致性，原子性、隔离性和耐久性都有着明确的定义和实现方式，而一致性感觉更像是一种约束和规范，即无论你如何并行或者分布式去处理数据，事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。可以从以下几点感受一下：1、淘宝双11抢购，1000个人同时抢100个同个商品，这是个典型的事务问题，存在抢购和支付两个步骤。设计上的约束是必须所有商品都能被抢购掉，所有抢购到的商品都能被支付，所有支付掉的商品都可以获得。由于多步操作，中途会有很多可能发生，有的人抢到商品但支付时候想想又放弃了，如有的人抢到商品并购买了，但付时候余额不够。如果做的不好其中会产生很多不一致，如交了钱拿不到货，明明有货却不能抢等。这个是在实现方法上的一致性。2、目前数据库多采用分部署多节点存储和访问，一个人的银行账号余额会同时存在多个节点上，当发生扣款时需要同步所有节点，同步需要时间，可能有2个节点已更新，还有3个节点未更新，此时如果又有多个查询存款的请求，向哪个节点访问未知，如何保证所查询结果一致性。

以上对ACID的理解需要大家更大的感悟，如果是开发，在底层技术实现上可以不需要过于深入，但对于业务实现上必须深刻理解，尤其在分布式、高性能、高并发的环境下。否则会出现很多莫名其妙的问题。

下面我们直接在mysql里看个死锁，这里我们直接用navicat进行测试。
1、我们建三张表，分别是用户余额表（t_count）、货物表（t_goods）和订单表（t_order），具体如下：

CREATE TABLE `t_count` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL,
  `balance` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

CREATE TABLE `t_goods` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `goods` varchar(255) NOT NULL,
  `total_count` int(11) NOT NULL,
  `current_count` int(11) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


CREATE TABLE `t_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `count_id` int(11) NOT NULL,
  `goods_id` int(10) NOT NULL,
  `fee` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

2、我们建两个存储过程p1、p2实现死锁
右键列表中函数，点击新建函数。
右键函数点击新建函数
建立两个存储过程。
p1实现先扣钱再扣货物，为演示死锁，中间sleep5秒

BEGIN
    update t_count set balance = balance - 2;
    Select SLEEP(5);
    update t_goods set current_count = current_count - 1; 
END

p2实现先扣货物，再扣钱

BEGIN
  update t_goods set current_count = current_count - 1 where id = 1; 
  update t_count set balance = balance - 2 where id = 1;    
END

很明显在业务上p1和p2两个请求是互锁的。先执行p1然后迅速执行p2,发现没有死锁，这是怎么回事？默认情况下mysql为自动提交事务，即每执行一行就进行一次提交，可以认为没有事务，有两中方法解决，1、关闭自动提交事务SET AUTOCOMMIT=0;2、将事务包在START TRANSACTION和COMMIT中。我们对p1、p2修改一下，添加事务。

#p1
BEGIN
    START TRANSACTION;
    update t_count set balance = balance - 2;
    Select SLEEP(5);
    update t_goods set current_count = current_count - 1; 
    COMMIT;
END

#p2
BEGIN
  START TRANSACTION;
  update t_goods set current_count = current_count - 1 where id = 1; 
  update t_count set balance = balance - 2 where id = 1;
  COMMIT;   
END

再执行p1然后迅速执行p2,发下p2顺利完成，而p1提示检测到死锁，进行回滚。
p2执行结果

时间: 3.646ms
Procedure executed successfully
受影响的行: 0

p1执行结果

Procedure execution failed
1213 - Deadlock found when trying to get lock; try restarting transaction

添加事务后，p1进程执行->先锁t_count表->然后sleep->此时p2执行->先锁t_goods->然后锁t_count，系统发现t_count已被锁，p2等待->p1sleep结束->p1锁t_goods，发现t_goods已被锁，并且探测到p1等待将发生死锁，此时mysql强制将p1事务回滚结束以防止死锁,p1释放t_count并结束->p2锁t_count->p2结束。
从测试中看出，数据库在处理死锁方面提供了一定的检测能力，并在尽量保证事务完成情况下释放可能产生死锁的事务。

接下来我们测试一下行级锁，完成并发环境下购物和扣款。
行级锁实现比较简单，只要在select后面添加for update就可加锁。
我们建立p3，p4两个存储过程测试

#p3
BEGIN
START TRANSACTION;
select count,fee,goods_id into @nc,@nf,@ngid from t_order where id = 1 ;
select current_count into @ncc  from t_goods where id = @ngid FOR UPDATE;
select SLEEP(10);
update t_goods set current_count = @ncc - @nc where id = @ngid;
COMMIT;
END

#p4
BEGIN
START TRANSACTION;
select count,fee,goods_id into @nc,@nf,@ngid from t_order where id = 2 ;
select current_count into @ncc  from t_goods where id = @ngid FOR UPDATE;
update t_goods set current_count = @ncc - @nc where id = @ngid;
COMMIT;
END