MySQL的排它锁与共享锁

博主最近在复习 MySQL 和 Redis 的知识。顺便就记录一下这个 MySQL 排它锁和共享锁。

本文以下内容基于数据表(test 表)：

+----+-------+
| id | name  |
+----+-------+
| 1  | 111   |
+----+-------+
| 2  | 222   |
+----+-------+

共享锁

共享锁也叫读锁，就是在读取数据的时候加上共享锁，如果不提交事务就会一直锁行阻塞。

执行 SQL ：

BEGIN;
SELECT `id`,`name` FROM test WHERE id = 1 LOCK IN SHARE MODE;

正常返回了数据记录：

此时打开另外一个数据库管理软件（新进程）访问同一个数据库继续执行排它锁 SQL：

BEGIN;
SELECT `id`,`name` FROM test WHERE id = 1 FOR UPDATE;

因为刚才执行的 SQL 加了共享锁，所以此时新进程使用排它锁查询同一条数据会因为阻塞而超时。

而如果使用正常的查询或者共享锁继续查询：

SELECT `id`,`name` FROM test WHERE id = 1;
# 或者：
SELECT `id`,`name` FROM test WHERE id = 1 LOCK IN SHARE MODE;

是不会被阻塞的，可以正常返回数据。

排它锁

排它锁是独占锁，执行之后使用共享锁查询被排它锁锁住的数据记录也无法查询。

例如 A 进程中查询：

BEGIN;
SELECT `id`,`name` FROM test WHERE id = 1 FOR UPDATE;

在 B 进程中执行:

BEGIN;
SELECT `id`,`name` FROM test WHERE id = 1 LOCK IN SHARE MODE;

或者执行：

BEGIN;
SELECT `id`,`name` FROM test WHERE id = 1 FOR UPDATE;

都是无法查询到结果，直到 A 进程提交事务后才会获取到结果。

自带排它锁

其实 innoDB 执行 UPDATE 是带有排它锁的，所以在事务中执行 UPDATE 但是不提交事务的时候，其他进程使用排它锁或者共享锁是无法查询的。

A 进程执行:

BEGIN;
UPDATE test SET NAME = 333 WHERE id = 1;

B 进程执行：

BEGIN;
SELECT `id`,`name` FROM test WHERE id = 1 FOR UPDATE;
COMMIT;

就会遇到阻塞而无法查询数据。

乐观锁

上面写的共享锁和排它锁其实都是属于悲观锁，而乐观锁其实更适合高并发。

假如 test 表中含有的字段和数据：

+----+-------+---------+
| id | stock | version |
+----+-------+---------+
| 1  | 100   | 1       |
| 2  | 200   | 2       |
+----+-------+---------+

查询数据：

SELECT `id`,`name` FROM test WHERE id = 1

返回的结果：

+----+-------+---------+
| id | stock | version |
+----+-------+---------+
| 1  | 100   | 1       |
+----+-------+---------+

此时需要减少该记录的库存，使用乐观锁可以这样执行：

UPDATE test SET stock = stock - 1 WHERE id = 1 AND stock > 0 AND version = 1 AND stock = 100;

因为在 where 条件中加入了查询到的结果返回的参数，并且如果库存 stock 为 0 的时候是无法执行成功的，所以一般情况下这样执行之后超卖就不会出现。

共享锁和排它锁的区别

共享锁是可以大家一起读的，就是在 A 进程使用共享锁查询了数据，B 进行也可以继续查询，但是如果 B 进程进行修改也会进行阻塞。 如果此时新的进程使用排它锁查询被共享锁的数据是会被阻塞的。

排它锁是独占锁，当查询的时候 SELECT ... FOR UPDATE 其他进程就不能使用排它锁或者共享锁进行查询该数据了（增删改查都无法进行）。

注意

使用锁一定要将数据引擎设置为 innoDB, 因为 Myisam 引擎不支持事务。

以上内容是博主对锁的个人理解，如有错误可以评论指正。