程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> MySQL中SELECT+UPDATE處理並發更新問題解決方案分享

MySQL中SELECT+UPDATE處理並發更新問題解決方案分享

編輯: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 #duration:INTEGER# MONTH)
            ELSE DATE_ADD(end_at, INTERVAL #duration:INTEGER# MONTH)
            END,
   active_status=1,
   updated_at=NOW()
WHERE uid=#uid:BIGINT#
LIMIT 1;

    So easy!

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 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

•第二種方案寫獨占鎖,可以解決問題,但不常用

•第三種方案應該是比較中庸的解決方案,並且甚至可以不加事務,也是我個人推薦的方案


此外,樂觀鎖和悲觀鎖的選擇一般是這樣的(參考了文末第二篇資料):

•如果對讀的響應度要求非常高,比如證券交易系統,那麼適合用樂觀鎖,因為悲觀鎖會阻塞讀

•如果讀遠多於寫,那麼也適合用樂觀鎖,因為用悲觀鎖會導致大量讀被少量的寫阻塞

•如果寫操作頻繁並且沖突比例很高,那麼適合用悲觀寫獨占鎖

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved