mysql 悲观锁，乐观锁

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，但是在高并发环境下可能出现问题。

如下:

 

image.png

其实在①或者②环节，已经有人下单并且减完库存了，这个时候仍然去执行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值相等，则予以更新，否则认为是过期数据，返回更新失败。

放个被用烂了的图

image.png

举个栗子🌰：

 

//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来做版本控制。

1. 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锁的代码可以用下图来显示：

image.png

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

image.png

 

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

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

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 ? (上小学我就会，小样，你再飚啊！)

4.0 官方原文：

 

image.png

非常重要，必须读懂，读不懂，就去幼儿园回炉吧，亲

 

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到间隙中

 

深入理解SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE

概念和区别

SELECT ... LOCK IN SHARE MODE走的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。

SELECT ... FOR UPDATE 走的是IX锁(意向排它锁)，即在符合条件的rows上都加了排它锁，其他session也就无法在这些记录上添加任何的S锁或X锁。如果不存在一致性非锁定读的话，那么其他session是无法读取和修改这些记录的，但是innodb有非锁定读(快照读并不需要加锁)，for update之后并不会阻塞其他session的快照读取操作，除了select ...lock in share mode和select ... for update这种显示加锁的查询操作。

通过对比，发现for update的加锁方式无非是比lock in share mode的方式多阻塞了select...lock in share mode的查询方式，并不会阻塞快照读。

应用场景

在我看来，SELECT ... LOCK IN SHARE MODE的应用场景适合于两张表存在关系时的写操作，拿mysql官方文档的例子来说，一个表是child表，一个是parent表，假设child表的某一列child_id映射到parent表的c_child_id列，那么从业务角度讲，此时我直接insert一条child_id=100记录到child表是存在风险的，因为刚insert的时候可能在parent表里删除了这条c_child_id=100的记录，那么业务数据就存在不一致的风险。正确的方法是再插入时执行select * from parent where c_child_id=100 lock in share mode,锁定了parent表的这条记录，然后执行insert into child(child_id) values (100)就ok了。


但是如果是同一张表的应用场景，举个例子，电商系统中计算一种商品的剩余数量，在产生订单之前需要确认商品数量>=1,产生订单之后应该将商品数量减1。
1 select amount from product where product_name='XX';
2 update product set amount=amount-1 where product_name='XX';


显然1的做法是是有问题，因为如果1查询出amount为1，但是这时正好其他session也买了该商品并产生了订单，那么amount就变成了0，那么这时第二步再执行就有问题。
那么采用lock in share mode可行吗，也是不合理的，因为两个session同时锁定该行记录时，这时两个session再update时必然会产生死锁导致事务回滚。以下是操作范例(按时间顺序)


session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test_jjj lock in share mode;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj        |
+-----+------------+
2 rows in set (0.00 sec)


session2(同样锁定了相同的行)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test_jjj lock in share mode;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj        |
+-----+------------+
2 rows in set (0.00 sec)


session1(这时session1再update时就会引起锁等待)
mysql> update test_jjj set name='jjj1' where name='jjj';


session2(这时session2同样update就会检测到死锁，回滚session2，注意执行时间不要超过session1的锁等待超时检测时间，即不要超过innodb_lock_wait_timeout设置的值)
mysql> update test_jjj set name='jjj1' where name='jjj';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction


session1(此时session1执行完成)
mysql> update test_jjj set name='jjj1' where name='jjj';
Query OK, 1 row affected (29.20 sec)
Rows matched: 1  Changed: 1  Warnings: 0


通过该案例可知lock in share mode的方式在这个场景中不适用，我们需要使用for  update的方式直接加X锁，从而短暂地阻塞session2的select...for update操作;以下是操作范例


session1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test_jjj for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj1       |
+-----+------------+
2 rows in set (0.00 sec)


session2(此时session2处于锁等待状态，得不到结果)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test_jjj for update;


session1(这时session1 update之后提交，可完成)
mysql> update test_jjj set name='jjj1' where name='jjj';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0


mysql> commit;
Query OK, 0 rows affected (0.00 sec)


session2(session1提交之后session2刚才的查询结果就出来了，也就可以再次update往下执行了)
mysql> select * from test_jjj for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj1       |
+-----+------------+
2 rows in set (37.19 sec)
mysql> select * from test_jjj for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj1       |
+-----+------------+
2 rows in set (37.19 sec)


mysql> update test_jjj set name='jjj1' where name='jjj';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0


mysql> commit;

Query OK, 0 rows affected (0.00 sec)

通过对比，lock in share mode适用于两张表存在业务关系时的一致性要求，for  update适用于操作同一张表时的一致性要求。

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

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