視訊+圖文串講：MySQL 行鎖、間隙鎖、Next-Key-Lock、以及實現記錄存在的話就更新，如果記錄不存在的話就插入如何保證併發安全

導讀

Hi，大家好！我是白日夢！本文是MySQL專題的第 27 篇。

下文還是白日夢以自導自演的方式，圍繞“如何實現記錄存在的話就更新，如果記錄不存在的話就插入。”展開本話題。看看你能抗到第幾問吧

換一種寫作風格，自導自演面試現場！感覺這樣還是比較有趣的，歡迎大家訂閱我的MySQL專題，公眾號首發！持續更新中～

點選閱讀原文，有視訊串講、視訊實戰各種案例、格式也會好看一點哦～

點選閱讀原文，有視訊串講、視訊實戰各種案例、格式也會好看一點哦～

點選閱讀原文，有視訊串講、視訊實戰各種案例、格式也會好看一點哦～

歡迎關注白日夢，公眾號首發！持續連載中

推薦閱讀原文，可以看視訊教程！

推薦閱讀原文，可以看視訊教程！

推薦閱讀原文，可以看視訊教程！

                         

那我們繼續，還是這道場景題：現在我的業務中有這樣的需求：如果目標記錄存在的話我就更新它，如果記錄不存在的話我就插入。說說看你知道哪些實現方式吧！

嗯，比如我可以像下面這樣做

這種方式。

// 虛擬碼user=User.FindById(1)if user == null{  user.Insert()}else{  user.Update()}

嗯！你這段程式碼如果不存在併發訪問還好，一旦出現併發訪問的情況。你這段邏輯會有諸多的併發修改異常的！

比如這樣的例子：商品有上線和下線的狀態，然後管理員可以在後臺頁面中修改商品的狀態，比如程式碼這樣寫的：

// 虛擬碼，如果將狀態置反if product.Status == “online”{   product.Status = "offline";}else{   product.Status = "online";  }

這時管理員A、B併發的去操作商品狀態：

嗯，確實存在這種情況，不過我可以自定義FindById()中的sql語句，通過 select for update的方式，規避你在圖畫出來的風險。

比如自定義SQL，讓 user = User.FindByID(1)函式執行的SQL為

select * from user where id = 1 for update;

直接select時會給id = 1的行新增一把讀鎖，現在我通過select for update檢索，在讀select時給id = 的行新增寫鎖。

那麼當我在讀取使用這行資料時，其他的人select for update

就會被阻塞，因為寫鎖之間彼此是互

斥的。最終也不會出現Update彼此覆蓋的情況

哦？那你畫一個時序圖出來瞧瞧

嗯嗯，時序圖大概長下面這樣

嗯，乍一看你上面畫的這個圖是沒問題的，但我有個問題：如果id = xx的行不存在呢？

嗯，所以我們不如分不同情況討論一下。下面我列舉不同場景，你畫時序圖怎樣？

嗯嗯，好的！ 

嗯！首先我們知道了，select for update會嘗試新增X鎖，也就是寫鎖。

常見的寫鎖有這麼幾種：

1、record lock 給指定行記錄新增鎖

2、gap lock 間隙鎖

3、next key lock 

下面通過在不同條件下執行select for update sql，再觀察實驗結果，就能八九不離十的推測出for update的加鎖情況。

實驗一已知條件如下：

create table t(a int,b int,primary key(a),key(b));insert into t select 1,1;insert into t select 3,1;insert into t select 5,3;insert into t select 7,6;insert into t select 10,8;

select for update sql為：

select * from t where a= 5 for update;

提示：注意id=5的行已經存在了。

下面你畫一下時序圖吧

嗯嗯，時序圖加鎖情況如下：

嗯，那如果我們需要執行的sql如下：

select * from t where b = 3 for update;

注意b並不是唯一鍵，它只是一個普通索引哦！

你能畫一下它的加鎖時序圖嗎？

嗯嗯，時序圖如下：

(最後一條Sql條件是 where a = 5，不是a=3)

還是使用這些資料

