Mysql事務(wù)和數據中一致性處理的示例分析

這篇文章主要介紹了事務(wù)和數據中一致性處理的示例分析，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著(zhù)大家一起了解一下。

在工作中，我們經(jīng)常會(huì )遇到這樣的問(wèn)題，需要更新庫存，當我們查詢(xún)到可用的庫存準備修改時(shí)，這時(shí)，其他的用戶(hù)可能已經(jīng)對這個(gè)庫存數據進(jìn)行修改了，導致，我們查詢(xún)到的數據會(huì )有問(wèn)題，下面我們就來(lái)看解決方法。

在MySQL的InnoDB中，預設的Tansaction isolation level 為REPEATABLE READ（可重讀）

如果SELECT 后面若要UPDATE 同一個(gè)表單，最好使用SELECT ... UPDATE。

舉個(gè)例子:

假設商品表單products 內有一個(gè)存放商品數量的quantity ，在訂單成立之前必須先確定quantity 商品數量是否足夠(quantity>0) ，然后才把數量更新為1。代碼如下:

SELECT quantity FROM products WHERE id=3; UPDATE products SET quantity = 1 WHERE id=3;

為什么不安全呢?

少量的狀況下或許不會(huì )有問(wèn)題，但是大量的數據存取「鐵定」會(huì )出問(wèn)題。如果我們需要在quantity>0 的情況下才能扣庫存，假設程序在第一行SELECT 讀到的quantity 是2 ，看起來(lái)數字沒(méi)有錯，但是當MySQL 正準備要UPDATE 的時(shí)候，可能已經(jīng)有人把庫存扣成0 了，但是程序卻渾然不知，將錯就錯的UPDATE 下去了。因此必須透過(guò)的事務(wù)機制來(lái)確保讀取及提交的數據都是正確的。

于是我們在MySQL 就可以這樣測試，代碼如下:

SET AUTOCOMMIT=0; BEGIN WORK; SELECT quantity FROM products WHERE id=3 FOR UPDATE;

此時(shí)products 數據中id=3 的數據被鎖住(注3)，其它事務(wù)必須等待此次事務(wù) 提交后才能執行

SELECT * FROM products WHERE id=3 FOR UPDATE

如此可以確保quantity 在別的事務(wù)讀到的數字是正確的。

UPDATE products SET quantity = '1' WHERE id=3 ; COMMIT WORK;

提交(Commit)寫(xiě)入數據庫，products 解鎖。

注1: BEGIN/COMMIT 為事務(wù)的起始及結束點(diǎn)，可使用二個(gè)以上的MySQL Command 視窗來(lái)交互觀(guān)察鎖定的狀況。

注2: 在事務(wù)進(jìn)行當中，只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一筆數據時(shí)會(huì )等待其它事務(wù)結束后才執行，一般SELECT ... 則不受此影響。

注3: 由于InnoDB 預設為Row-level Lock，數據列的鎖定可參考這篇。

注4: InnoDB 表單盡量不要使用LOCK TABLES 指令，若情非得已要使用，請先看官方對于InnoDB 使用LOCK TABLES 的說(shuō)明，以免造成系統經(jīng)常發(fā)生死鎖。

更高級用法

如果我們需要先查詢(xún)，后更新數據的話(huà)，最好可以這樣使用語(yǔ)句：

UPDATE products SET quantity = '1' WHERE id=3 AND quantity > 0;

這樣，可以不用添加事物就可處理。

mysql處理高并發(fā)，防止庫存超賣(mài)

看到了一篇非常好的文章，特轉此學(xué)習。

今天王總又給我們上了一課，其實(shí)mysql處理高并發(fā)，防止庫存超賣(mài)的問(wèn)題，在去年的時(shí)候，王總已經(jīng)提過(guò)；但是很可惜，即使當時(shí)大家都聽(tīng)懂了，但是在現實(shí)開(kāi)發(fā)中，還是沒(méi)這方面的意識。今天就我的一些理解，整理一下這個(gè)問(wèn)題，并希望以后這樣的課程能多點(diǎn)。

先來(lái)就庫存超賣(mài)的問(wèn)題作描述：一般電子商務(wù)網(wǎng)站都會(huì )遇到如團購、秒殺、特價(jià)之類(lèi)的活動(dòng)，而這樣的活動(dòng)有一個(gè)共同的特點(diǎn)就是訪(fǎng)問(wèn)量激增、上千甚至上萬(wàn)人搶購一個(gè)商品。然而，作為活動(dòng)商品，庫存肯定是很有限的，如何控制庫存不讓出現超買(mǎi)，以防止造成不必要的損失是眾多電子商務(wù)網(wǎng)站程序員頭疼的問(wèn)題，這同時(shí)也是最基本的問(wèn)題。

從技術(shù)方面剖析，很多人肯定會(huì )想到事務(wù)，但是事務(wù)是控制庫存超賣(mài)的必要條件，但不是充分必要條件。

