MySQL中SELECT+UPDATE併發更新問題

發表於2019-05-11
MySql

問題背景：

假設MySQL資料庫有一張會員表vip_member（InnoDB表），結構如下：

當一個會員想續買會員（只能續買1個月、3個月或6個月）時，必須滿足以下業務要求：

  • 如果end_at早於當前時間，則設定start_at為當前時間，end_at為當前時間加上續買的月數
  • 如果end_at等於或晚於當前時間，則設定end_at=end_at+續買的月數
  • 續買後active_status必須為1（即被啟用）

問題分析：

對於上面這種情況，我們一般會先SELECT查出這條記錄，然後根據查出記錄的end_at再UPDATE start_at和end_at，虛擬碼如下（為uid是1001的會員續1個月）： 

vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 # 查uid為1001的會員
if vipMember.end_at < NOW(): UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001 else: UPDATE vip_member SET end_at=DATE_ADD(end_at, INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001



假如同時有兩個執行緒執行上面的程式碼，很顯然存在“資料覆蓋”問題（即一個是續1個月，一個續2個月，但最終可能只續了2個月，而不是加起來的3個月）。

解決方案：

A、我想到的第一種方案是把SELECT和UPDATE合成一條SQL，如下： 

UPDATE vip_member 
SET 
   start_at = CASE
              WHEN end_at < NOW() THEN NOW() ELSE start_at END, end_at = CASE WHEN end_at < NOW() THEN DATE_ADD(NOW(), INTERVAL 1 MONTH) ELSE DATE_ADD(end_at, INTERVAL 1 MONTH) END, active_status=1, updated_at=NOW() WHERE uid=#uid:BIGINT# LIMIT 1;


B、第二種方案：事務，即用一個事務來包裹上面的SELECT+UPDATE操作。

那麼是否包上事務就萬事大吉了呢？

顯然不是。因為如果同時有兩個事務都分別SELECT到相同的vip_member記錄，那麼一樣的會發生資料覆蓋問題。那有什麼辦法可以解決呢？難道要設定事務隔離級別為SERIALIZABLE，考慮到效能不現實。

我們知道InnoDB支援行鎖。檢視MySQL官方文件（innodb locking reads）瞭解到InnoDB在讀取行資料時可以加兩種鎖：讀共享鎖和寫獨佔鎖。

讀共享鎖是通過下面這樣的SQL獲得的： 

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

如果事務A獲得了先獲得了讀共享鎖，那麼事務B之後仍然可以讀取加了讀共享鎖的行資料，但必須等事務A commit或者roll back之後才可以更新或者刪除加了讀共享鎖的行資料。

寫獨佔鎖是通過SELECT...FOR UPDATE獲得： 

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

如果事務A先獲得了某行的寫共享鎖，那麼事務B就必須等待事務A commit或者roll back之後才可以訪問行資料。

顯然要解決會員狀態更新問題，不能加讀共享鎖，只能加寫共享鎖，即將前面的SQL改寫成如下： 

vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 FOR UPDATE # 查uid為1001的會員
if vipMember.end_at < NOW(): UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001 else: UPDATE vip_member SET end_at=DATE_ADD(end_at, INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001



另外這裡特別提醒下：UPDATE/DELETE SQL儘量帶上WHERE條件並在WHERE條件中設定索引過濾條件，否則會鎖表，效能可想而知有多差了。

C、第三種方案：樂觀鎖，類CAS機制

第二種加鎖方案是一種悲觀鎖機制。而且SELECT...FOR UPDATE方式也不太常用，聯想到CAS實現的樂觀鎖機制，於是我想到了第三種解決方案：樂觀鎖。

具體來說也挺簡單，首先SELECT SQL不作任何修改，然後在UPDATE SQL的WHERE條件中加上SELECT出來的vip_memer的end_at條件。如下： 

vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 # 查uid為1001的會員
cur_end_at = vipMember.end_at
if vipMember.end_at < NOW(): UPDATE vip_member SET start_at=NOW(), end_at=DATE_ADD(NOW(), INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001 AND end_at=cur_end_at else: UPDATE vip_member SET end_at=DATE_ADD(end_at, INTERVAL 1 MONTH), active_status=1, updated_at=NOW() WHERE uid=1001 AND end_at=cur_end_at

這樣可以根據UPDATE返回值來判斷是否更新成功，如果返回值是0則表明存在併發更新，那麼只需要重試一下就好了。

方案比較：

三種方案各自優劣也許眾說紛紜，只說說我自己的看法：

  • 第一種方案利用一條比較複雜的SQL解決問題，不利於維護，因為把具體業務糅在SQL裡了，以後修改業務時不但需要讀懂這條SQL，還很有可能會修改成更復雜的SQL
  • 第二種方案寫獨佔鎖，可以解決問題，但不常用
  • 第三種方案應該是比較中庸的解決方案，並且甚至可以不加事務，也是我個人推薦的方案

此外，樂觀鎖和悲觀鎖的選擇一般是這樣的（參考了文末第二篇資料）：

  • 如果對讀的響應度要求非常高，比如證券交易系統，那麼適合用樂觀鎖，因為悲觀鎖會阻塞讀
  • 如果讀遠多於寫，那麼也適合用樂觀鎖，因為用悲觀鎖會導致大量讀被少量的寫阻塞
  • 如果寫操作頻繁並且衝突比例很高，那麼適合用悲觀寫獨佔鎖





程式設計師 MYSQL 程式設計
評論(1)

相關文章