mysql 锁

最新推荐文章于 2024-03-07 23:17:22 发布

Meiko丶

最新推荐文章于 2024-03-07 23:17:22 发布

阅读量238

点赞数

分类专栏： Java

原文链接：https://www.jianshu.com/p/904f52bde904

版权

Java 专栏收录该内容

149 篇文章 5 订阅

订阅专栏

分批次对Mysql的锁和大家一起分享

起分享

前言

数据库的锁机制是并发控制的重要内容，是对程序控制数据一致性的补充，更细粒度的保障数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。下面主要针对我们常见的InnoDB和Myisam进行解析。

注：下文提到的分库分表、fail-fast理念如果有需要，可以给大家分享下，在我厂内部应用场景。

🌺🌺🌺听着《嘴巴嘟嘟》，写着文章，有种初念的感觉。

花絮

小明是一家小作坊的屌丝程序员，工作3年，无房无车，有个女朋友叫"清风"，一天一天又一天，过着无欲无求的屌丝生活，突然下雪的那天，听说大厂某宝在招人：钱多事少妹纸穿的少、年终6月起步、有股票、上班不打卡、食堂超好、大神超多、可以直接对话18罗汉、老肖，甚至还可以撩马爸爸！于是乎，小明血脉膨胀，气血翻涌，热泪盈眶，竟不能自已！闭关，苦练杀敌本领，2个月后，成功进入阿里，成为屌丝中的王者！于是乎，翻出祖传宝典《程序员活下去的200个本事》之MYSQL篇。

有想来阿里的，可以联系我，内推你哦~

乐观锁&悲观锁

乐观并发控制和悲观并发控制是并发控制采用的主要方法。乐观锁和悲观锁不仅在关系数据库里应用，在Hibernate、Memcache等等也有相关概念。

1. 悲观锁

现在互联网高并发的架构中，受到fail-fast思路的影响，悲观锁已经非常少见了。

悲观锁(Pessimistic Locking)，悲观锁是指在数据处理过程，使数据处于锁定状态，一般使用数据库的锁机制实现。

1.1 数据表中的实现

在MySQL中使用悲观锁，必须关闭MySQL的自动提交，set autocommit=0，MySQL默认使用自动提交autocommit模式，也即你执行一个更新操作，MySQL会自动将结果提交。

set autocommit=0

举个🌰栗子：
假设商品表中有一个字段quantity表示当前该商品的库存量。假设有一件Dulex套套，其id为100，quantity=8个；如果不使用锁，那么操作方法

如下：

//step1: 查出商品剩余量
 select quantity from items where id=100;
//step2: 如果剩余量大于0，则根据商品信息生成订单
 insert into orders(id,item_id) values(null,100);
 //step3: 修改商品的库存
 update Items set quantity=quantity-1 where id=100;

这样子的写法，在小作坊真的很正常，No Problems，但是在高并发环境下可能出现问题。

如下:

其实在①或者②环节，已经有人下单并且减完库存了，这个时候仍然去执行step3，就造成了超卖。

但是使用悲观锁，就可以解决这个问题，在上面的场景中，商品信息从查询出来到修改，中间有一个生成订单的过程，使用悲观锁的原理就是，当我们在查询出items信息后就把当前的数据锁定，直到我们修改完毕后再解锁。那么在这个过程中，因为数据被锁定了，就不会出现有第三者来对其进行修改了。而这样做的前提是需要将要执行的SQL语句放在同一个事物中，否则达不到锁定数据行的目的。

如下：

//step1: 查出商品状态
select quantity from items where id=100 for update;
//step2: 根据商品信息生成订单
insert into orders(id,item_id) values(null,100);
//step3: 修改商品的库存
update Items set quantity=quantity-2 where id=100;

select...for update是MySQL提供的实现悲观锁的方式。此时在items表中，id为100的那条数据就被我们锁定了，其它的要执行select quantity from items where id=100 for update的事务必须等本次事务提交之后才能执行。这样我们可以保证当前的数据不会被其它事务修改。

1.2 扩展思考