舉例：

總庫存：4個(gè)商品

請求人：a、1個(gè)商品 b、2個(gè)商品 c、3個(gè)商品

程序如下：

beginTranse(開(kāi)啟事務(wù))
try{
 $result = $dbca->query('select amount from s_store where postID = 12345');
 if(result->amount > 0){
  //quantity為請求減掉的庫存數量
  $dbca->query('update s_store set amount = amount - quantity where postID = 12345');
 }
}catch($e Exception){
 rollBack(回滾)
}
commit(提交事務(wù))

以上代碼就是我們平時(shí)控制庫存寫(xiě)的代碼了，大多數人都會(huì )這么寫(xiě)，看似問(wèn)題不大，其實(shí)隱藏著(zhù)巨大的漏洞。數據庫的訪(fǎng)問(wèn)其實(shí)就是對磁盤(pán)文件的訪(fǎng)問(wèn)，數據庫中的表其實(shí)就是保存在磁盤(pán)上的一個(gè)個(gè)文件，甚至一個(gè)文件包含了多張表。例如由于高并發(fā)，當前有三個(gè)用戶(hù)a、b、c三個(gè)用戶(hù)進(jìn)入到了這個(gè)事務(wù)中，這個(gè)時(shí)候會(huì )產(chǎn)生一個(gè)共享鎖，所以在select的時(shí)候，這三個(gè)用戶(hù)查到的庫存數量都是4個(gè)，同時(shí)還要注意，mysql innodb查到的結果是有版本控制的，再其他用戶(hù)更新沒(méi)有commit之前(也就是沒(méi)有產(chǎn)生新版本之前)，當前用戶(hù)查到的結果依然是就版本；

然后是update，假如這三個(gè)用戶(hù)同時(shí)到達update這里，這個(gè)時(shí)候update更新語(yǔ)句會(huì )把并發(fā)串行化，也就是給同時(shí)到達這里的是三個(gè)用戶(hù)排個(gè)序，一個(gè)一個(gè)執行，并生成排他鎖，在當前這個(gè)update語(yǔ)句commit之前，其他用戶(hù)等待執行，commit后，生成新的版本；這樣執行完后，庫存肯定為負數了。但是根據以上描述，我們修改一下代碼就不會(huì )出現超買(mǎi)現象了，代碼如下：

beginTranse(開(kāi)啟事務(wù))
try{
 //quantity為請求減掉的庫存數量
 $dbca->query('update s_store set amount = amount - quantity where postID = 12345');
 $result = $dbca->query('select amount from s_store where postID = 12345');
 if(result->amount < 0){
  throw new Exception('庫存不足');
 }
}catch($e Exception){
 rollBack(回滾)
}
commit(提交事務(wù))

另外，更簡(jiǎn)潔的方法：

beginTranse(開(kāi)啟事務(wù))
try{
 //quantity為請求減掉的庫存數量
 $dbca->query('update s_store set amount = amount - quantity where amount>=quantity and postID = 12345');
}catch($e Exception){
 rollBack(回滾)
}
commit(提交事務(wù))

1、在秒殺的情況下，肯定不能如此高頻率的去讀寫(xiě)數據庫，會(huì )嚴重造成性能問(wèn)題的。

必須使用緩存，將需要秒殺的商品放入緩存中，并使用鎖來(lái)處理其并發(fā)情況。當接到用戶(hù)秒殺提交訂單的情況下，先將商品數量遞減（加鎖/解鎖）后再進(jìn)行其他方面的處理，處理失敗在將數據遞增1（加鎖/解鎖），否則表示交易成功。
當商品數量遞減到0時(shí)，表示商品秒殺完畢，拒絕其他用戶(hù)的請求。

2、這個(gè)肯定不能直接操作數據庫的，會(huì )掛的。直接讀庫寫(xiě)庫對數據庫壓力太大，要用緩存。

把你要賣(mài)出的商品比如10個(gè)商品放到緩存中；然后在memcache里設置一個(gè)計數器來(lái)記錄請求數，這個(gè)請求書(shū)你可以以你要秒殺賣(mài)出的商品數為基數，比如你想賣(mài)出10個(gè)商品，只允許100個(gè)請求進(jìn)來(lái)。那當計數器達到100的時(shí)候，后面進(jìn)來(lái)的就顯示秒殺結束，這樣可以減輕你的的壓力。然后根據這100個(gè)請求，先付款的先得后付款的提示商品以秒殺完。

3、首先，多用戶(hù)并發(fā)修改同一條記錄時(shí)，肯定是后提交的用戶(hù)將覆蓋掉前者提交的結果了。

這個(gè)直接可以使用加鎖機制去解決，樂(lè )觀(guān)鎖或者悲觀(guān)鎖。

