MySQL锁系列(3)：悲观锁探究

抛出一个面试问题

Mysql如何实现乐观锁与悲观锁？

相信这个或多或少都知道。这次主要看看Mysql支持的悲观锁。

下次再专门研究一下乐观锁与悲观锁的应用

悲观锁

Mysql支持行锁，也就是可以对一条数据加X锁(排它锁)，用法为

SELECT ... FOR UPDATE

在前面介绍过Mysql的一致性锁定读(传送门：Mysql锁概述)，就是通过for update实现的。这可以用在需要显示的对数据库读取操作加锁以保证数据逻辑一致性的场景下。

比如用户转账的场景，可能涉及多个步骤

• 首先查询用户账户信息：select操作
• 检测是否能进行转账：业务检查
• 进行转账，更新账户余额等信息

假定这个操作就在一个本地事物中，看起来是不会有问题的。但是如果出现并发，就有可能出现丢失更新，如下

上图为丢失更新的一种，这种情况下是很可怕的，产生了资损

再看下应用了select for update之后流程

• A事物对账户加X锁之后，B事物去执行同样的加锁会失败，将产生等待直到A事物提交
• B获取锁，查询到的余额是A更新之后的900，将不会产生丢失更新问题了

MVCC

这里有一个问题

1. 事物A对一条记录加了X锁，即用select...for update where id= X对记录加了悲观锁
2. 事物B能否读取同一条记录：即select... where id= X

试验一下，先建一张表test1，id为主键，并插入2条记录

CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test1(age) VALUES(22);
INSERT INTO test1(age) VALUES(33);

然后锁住age为22的这条记录，这个时候是能正常返回的

BEGIN;
select age from test1 where id = 1 FOR update;

再开户一个事物，也查询这条记录，会返回什么呢？

BEGIN;
select age from test1 where id = 1;

结果是也返回了，如果是for update，肯定是不会返回的，A事物不提交，B肯定会失败，锁定超时(可自行实验)

那么B事物不加锁，普通查询，为什么会返回呢？这就是mysql的MVCC机制：

• 快照数据是一行记录的历史版本，一个行记录可能不止有一条快照数据，一般称这个种技术为行多版本技术
• 通过行多版本技术带来的并发控制，称为多版本并发控制，也就是MVCC

通俗的说，上面B读取的就是age为22的历史版本。由于是新表，只有一条记录，所以只有一条快照记录。

再作一个例子，A事物修改主键id，但是不提交事物

BEGIN;
UPDATE test1 set id = 3 where id = 1;

B事物还是正常直接读取，读取出来的id还是修改之前的

BEGIN;
select * from test1 where id = 1;

所以在REPEATABLE READ隔离级别下，读取的快照版本是事物开始的版本

那么在READ COMMITED隔离级别下呢？修改隔离级别

show global variables like '%isolation%';
set global tx_isolation ='read-committed';

然后重复执行上面的步骤

BEGIN;
UPDATE test1 set id = 3 where id = 1;

BEGIN;
select * from test1 where id = 1;

发现此时事物B查询出来的不再是id=1的那条记录了，而是空的。说明在READ COMMITED级别下，对快照数据的定义不一样，读取问题最新的一分快照。这违背了数据库ACID中的I，即隔离性

一锁二判三更新

这是一句口诀：一锁二判三更新。

说的是在并发的情况下，执行下面的步骤，基本上可以避免并发问题

• 首先锁住被操作的资源
• 判断数据是否发生变化
• 执行业务操作

加锁分析

上面的for update加锁，都是通过id字段，那如果通过age字段行吗？

假定还是在REPEATABLE-READ隔离级别下，回顾下test1表中现在有2条记录

首先对age为22的记录加锁

BEGIN;
SELECT * from test1 where age = 22 for UPDATE;

然后插入一条记录age为23

BEGIN;
INSERT INTO test1(age) VALUES(23);

但是上面的sql不会成功，会等待直到超时

上面的例子说明了，age字段没有任何索引的情况下，是会通过GAP锁锁住一个范围的，而不是像主键id一样只锁住一行(主键id是可以插入age为23的)，那么此时的加锁情况是怎样的呢？

从上面可以看到，这种情况下对主键id加了3把锁，对age加了4把GAP锁，这种是很可怕的事情，如果数据越多，消耗越多(要用更多内存来记录锁的信息)

所以用select for update，建议对主键或者是唯一键使用

并且分析加锁，需要考虑很多方面

• 隔离级别
• 字段是否主键
• 不是主键，列上是否有索引
• 是否唯一索引
• 执行计划是什么？索引扫描，还是全表扫描？

后面在分析上面以可能几种情况