MySQL併發更新數據時的處理方法

NO IMAGE

前言

在後端開發中我們不可避免的會遇見MySQL數據併發更新的情況,作為一名後端研發,如何解決這類問題也是必須要知道的,同時這也是面試中經常考察的知識點。

UPDATE是否會加鎖?

SQL語句為如下時,是否會加鎖?

UPDATE table1 SET num = num + 1 WHERE id=1;

答案是不會

實際上MySQL是支持給數據行加鎖(InnoDB)的,並且在UPDATE/DELETE等操作時確實會自動加上排它鎖。只是並非只要有UPDATE關鍵字就會全程加鎖,針對上面的MySQL語句而言,其實並不只是一條UPDATE語句,而應該類似於兩條SQL語句(偽代碼):

a = SELECT * FROM table1 WHERE id=1;
UPDATE table1 SET num = a.num + 1 WHERE id=1; 

其中執行SELECT語句時沒有加鎖,只有在執行UPDATE時才進行加鎖的。所以才會出現併發操作時的更新數據不一致。原因找到了,解決問題就不遠了。而針對這類問題,解決的方法可以有2種:

  • 通過事務顯式的對SELECT進行加鎖
  • 使用樂觀鎖機制

SELECT顯式加鎖

對SELECT進行加鎖的方式有兩種,如下:

SELECT ... LOCK IN SHARE MODE       #共享鎖,其它事務可讀,不可更新
SELECT ... FOR UPDATE               #排它鎖,其它事務不可讀寫
如果你不使用這2種語句,默認情況下SELECT語句是不會加鎖的。並且對於上面提到的場景,必須使用排它鎖。另外,上面的2種語句只有在事務之中才能生效,否則不會生效。在MySQL命令行使用事務的方式如下:

SET AUTOCOMMIT=0; 
BEGIN WORK; 
a = SELECT num FROM table1 WHERE id=2 FOR UPDATE;  
UPDATE table1 SET num = a.num + 1 WHERE id=2; 
COMMIT WORK;
這樣只要以後更新數據時,都使用這樣事務來進行操作;那麼在併發的情況下,後執行的事務就會被堵塞,直到當前事務執行完成。(通過鎖把併發改成了順序執行)

使用樂觀鎖

樂觀鎖是鎖實現的一種機制,它總是會天真的認為所有需要修改的數據都不會衝突。所以在更新之前它不會給數據加鎖,而只是查詢了數據行的版本號(這裡的版本號屬於自定義的字段,需要在業務表的基礎上額外增加一個字段,每當更新一次就會自增或者更新)。

在具體更新數據的時候更新條件中會添加版本號信息,

  • 當版本號沒有變化的時候說明該數據行未被更新過,並且也滿足更新條件,所以會更新成功。
  • 當版本號有變化的時候,則無法更新數據行,因為條件不滿足,此時就需要在進行一次SQL操作。(重新查詢記數據行,再次使用新的版本號更新數據)

實踐

對 for update上鎖進行一次實踐

一個student表,其中有一條數據

MySQL併發更新數據時的處理方法

開啟兩個client

第一個開啟事務後執行

select name from student where id = 1 for update;

MySQL併發更新數據時的處理方法

第二個開啟事務後執行相同的語句,發現該條數據被第一個事務上鎖阻塞了

MySQL併發更新數據時的處理方法

這時候第一個事務執行修改並commit;

MySQL併發更新數據時的處理方法

第二個事務的select執行,發現阻塞了4秒多

MySQL併發更新數據時的處理方法

小結

總的來說,這2種方式都可以支持數據庫的併發更新操作。但具體使用哪一種就得看實際的應用場景,應用場景對哪種支持更好,並且對性能的影響最小。

參考自:

chenguangwu.github.io/evernode/My…

相關文章

SpringBoot+Dubbo實戰demo

IOC(二)DefaultListableBeanFactory

IOC(一)BeanFactory和BeanDefinition

SpringBean的生命週期分析