樂(lè )觀(guān)鎖:，就是在數據庫設計一個(gè)版本號的字段，每次修改都使其+1，這樣在提交時(shí)比對提交前的版本號就知道是不是并發(fā)提交了，但是有個(gè)缺點(diǎn)就是只能是應用中控制，如果有跨應用修改同一條數據樂(lè )觀(guān)鎖就沒(méi)辦法了，這個(gè)時(shí)候可以考慮悲觀(guān)鎖。

悲觀(guān)鎖:，就是直接在數據庫層面將數據鎖死，類(lèi)似于oralce中使用select xxxxx from xxxx where xx=xx for update，這樣其他線(xiàn)程將無(wú)法提交數據。

除了加鎖的方式也可以使用接收鎖定的方式，思路是在數據庫中設計一個(gè)狀態(tài)標識位，用戶(hù)在對數據進(jìn)行修改前，將狀態(tài)標識位標識為正在編輯的狀態(tài)，這樣其他用戶(hù)要編輯此條記錄時(shí)系統將發(fā)現有其他用戶(hù)正在編輯，則拒絕其編輯的請求，類(lèi)似于你在操作系統中某文件正在執行，然后你要修改該文件時(shí)，系統會(huì )提醒你該文件不可編輯或刪除。

4、不建議在數據庫層面加鎖，建議通過(guò)服務(wù)端的內存鎖（鎖主鍵）。當某個(gè)用戶(hù)要修改某個(gè)id的數據時(shí)，把要修改的id存入memcache，若其他用戶(hù)觸發(fā)修改此id的數據時(shí)，讀到memcache有這個(gè)id的值時(shí)，就阻止那個(gè)用戶(hù)修改。

5、實(shí)際應用中，并不是讓mysql去直面大并發(fā)讀寫(xiě)，會(huì )借助“外力”，比如緩存、利用主從庫實(shí)現讀寫(xiě)分離、分表、使用隊列寫(xiě)入等方法來(lái)降低并發(fā)讀寫(xiě)。

悲觀(guān)鎖和樂(lè )觀(guān)鎖

首先，多用戶(hù)并發(fā)修改同一條記錄時(shí)，肯定是后提交的用戶(hù)將覆蓋掉前者提交的結果了。這個(gè)直接可以使用加鎖機制去解決，樂(lè )觀(guān)鎖或者悲觀(guān)鎖。

悲觀(guān)鎖(Pessimistic Lock), 顧名思義，就是很悲觀(guān)，每次去拿數據的時(shí)候都認為別人會(huì )修改，所以每次在拿數據的時(shí)候都會(huì )上鎖，這樣別人想拿這個(gè)數據就會(huì )block直到它拿到鎖。傳統的里邊就用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫(xiě)鎖等，都是在做操作之前先上鎖。

樂(lè )觀(guān)鎖(Optimistic Lock), 顧名思義，就是很樂(lè )觀(guān)，每次去拿數據的時(shí)候都認為別人不會(huì )修改，所以不會(huì )上鎖，但是在更新的時(shí)候會(huì )判斷一下在此期間別人有沒(méi)有去更新這個(gè)數據，可以使用版本號等機制。樂(lè )觀(guān)鎖適用于多讀的應用類(lèi)型，這樣可以提高吞吐量，像數據庫如果提供類(lèi)似于write_condition機制的其實(shí)都是提供的樂(lè )觀(guān)鎖。

兩種鎖各有優(yōu)缺點(diǎn)，不能單純的定義哪個(gè)好于哪個(gè)。樂(lè )觀(guān)鎖比較適合數據修改比較少，讀取比較頻繁的場(chǎng)景，即使出現了少量的沖突，這樣也省去了大量的鎖的開(kāi)銷(xiāo)，故而提高了系統的吞吐量。但是如果經(jīng)常發(fā)生沖突（寫(xiě)數據比較多的情況下），上層應用不不斷的retry，這樣反而降低了性能，對于這種情況使用悲觀(guān)鎖就更合適。

實(shí)戰

對這個(gè)表的 amount 進(jìn)行修改，開(kāi)兩個(gè)命令行窗口

第一個(gè)窗口A(yíng);

SET AUTOCOMMIT=0; BEGIN WORK; SELECT * FROM order_tbl WHERE order_id='124' FOR UPDATE;

第二個(gè)窗口B：

# 更新訂單ID 124 的庫存數量
UPDATE `order_tbl` SET amount = 1 WHERE order_id = 124;

我們可以看到窗口A(yíng)加了事物，鎖住了這條數據，窗口B執行時(shí)會(huì )出現這樣的問(wèn)題：

第一個(gè)窗口完整的提交事物：

SET AUTOCOMMIT=0; BEGIN WORK; SELECT * FROM order_tbl WHERE order_id='124' FOR UPDATE;
UPDATE `order_tbl` SET amount = 10 WHERE order_id = 124;
COMMIT WORK;