前言

前幾天，知識(shí)星球中的一個(gè)小伙伴，問(wèn)了我一個(gè)問(wèn)題：在MySQL中，事務(wù)A中使用select...for update where id=1鎖住了，某一條數(shù)據(jù)，事務(wù)還沒(méi)提交，此時(shí)，事務(wù)B中去用select ... where id=1查詢那條數(shù)據(jù)，會(huì)阻塞等待嗎？

select...for update在MySQL中，是一種悲觀鎖的用法，一般情況下，會(huì)鎖住一行數(shù)據(jù)，但如果沒(méi)有使用正確的話，也會(huì)把整張表鎖住。

其實(shí)，我之前也在實(shí)際項(xiàng)目中試過(guò)用，比如：積分兌換禮品的功能。

今天跟大家一起聊聊select...for update這個(gè)話題，希望對(duì)你會(huì)有所幫助。

1. 要什么要用行鎖？

假如現(xiàn)在有這樣一種業(yè)務(wù)場(chǎng)景：用戶A給你轉(zhuǎn)賬了2000元，用戶B給你轉(zhuǎn)賬了3000元，而你的賬戶初始化金額是1000元。

在事務(wù)1中會(huì)執(zhí)行下面這條sql：

update account set money=money+2000 where id=123;

在事務(wù)2中執(zhí)行下面這條sql：

update account set money=money+3000 where id=123;

這兩條sql執(zhí)行成功之后，你的money可能是：3000、4000、6000，這三種情況中的一種。

你之前的想法是，用戶A和用戶B總共給你轉(zhuǎn)賬5000，最終你賬戶的錢應(yīng)該是6000才對(duì)，3000和4000是怎么來(lái)的？

假如事務(wù)1在執(zhí)行update語(yǔ)句的過(guò)程中，事務(wù)2同時(shí)也在執(zhí)行update語(yǔ)句。

事務(wù)1中查詢到money是1000，此外事務(wù)2也查詢到money是1000。

如果事務(wù)1先執(zhí)行update語(yǔ)句，事務(wù)2后執(zhí)行update語(yǔ)句，第一次update的3000，會(huì)被后面的4000覆蓋掉，最終結(jié)果為4000。

如果事務(wù)2先執(zhí)行update語(yǔ)句，事務(wù)1后執(zhí)行update語(yǔ)句，第一次update的4000，會(huì)被后面的3000覆蓋掉，最終結(jié)果為3000。

這兩種情況都產(chǎn)生了嚴(yán)重的數(shù)據(jù)問(wèn)題。

我們需要有某種機(jī)制，保證事務(wù)1和事務(wù)2要順序執(zhí)行，不要一起執(zhí)行。

這就需要加鎖了。

目前MySQL中使用比較多的有：表鎖、行鎖和間隙鎖。

我們這個(gè)業(yè)務(wù)場(chǎng)景，非常時(shí)候使用行鎖。

在事務(wù)1執(zhí)行update語(yǔ)句的過(guò)程中，先要把某一行數(shù)據(jù)鎖住，此時(shí)，其他的事務(wù)必須等待事務(wù)1執(zhí)行完，提交了事務(wù)，才能獲取那一行的數(shù)據(jù)。

在MySQL中是通過(guò)select...for update語(yǔ)句來(lái)實(shí)現(xiàn)的行鎖的功能。

但如果你在實(shí)際工作中使用不正確，也容易把整張表鎖住，嚴(yán)重影響性能。

select...where...for update語(yǔ)句的用法是否正確，跟where條件中的參數(shù)有很大的關(guān)系。

我們一起看看下面幾種情況。

假如user表現(xiàn)在有這樣的數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)的版本是：8.0.21。

創(chuàng)建的索引如下：

其中id是主鍵字段，code是唯一索引字段，name是普通索引字段，其他的都是普通字段。

2. 主鍵

當(dāng)where條件用的數(shù)據(jù)庫(kù)主鍵時(shí)。

例如開(kāi)啟一個(gè)事務(wù)1，在事務(wù)中更新id=1的用戶的年齡：

begin;select * from user where id=1 for update;update user set age=22 where id=1;

where條件中的id是數(shù)據(jù)庫(kù)的主鍵，并且使用for update關(guān)鍵字，加了一個(gè)行鎖，這個(gè)事務(wù)沒(méi)有commit。

此時(shí)，開(kāi)啟了另外一個(gè)事務(wù)2，也更新id=1的用戶的年齡：

begin;update user set age=23 where id=1;commit;

在執(zhí)行事務(wù)2的sql語(yǔ)句的過(guò)程中，會(huì)一直等待事務(wù)1釋放鎖。

如果事務(wù)1一直都不釋放行鎖，事務(wù)2最后會(huì)報(bào)下面這個(gè)異常：

如果此時(shí)開(kāi)始一個(gè)事務(wù)3，更新id=2的用戶的年齡：

begin;update user set age=23 where id=2;commit;

執(zhí)行結(jié)果如下：

由于事務(wù)3中更新的另外一行數(shù)據(jù)，因此可以執(zhí)行成功。

說(shuō)明使用for update關(guān)鍵字，鎖住了主鍵id=1的那一行數(shù)據(jù)，對(duì)其他行的數(shù)據(jù)并沒(méi)有影響。

3. 唯一索引

