MySQL中SELECT+UPDATE并發更新問題

假設MySQL資料庫有一張會員表vip_member（InnoDB表），結構如下（uid，start_at，end_at，updated_at，active_status）

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

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

1、問題分析：

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

vipMember = SELECT * FROM vip_member WHERE uid=1001LIMIT 1 # 查uid為1001的會員

if vipMember.end_at < NOW():

  UPDATEvip_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個月）。

2、解決方案：

1）我想到的第一種方案是把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;

2）事務，即用一個事務來包裹上面的SELECT+UPDATE操作：

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

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

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

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

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

寫獨占鎖是通過SELECT...FORUPDATE獲得：

SELECT counter_field FROM child_codesFOR UPDATE;

如果事務A先獲得了某行的寫獨占鎖，那麼事務B就必須等待事務Acommit或者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

3）第三種方案：樂觀鎖，類CAS機制

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

具體來說也挺簡單，首先SELECTSQL不作任何修改，然後在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