SQL 鎖

1. 定義

慕課解釋：一把鎖對(duì)應(yīng)一扇門(mén)，獲得鎖的可以進(jìn)門(mén)，否則只能在門(mén)外等待。

2. 前言

本小節(jié)，我們將一起學(xué)習(xí) SQL 中的鎖。

在一些并發(fā)場(chǎng)景中，會(huì)涉及到一些數(shù)據(jù)競(jìng)爭(zhēng)問(wèn)題。如 A、B 二人同時(shí)要修改同一條記錄，如果二人可以對(duì)其同時(shí)修改，那么很大的概率上，數(shù)據(jù)會(huì)起沖突，為了保證數(shù)據(jù)的安全性和正確性，SQL 引入了鎖。

本小節(jié)測(cè)試數(shù)據(jù)如下，請(qǐng)先在數(shù)據(jù)庫(kù)中執(zhí)行：

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. 鎖的分類

鎖的種類非常多，專業(yè)名詞數(shù)不勝數(shù)，我們無(wú)需將其所有都記住，在本小節(jié)我們只了解其常用且提及最廣的部分。

從鎖的粒度上，我們可以將其大致的分為如下幾類：

從數(shù)據(jù)庫(kù)系統(tǒng)管理角度來(lái)看，可以把鎖分為如下兩大類：

鎖的種類還有很多，實(shí)現(xiàn)方式也多姿多彩，如果你感興趣，可以查閱一下相關(guān)的資料。

我們分別從粒度和管理兩個(gè)角度上對(duì)鎖進(jìn)行了分類。

在粒度上，不同數(shù)據(jù)庫(kù)，甚至不同引擎對(duì)鎖的粒度支持都是不同的，如 MySQL 的 InnoDB 引擎支持行鎖、表鎖和庫(kù)鎖，而 MyISAM 引擎只能支持到表鎖。對(duì)于頁(yè)鎖，只有 SQL Server 支持，而不同數(shù)據(jù)庫(kù)也有類似間隙鎖的實(shí)現(xiàn)，它的功能與頁(yè)鎖差不多。

在管理上，鎖根據(jù)數(shù)據(jù)是否共享來(lái)分類，對(duì)于讀多寫(xiě)少的場(chǎng)景，共享鎖幾乎是并發(fā)的標(biāo)配，而一旦涉及數(shù)據(jù)修改，鎖就必須獨(dú)占了。

4. 實(shí)踐

下面，我們以幾個(gè)例子來(lái)熟悉一下鎖的使用。

4.1 例1、鎖住 imooc_user 表

在 SQL 中，你可以通過(guò)如下語(yǔ)句鎖住某一張表：

LOCK TABLE [table_name] [READ|WRITE];

其中table_name表示數(shù)據(jù)表名稱，[READ|WRITE]表示可以任選READ（讀鎖）或WRITE（寫(xiě)鎖）中的一種。

當(dāng)需要解鎖時(shí)，只需如下語(yǔ)句：

UNLOCK TABLE;

請(qǐng)書(shū)寫(xiě) SQL 語(yǔ)句，鎖住imooc_user表，但其他人可讀。

分析：

題干中指出，他人可讀，因此鎖為讀鎖，通過(guò) LOCK TABLE 鎖住該表即可。

語(yǔ)句：

整理可得語(yǔ)句如下：

LOCK TABLE imooc_user READ;

鎖住后，其他人仍然能夠讀取 imooc_user 表的數(shù)據(jù)，如下：

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

測(cè)試完畢后，我們一定記得解鎖：

UNLOCK TABLE;

4.2 例2、鎖住 pedro 用戶

對(duì)于某一條記錄（某一行），SQL 提交如下方式來(lái)加讀鎖：

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

其中table_name表示數(shù)據(jù)表名稱，condition表示過(guò)濾條件。

如果你要獨(dú)占這一行的數(shù)據(jù)，可以這樣加上寫(xiě)鎖：

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

注意： 在測(cè)試時(shí)，你必須在一個(gè)事務(wù)里面進(jìn)行行鎖，否則查詢直接退回，鎖的時(shí)間極短。

請(qǐng)書(shū)寫(xiě) SQL 語(yǔ)句，鎖住 imooc_user 表中用戶pedro，只允許別人讀，不允許別人寫(xiě)。

分析：

pedro 用戶是表中的一條記錄，因此通過(guò) SELECT … LOCK … 的方式加上行讀鎖，為了方便測(cè)試我們以一個(gè)事務(wù)的方式來(lái)操作鎖。

語(yǔ)句：

整理可得語(yǔ)句如下：

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

鎖住該行后，其他用戶可以讀取它卻不能修改它，直到釋放鎖才能修改，如下：

COMMIT;

有時(shí)候，我們也需要更加霸道地鎖住 pedro，即不讓人寫(xiě)，也不讓人讀，這個(gè)時(shí)候就可以使用寫(xiě)鎖。

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

操作完畢后，我們一定記得提交事務(wù)以釋放鎖。

COMMIT;

5. 個(gè)人經(jīng)驗(yàn)

• 鎖與事務(wù)都是面試必備，且二者往往都是彼此關(guān)聯(lián)。
• 鎖的內(nèi)容浩瀚如海，本小節(jié)以粒度和管理兩個(gè)視角，簡(jiǎn)單地介紹了鎖，在實(shí)戰(zhàn)部分，我們還會(huì)接著討論它。