當(dāng)where條件用的數(shù)據(jù)庫(kù)唯一索引時(shí)。

開(kāi)啟一個(gè)事務(wù)1，在事務(wù)中更新code=101的用戶的年齡：

begin;select * from user where code='101' for update;update user set age=22 where code='101';

where條件中的code是數(shù)據(jù)庫(kù)的唯一索引，并且使用for update關(guān)鍵字，加了一個(gè)行鎖，這個(gè)事務(wù)沒(méi)有commit。

此時(shí)，開(kāi)啟了另外一個(gè)事務(wù)2，也更新code=101的用戶的年齡：

begin;update user set age=23 where code='101';commit;

執(zhí)行結(jié)果跟主鍵的情況是一樣的。

4. 普通索引

當(dāng)where條件用的數(shù)據(jù)庫(kù)普通索引時(shí)。

開(kāi)啟一個(gè)事務(wù)1，在事務(wù)中更新name=周星馳的用戶的年齡：

begin;select * from user where name='周星馳' for update;update user set age=22 where name='周星馳';

where條件中的name是數(shù)據(jù)庫(kù)的普通索引，并且使用for update關(guān)鍵字，加了一個(gè)行鎖，這個(gè)事務(wù)沒(méi)有commit。

此時(shí)，開(kāi)啟了另外一個(gè)事務(wù)2，也更新name=周星馳的用戶的年齡：

begin;update user set age=23 where name='周星馳';commit;

執(zhí)行結(jié)果跟主鍵的情況也是一樣的。

5. 主鍵范圍

當(dāng)where條件用的數(shù)據(jù)庫(kù)主鍵范圍時(shí)。

開(kāi)啟一個(gè)事務(wù)1，在事務(wù)中更新id in (1,2)的用戶的年齡：

begin;select * from user where id in (1,2) for update;update user set age=22 where id in (1,2);

where條件中的id是數(shù)據(jù)庫(kù)的主鍵范圍，并且使用for update關(guān)鍵字，加了多個(gè)行鎖，這個(gè)事務(wù)沒(méi)有commit。

此時(shí)，開(kāi)啟了另外一個(gè)事務(wù)2，也更新id=1的用戶的年齡：

begin;update user set age=23 where id=1;commit;

執(zhí)行結(jié)果跟主鍵的情況也是一樣的。

此時(shí)，開(kāi)啟了另外一個(gè)事務(wù)2，也更新id=2的用戶的年齡：

begin;update user set age=23 where id=2;commit;

執(zhí)行結(jié)果跟主鍵的情況也是一樣的。

6. 普通字段

當(dāng)where條件用的數(shù)據(jù)庫(kù)普通字段時(shí)。

該字段既不是主鍵，也不是索引。

開(kāi)啟一個(gè)事務(wù)1，在事務(wù)中更新age=22的用戶的年齡：

begin;select * from user where age=22 for update;update user set age=22 where age=22 ;

where條件中的age是數(shù)據(jù)庫(kù)的普通字段，并且使用for update關(guān)鍵字，加的是表鎖，這個(gè)事務(wù)沒(méi)有commit。

此時(shí)，開(kāi)啟了另外一個(gè)事務(wù)2，也更新age=22的用戶的年齡：

begin;update user set age=23 where age=22 ;commit;

此時(shí)，執(zhí)行事務(wù)2時(shí)，會(huì)一直阻塞等待事務(wù)1釋放鎖。

調(diào)整一下sql條件，查詢條件改成age=23：

begin;update user set age=23 where age=23 ;commit;

此時(shí)，行事務(wù)3時(shí)，也會(huì)一直阻塞等待事務(wù)1釋放鎖。

也就是說(shuō)，在for update語(yǔ)句中，使用普通字段作為查詢條件時(shí)，加的是表鎖，而并非行鎖。

7. 空數(shù)據(jù)

當(dāng)where條件查詢的數(shù)據(jù)不存在時(shí)，會(huì)發(fā)生什么呢？

開(kāi)啟一個(gè)事務(wù)1，在事務(wù)中更新id=66的用戶的年齡：

begin;select * from user where id=66 for update;update user set age=22 where id=66 ;

這條數(shù)據(jù)是不存在的。

此時(shí)，開(kāi)啟了另外一個(gè)事務(wù)2，也更新id=66的用戶的年齡：

begin;update user set age=23 where id=66 ;commit;

執(zhí)行結(jié)果：

執(zhí)行成功了，說(shuō)明這種情況沒(méi)有加鎖。

總結(jié)

最后給大家總結(jié)一下select...for update加鎖的情況：

1. 主鍵字段：加行鎖。

2. 唯一索引字段：加行鎖。

3. 普通索引字段：加行鎖。

4. 主鍵范圍：加多個(gè)行鎖。

5. 普通字段：加表鎖。

6. 查詢空數(shù)據(jù)：不加鎖。

如果事務(wù)1加了行鎖，一直沒(méi)有釋放鎖，事務(wù)2操作相同行的數(shù)據(jù)時(shí)，會(huì)一直等待直到超時(shí)。

如果事務(wù)1加了表鎖，一直沒(méi)有釋放鎖，事務(wù)2不管操作的是哪一行數(shù)據(jù)，都會(huì)一直等待直到超時(shí)。

作者：蘇三說(shuō)技術(shù)

來(lái)源：博客園

查看原文