這篇文章主要介紹了MySQL中SELECT+UPDATE處理并發(fā)更新問(wèn)題解決方案分享,需要的朋友可以參考下。

問(wèn)題背景

假設(shè)MySQL數(shù)據(jù)庫(kù)有一張會(huì)員表vip_member（InnoDB表），結(jié)構(gòu)如下：

當(dāng)一個(gè)會(huì)員想續(xù)買(mǎi)會(huì)員（只能續(xù)買(mǎi)1個(gè)月、3個(gè)月或6個(gè)月）時(shí)，必須滿足以下業(yè)務(wù)要求：

如果end_at早于當(dāng)前時(shí)間，則設(shè)置start_at為當(dāng)前時(shí)間，end_at為當(dāng)前時(shí)間加上續(xù)買(mǎi)的月數(shù)如果end_at等于或晚于當(dāng)前時(shí)間，則設(shè)置end_at=end_at+續(xù)買(mǎi)的月數(shù)續(xù)買(mǎi)后active_status必須為1（即被激活）問(wèn)題分析

對(duì)于上面這種情況，我們一般會(huì)先SELECT查出這條記錄，然后根據(jù)查出記錄的end_at再UPDATE start_at和end_at，偽代碼如下（為uid是1001的會(huì)員續(xù)1個(gè)月）：

vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 # 查uid為1001的會(huì)員 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

假如同時(shí)有兩個(gè)線程執(zhí)行上面的代碼，很顯然存在“數(shù)據(jù)覆蓋”問(wèn)題（即一個(gè)是續(xù)1個(gè)月，一個(gè)續(xù)2個(gè)月，但最終可能只續(xù)了2個(gè)月，而不是加起來(lái)的3個(gè)月）。

解決方案

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

如下：

UPDATE vip_member SET start_at = CASE WHEN end_at < NOW() THEN NOW()ELSE start_atEND, end_at = CASEWHEN 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;

B、第二種方案：事務(wù)，即用一個(gè)事務(wù)來(lái)包裹上面的SELECT+UPDATE操作

那么是否包上事務(wù)就萬(wàn)事大吉了呢？

顯然不是。因?yàn)槿绻瑫r(shí)有兩個(gè)事務(wù)都分別SELECT到相同的vip_member記錄，那么一樣的會(huì)發(fā)生數(shù)據(jù)覆蓋問(wèn)題。那有什么辦法可以解決呢？難道要設(shè)置事務(wù)隔離級(jí)別為SERIALIZABLE，考慮到性能不現(xiàn)實(shí)。

我們知道InnoDB支持行鎖。查看MySQL官方文檔（innodb locking reads）了解到InnoDB在讀取行數(shù)據(jù)時(shí)可以加兩種鎖：讀共享鎖和寫(xiě)?yīng)氄兼i。

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

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

如果事務(wù)A獲得了先獲得了讀共享鎖，那么事務(wù)B之后仍然可以讀取加了讀共享鎖的行數(shù)據(jù)，但必須等事務(wù)A commit或者roll back之后才可以更新或者刪除加了讀共享鎖的行數(shù)據(jù)。

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

如果事務(wù)A先獲得了某行的寫(xiě)共享鎖，那么事務(wù)B就必須等待事務(wù)A commit或者roll back之后才可以訪問(wèn)行數(shù)據(jù)。

顯然要解決會(huì)員狀態(tài)更新問(wèn)題，不能加讀共享鎖，只能加寫(xiě)共享鎖，即將前面的SQL改寫(xiě)成如下：

vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 FOR UPDATE # 查uid為1001的會(huì)員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=1001else: 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條件中設(shè)定索引過(guò)濾條件，否則會(huì)鎖表，性能可想而知有多差了。

C、第三種方案：樂(lè)觀鎖，類(lèi)CAS機(jī)制

第二種加鎖方案是一種悲觀鎖機(jī)制。而且SELECT...FOR UPDATE方式也不太常用，聯(lián)想到CAS實(shí)現(xiàn)的樂(lè)觀鎖機(jī)制，于是我想到了第三種解決方案：樂(lè)觀鎖。

具體來(lái)說(shuō)也挺簡(jiǎn)單，首先SELECT SQL不作任何修改，然后在UPDATE SQL的WHERE條件中加上SELECT出來(lái)的vip_memer的end_at條件。如下：

vipMember = SELECT * FROM vip_member WHERE uid=1001 LIMIT 1 # 查uid為1001的會(huì)員cur_end_at = vipMember.end_atif 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_atelse: 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

這樣可以根據(jù)UPDATE返回值來(lái)判斷是否更新成功，如果返回值是0則表明存在并發(fā)更新，那么只需要重試一下就好了。

方案比較

三種方案各自優(yōu)劣也許眾說(shuō)紛紜，只說(shuō)說(shuō)我自己的看法：

第一種方案利用一條比較復(fù)雜的SQL解決問(wèn)題，不利于維護(hù)，因?yàn)榘丫唧w業(yè)務(wù)糅在SQL里了，以后修改業(yè)務(wù)時(shí)不但需要讀懂這條SQL，還很有可能會(huì)修改成更復(fù)雜的SQL第二種方案寫(xiě)?yīng)氄兼i，可以解決問(wèn)題，但不常用第三種方案應(yīng)該是比較中庸的解決方案，并且甚至可以不加事務(wù)，也是我個(gè)人推薦的方案

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

如果對(duì)讀的響應(yīng)度要求非常高，比如證券交易系統(tǒng)，那么適合用樂(lè)觀鎖，因?yàn)楸^鎖會(huì)阻塞讀如果讀遠(yuǎn)多于寫(xiě)，那么也適合用樂(lè)觀鎖，因?yàn)橛帽^鎖會(huì)導(dǎo)致大量讀被少量的寫(xiě)阻塞如果寫(xiě)操作頻繁并且沖突比例很高，那么適合用悲觀寫(xiě)?yīng)氄兼i