需要注意的是，当我执行select quantity from items where id=100 for update后。如果我是在第二个事务中执行select quantity from items where id=100（不带for update）仍能正常查询出数据，不会受第一个事务的影响。另外，MySQL还有个问题是select...for update语句执行中所有扫描过的行都会被锁上，因此在MySQL中用悲观锁务必须确定走了索引，而不是全表扫描，否则将会将整个数据表锁住。

悲观锁并不是适用于任何场景，它也存在一些不足，因为悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。如果加锁的时间过长，其他用户长时间无法访问，影响了程序的并发访问性，同时这样对数据库性能开销影响也很大，特别是对长事务而言，这样的开销往往无法承受，这时就需要乐观锁。

在此和大家分享一下，在Oracle中，也存在select ... for update，和mysql一样，但是Oracle还存在了select ... for update nowait，即发现被锁后不等待，立刻报错。

2. 乐观锁

乐观锁相对悲观锁而言，它认为数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回错误信息，让用户决定如何去做。接下来我们看一下乐观锁在数据表和缓存中的实现。

2.1 数据表中的实现

利用数据版本号（version）机制是乐观锁最常用的一种实现方式。一般通过为数据库表增加一个数字类型的 “version” 字段，当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值+1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据，返回更新失败。

放个被用烂了的图

举个栗子🌰：

//step1: 查询出商品信息
select (quantity,version) from items where id=100;
//step2: 根据商品信息生成订单
insert into orders(id,item_id) values(null,100);
//step3: 修改商品的库存
update items set quantity=quantity-1,version=version+1 where id=100 and version=#{version};

既然可以用version，那还可以使用时间戳字段，该方法同样是在表中增加一个时间戳字段，和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

需要注意的是，如果你的数据表是读写分离的表，当master表中写入的数据没有及时同步到slave表中时会造成更新一直失败的问题。此时，需要强制读取master表中的数据（将select语句放在事物中）。

即：把select语句放在事务中，查询的就是master主库了！

2.2 乐观锁的锁粒度

乐观锁在我鸟系统中广泛用于状态同步，我们经常会遇到并发对一条物流订单修改状态的场景，所以此时乐观锁就发挥了巨大作用。

分享一个精心挑选乐观锁，以此缩小锁范围的case

商品库存扣减时，尤其是在秒杀、聚划算这种高并发的场景下，若采用version号作为乐观锁，则每次只有一个事务能更新成功，业务感知上就是大量操作失败。

// 仍挑选以库存数作为乐观锁
//step1: 查询出商品信息
select (inventory) from items where id=100;
//step2: 根据商品信息生成订单
insert into orders(id,item_id) values(null,100);
//step3: 修改商品的库存
update items set inventory=inventory-1 where id=100 and inventory-1>0;

没错！你参加过的天猫、淘宝秒杀、聚划算，跑的就是这条SQL，通过挑选乐观锁，可以减小锁力度，从而提升吞吐～

乐观锁需要灵活运用

现在互联网高并发的架构中，受到fail-fast思路的影响，悲观锁已经非常少见了。

2.3 扩展训练

在阿里很多系统中都能看到常用的features、params等字段，这些字段如果不进行版本控制，在并发场景下非常容易出现信息覆盖的问题。

比如：

线程	原始features	目标features
T-A	a=1;	a=1;b=1;
T-B	a=1;	a=1;c=1;

我们期望最终更新的结果为:

a=1;b=1;c=1;

此时若SQL写成了

update    
    lg_order
set    
    features=#features#
where    
    order_id=#order_id#

那么随着T-A和T-B的先后顺序不同，我们得到的结果有可能会是a=1;b=1;或a=1;c=1;

若此时采用乐观锁，利用全局字段version进行处理，则会发现与lg_order的其他字段变更有非常高的冲突率，因为version字段是全局的

update    
    lg_order
set    
    features=#features#,    
    version=version+1
where    
    order_id=#order_id#    
    and version=#ori_version#

这种SQL会因为version的失败而导致非常高的失败率，当然咯，我其他字段也在并发变更呀～

怎么办？

聪明的你会发现一般设计库表的时，凡事拥有features类似字段的，都会有一个features_cc与之成对出现，很多厂内年轻一辈的程序员很少注意到这个字段，我们努力纠正过很久，现在应该好很多了。