create table t(a int,b int,primary key(a),key(b));insert into t select 1,1;insert into t select 3,1;insert into t select 5,3;insert into t select 7,6;insert into t select 10,8;

假如我執行的sql是：

select * from t where a = 13 for update;

注意：

1、a=13的行並不存在哦！

2、a是唯一索引

你畫一下它的時序圖吧！

嗯呢，時序圖如下：

還是使用這些資料

create table t(a int,b int,primary key(a),key(b));insert into t select 1,1;insert into t select 3,1;insert into t select 5,3;insert into t select 7,6;insert into t select 10,8;

假如我執行的sql是：

select * from t where a = 8 for update;

注意：

1、a=8的行並不存在哦！

2、a是唯一索引

你畫一下它的時序圖吧！

嗯嗯、如下：

對唯一索引來說，gap的上下都鎖不住！

還是使用這些資料

create table t(a int,b int,primary key(a),key(b));insert into t select 1,1;insert into t select 3,1;insert into t select 5,3;insert into t select 7,6;insert into t select 10,8;

假如我執行的sql是：

select * from t where b = 5 for update;

注意：

1、b=5的行並不存在哦！

2、b是普通索引

你畫一下它的時序圖吧！

嗯嗯、如下。

對普通索引來說，gap鎖會 鎖上不鎖下！

嗯，對的。回到我們一開始的主題：如果資料存在我們就update

資料不存在我們就insert的話題來看的話。

通過如下方案：

begin；select for update；# insert or update;commit；

是可以保證併發修改的安全性的....

嗯嗯、其實通過實驗來看，確實是安全的。

小夥子可以的！整體感覺還不錯。

歡迎關注我。不久會給你安排下一面。

我沒有問題了，你有什麼想問我的嗎？

暫時沒有了，感謝大佬！

推薦閱讀

1. MySQL的修仙之路，圖文談談如何學MySQL、如何進階！（已釋出）
2. 面前突擊！33道資料庫高頻面試題，你值得擁有！（已釋出）
3. 大家常說的基數是什麼？（已釋出）
4. 講講什麼是慢查！如何監控？如何排查？（已釋出）
5. 對NotNull欄位插入Null值有啥現象？（已釋出）
6. 能談談 date、datetime、time、timestamp、year的區別嗎？（已釋出）
7. 瞭解資料庫的查詢快取和BufferPool嗎？談談看！（已釋出）
8. 你知道資料庫緩衝池中的LRU-List嗎？（已釋出）
9. 談談資料庫緩衝池中的Free-List？（已釋出）
10. 談談資料庫緩衝池中的Flush-List？（已釋出）
11. 瞭解髒頁刷回磁碟的時機嗎？（已釋出）
12. 用十一張圖講清楚，當你CRUD時BufferPool中發生了什麼！以及BufferPool的優化！（已釋出）
13. 聽說過表空間沒？什麼是表空間？什麼是資料表？(已釋出)
14. 談談MySQL的：資料區、資料段、資料頁、資料頁究竟長什麼樣？瞭解資料頁分裂嗎？談談看！(已釋出)
15. 談談MySQL的行記錄是什麼？長啥樣？(已釋出)
16. 瞭解MySQL的行溢位機制嗎？(已釋出)
17. 說說fsync這個系統呼叫吧! (已釋出)
18. 簡述undo log、truncate、以及undo log如何幫你回滾事物! (已釋出)
19. 我勸！這位年輕人不講MVCC，耗子尾汁！ (已釋出)
20. MySQL的崩潰恢復到底是怎麼回事？ (已釋出)
21. MySQL的binlog有啥用？誰寫的？在哪裡？怎麼配置 (已釋出)
22. MySQL的bin log的寫入機制 (已釋出)
23. 刪庫後！除了跑路還能幹什麼？(已釋出)
24. 自導自演的面試現場，趣學資料庫的10種檔案(已釋出)
25. 大型面試現場：一條update sql執行都經歷什麼？(已釋出)
26. 大型翻車現場：如何實現記錄存在的話就更新，如果記錄不存在的話就插入。(已釋出)