数据库 / 33 SQL 锁

SQL 锁

1. 定义

慕课解释:一把对应一扇门,获得锁的可以进门,否则只能在门外等待。

2. 前言

本小节,我们将一起学习 SQL 中的

在一些并发场景中,会涉及到一些数据竞争问题。如 A、B 二人同时要修改同一条记录,如果二人可以对其同时修改,那么很大的概率上,数据会起冲突,为了保证数据的安全性和正确性,SQL 引入了

本小节测试数据如下,请先在数据库中执行:

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int
);
INSERT INTO imooc_user(id,username,age)
VALUES (1,'peter',18),(2,'pedro',24),(3,'jerry',22),(4,'mike',18),(5,'tom',20);

3. 锁的分类

锁的种类非常多,专业名词数不胜数,我们无需将其所有都记住,在本小节我们只了解其常用且提及最广的部分。

从锁的粒度上,我们可以将其大致的分为如下几类:

名称 描述 说明
库锁 锁定某个数据库 粒度最大,若非特殊情况(数据库备份),切勿使用。
表锁 锁定某张数据表 粒度也比较大,直接涉及一张表,若非特殊情况,也勿使用。
页锁 锁定某张数据页 SQL Server 特有的锁,会锁定数据页,数据表中的数据是按页组织的。
行锁 锁定某一行记录 粒度最小,只锁定一条记录,推荐使用。

从数据库系统管理角度来看,可以把锁分为如下两大类:

名称 描述 说明
共享锁 其他人可以读取,但不能修改 也被称为读锁
排他锁 其他人不能读取,也不能修改 也被称为写锁

锁的种类还有很多,实现方式也多姿多彩,如果你感兴趣,可以查阅一下相关的资料。

我们分别从粒度和管理两个角度上对锁进行了分类。

在粒度上,不同数据库,甚至不同引擎对锁的粒度支持都是不同的,如 MySQL 的 InnoDB 引擎支持行锁、表锁和库锁,而 MyISAM 引擎只能支持到表锁。对于页锁,只有 SQL Server 支持,而不同数据库也有类似间隙锁的实现,它的功能与页锁差不多。

在管理上,锁根据数据是否共享来分类,对于读多写少的场景,共享锁几乎是并发的标配,而一旦涉及数据修改,锁就必须独占了。

4. 实践

下面,我们以几个例子来熟悉一下锁的使用。

4.1 例1、锁住 imooc_user 表

在 SQL 中,你可以通过如下语句锁住某一张表:

LOCK TABLE [table_name] [READ|WRITE];

其中table_name表示数据表名称,[READ|WRITE]表示可以任选READ(读锁)WRITE(写锁)中的一种。

当需要解锁时,只需如下语句:

UNLOCK TABLE;

请书写 SQL 语句,锁住imooc_user表,但其他人可读。

分析:

题干中指出,他人可读,因此锁为读锁,通过 LOCK TABLE 锁住该表即可。

语句:

整理可得语句如下:

LOCK TABLE imooc_user READ;

锁住后,其他人仍然能够读取 imooc_user 表的数据,如下:

# select * from imooc_user;
+----+----------+
| id | username |
+----+----------+
| 1  | peter    |
| 2  | pedro    |
| 3  | jerry    |
| 4  | mike     |
| 5  | tom      |
+----+----------+

测试完毕后,我们一定记得解锁:

UNLOCK TABLE;

4.2 例2、锁住 pedro 用户

对于某一条记录(某一行),SQL 提交如下方式来加读锁:

SELECT * FROM [table_name] WHERE [condition] LOCK IN SHARE MODE;

其中table_name表示数据表名称,condition表示过滤条件。

如果你要独占这一行的数据,可以这样加上写锁:

SELECT * FROM [table_name] WHERE [condition] FOR UPDATE;

注意: 在测试时,你必须在一个事务里面进行行锁,否则查询直接退回,锁的时间极短。

请书写 SQL 语句,锁住 imooc_user 表中用户pedro,只允许别人读,不允许别人写。

分析:

pedro 用户是表中的一条记录,因此通过 SELECT … LOCK … 的方式加上行读锁,为了方便测试我们以一个事务的方式来操作锁。

语句:

整理可得语句如下:

BEGIN;
SELECT * FROM imooc_user WHERE id = 1 LOCK IN SHARE MODE;

锁住该行后,其他用户可以读取它却不能修改它,直到释放锁才能修改,如下:

COMMIT;

有时候,我们也需要更加霸道地锁住 pedro,即不让人写,也不让人读,这个时候就可以使用写锁。

BEGIN;
SELECT * FROM imooc_user WHERE id = 1 FOR UPDATE;

操作完毕后,我们一定记得提交事务以释放锁。

COMMIT;

5. 个人经验

  • 锁与事务都是面试必备,且二者往往都是彼此关联。
  • 锁的内容浩瀚如海,本小节以粒度和管理两个视角,简单地介绍了锁,在实战部分,我们还会接着讨论它。