features_cc的作用就是features的乐观锁版本的控制，这样就规避了使用version与整个字段冲突的尴尬。

update    
    lg_order
set    
    features=#features#,    
    features_cc= features_cc +1
where    
    order_id=#order_id#    
    and features_cc =#ori_ features_cc#

这里需要注意的是，需要应用owner仔细review自己相关表的SQL，要求所有涉及到这个表features字段的变更都必须加上features_cc= features_cc +1进行计算，否则会引起并发冲突，平时要做好保护措施，不然很中意中标。

在实际的环境中，这种高并发的场景中尤其多，大家思考一下是否自觉的加上了对features字段的乐观锁保护。

不过需要提出的是，做这种字段的精耕细作控制，是以提高维护成本作为代价的。

features、attribute这两个字段我们花费了很长时间才BU同学达成共识和review代码，要求用_cc来做版本控制。

Mysql的事务

伟人说：“想了解一件事情，你就得了解它的前因后果”，
想了解透彻mysql的锁，不得不说mysql的事务机制。

于是我问小明：你对mysql的事务了解吗？
小明轻咳一下，娓娓道来：

1.1 事务概述

数据库事务是数据库执行过程中的一个逻辑单位，一个事务通常包含了对数据库的读/写操作。它的存在包含有以下两个目的：

为数据库操作序列提供了一个回滚的方法，同时提供了数据库即使在异常状态下仍能保持一致性的方法。
当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法(版面问题下次讨论)，以防止彼此的操作互相干扰。

1.2 事务的特性：ACID

原子性 aotmic

事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

一致性 consistent

事务在完成时，必须使所有的数据都保持一致状态。

隔离性 isolaton

由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据

持久性 duration

事务完成后，它对系统的影响是永久性的

2. 排他锁&共享锁

我心想，这个谁都懂，继续追问：那你能在讲讲锁吗？
好的，那我讲一下Mysql的几个锁。小明窃喜，我已经看了imooc(公众号微信)大神的锁了，我怕你？

2.1 共享锁(shared locks,S锁)

小明说：在你没有女朋友的时候，你想和女人滚床单时候，只能去找红灯区，别人也可以找红灯区，这就是共享！

共享锁又叫读锁，如果事务T1对行R加上S锁，则

其它事务T2/T3/Tn只能对行R再加S锁，不能加其它锁
获得S锁的事务只能读数据，不能写数据（你傻呀，当然也不能删咯）。

select … lock in share mode;

2.2 排他锁(exclusive locks,X锁)

小明说：你有钱找了个女票，你就要独占这个女票，别人不能使用，看（读）都不行！这就是排他！我们重点说一下排它锁。

排它锁又叫写锁，如果事务T1对行R加上X锁，则

其它事务T2/T3/Tn都不能对行R加任何类型的锁，直到T1事务在行R上的X锁释放。
获得X锁的事务既能读数据，又能写数据（也可以删除数据）。

select ... for update

举例表USER：

id	name	desc
1	马云	首富
2	小明	首负

T1（事务1）：

// start T1
SELECT * FROM USER WHERE id = 1 lock in share mode; (S锁)
......

// start T2
UPDATE USER SET name = '小明' WHERE id = 1;
......

如果T1不进行提交，则S锁不会释放，
那么T2就拿着X锁眼巴巴的看着，一直等待T1（事务1）释放S锁。

此时

// 接上文代码块
// start T3
// 此时，如果 T3 做同样查询，可以直接获取S锁进行查询
SELECT * FROM USER WHERE id = 1 lock in share mode; (S锁)

这个时候如果T1（事务1）要进行 DELETE 操作

// start T1
SELECT * FROM USER WHERE id = 1 lock in share mode; (S锁)
......
DELETE FROM USER WHERE id = 1；
......

此时:
T1发现X锁被T2占据着，所以T1拿不到X锁一直等待T2释放X锁，而T2拿着X锁等待T1释放S锁，这样互相等待就产生了死锁，deadLock。
发生死锁以后，InnoDB 会产生错误信息，并且释放锁（后面会专门讲业务中遇到的死锁和解决方案）。

上述X锁的代码可以用下图来显示：

用时间流程来显示3个线程的交互如下：

你可以把排它锁对行的保护，看作你对你女的保护，只能你碰，别人不能动！前提你得有女，不认真学习，你就是个屌丝...单身狗

听到这里我觉得小明同学很有意思哦，这个栗子🌰举得很有力气很有代入感！不愧是有女朋友的人啊！

3. 意向锁

我对小明已经开始有点兴趣了，继续追问，那你能否再说一下意向锁？
好！

3.1 意向锁(Intention Locks)

小明说: 他在找苍老师或者女票时候，先要远程看看对方有没有被人家共享或者独占，而不是到面前有没有人锁住她，可以节省成本！

意向锁是表锁，多用在innoDB中，是数据库自身的行为，不需要人工干预，在事务结束后会自行解除。

意向锁分为意向共享锁(IS锁)和意向排它锁(IX锁)

锁：表示事务中将要对某些行加S锁
IX锁：表示事务中将要对某些行加X锁

意向锁的主要作用是提升存储引擎性能，innoDB中的S锁和X锁是行锁，每当事务到来时，存储引擎需要遍历所有行的锁持有情况，性能较低，因此引入意向锁，检查行锁前先检查意向锁是否存在，如果存在则阻塞线程。

3.2 意向锁的使用

顺上面的思路讲下去，我们看下使用的逻辑

举个栗子🌰：

T1:
SELECT * FROM A WHERE id = 1 lock in share mode；（加S锁）

T2:
SELECT * FROM A WHERE id > 0 for update; (加X锁)

看上面这2个SQL事务，T1执行时候，对id=1这行加上了S锁，T2执行前，需要获取全表的更新锁进行判断，即：
step1：判断表A是否有表级锁
step2：判断表A每一行是否有行级锁
当数据量较大时候（我们一张表一般500-5000万数据），step2这种判断极其低效。

亚麻跌！亚麻跌！亚麻跌！于是乎，我们就need意向锁协议。

意向锁协议

事务要获取表A某些行的S锁必须要获取表A的IS锁
事务要获取表A某些行的X锁必须要获取表A的IX锁

Now！do you get me sense ?
这个时候step2就改变成了对意向锁的判断
step2：发现表A有IS锁，说明表肯定有行级的S锁，因此，T2申请X锁阻塞等待，不需要判断全表，判断效率极大提高（是不是省了很多钱）

4. 间隙锁

小明先飚了一段英语，想来压制我？
但是我并不care，who care？9012年了，谁不会英语？so easy！我可是从小用步步高点读机长大的人哦。
我say：pardon ? (上小学我就会，小样，你再飚啊！)

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

当我们用范围条件条件检索数据（非聚簇索引、非唯一索引），并请求共享或排他锁时，InnoDB会给符合条件的数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，称为间隙，InnoDB也会为这些间隙加锁，即间隙锁。
Next-Key锁是符合条件的行锁加上间隙锁

4.1 间隙锁产生的条件

在InnoDB下，间隙锁的产生需要满足三个条件：

隔离级别为RR
当前读
查询条件能够走到索引

4.2 间隙锁的作用

MySQL官方文档：间隙锁的目的是为了让其他事务无法在间隙中新增数据。

在RR模式的InnoDB中，间隙锁能起到两个作用：

1. 保障数据的恢复和复制

2. 防止幻读

防止在间隙中执行insert语句
防止将已有数据update到间隙中

数据库数据的恢复和复制是通过binlog实现的，binlog中记录了执行成功的DML语句（在阿里得到了极广泛的应用），在数据恢复时需要保证数据之间的事务顺序，间隙锁可以避免在一批数据中插入其他事务。

作者：你好Max
链接：https://www.jianshu.com/p/904f52bde904
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

Meiko丶

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 锁

分批次对Mysql的锁和大家一起分享起分享前言数据库的锁机制是并发控制的重要内容，是对程序控制数据一致性的补充，更细粒度的保障数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。下面主要针对我们常见的InnoDB和Myisam进行解析。注：下文提到的分库分表、fail-fast理念如果有需要，可以给大家分享下，在我厂内部应用场景。????????????听着《嘴巴嘟嘟》，写着文章，有种初念的感觉。花絮小明是一家小作坊的屌丝程序员，工作3年，无房无
复制链接

扫一扫