Mysql事務及數(shù)據(jù)一致性處理的示例分析

這篇文章主要介紹Mysql事務及數(shù)據(jù)一致性處理的示例分析，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

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

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

舉個例子:

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

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

為什么不安全呢?

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

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

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

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

SELECT * FROM products WHERE id=3 FOR UPDATE

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

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

提交(Commit)寫入數(shù)據(jù)庫，products 解鎖。
注1: BEGIN/COMMIT 為事務的起始及結(jié)束點，可使用二個以上的MySQL Command 視窗來交互觀察鎖定的狀況。
注2: 在事務進行當中，只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一筆數(shù)據(jù)時會等待其它事務結(jié)束后才執(zhí)行，一般SELECT ... 則不受此影響。
注3: 由于InnoDB 預設為Row-level Lock，數(shù)據(jù)列的鎖定可參考這篇。
注4: InnoDB 表單盡量不要使用LOCK TABLES 指令，若情非得已要使用，請先看官方對于InnoDB 使用LOCK TABLES 的說明，以免造成系統(tǒng)經(jīng)常發(fā)生死鎖。

更高級用法

如果我們需要先查詢，后更新數(shù)據(jù)的話，最好可以這樣使用語句：

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

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

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

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

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

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

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

舉例：

總庫存：4個商品

請求人：a、1個商品 b、2個商品 c、3個商品

程序如下：

beginTranse(開啟事務)

try{

    $result = $dbca->query('select amount from s_store where postID = 12345');

    if(result->amount > 0){

        //quantity為請求減掉的庫存數(shù)量

        $dbca->query('update s_store set amount = amount - quantity where postID = 12345');

    }

}catch($e Exception){

    rollBack(回滾)

}

commit(提交事務)

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

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

beginTranse(開啟事務)

try{

    //quantity為請求減掉的庫存數(shù)量
    $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(提交事務)

另外，更簡潔的方法：

beginTranse(開啟事務)

try{

    //quantity為請求減掉的庫存數(shù)量
    $dbca->query('update s_store set amount = amount - quantity where amount>=quantity and postID = 12345');

}catch($e Exception){

    rollBack(回滾)

}

commit(提交事務)

=====================================================================================

1、在秒殺的情況下，肯定不能如此高頻率的去讀寫數(shù)據(jù)庫，會嚴重造成性能問題的
必須使用緩存，將需要秒殺的商品放入緩存中，并使用鎖來處理其并發(fā)情況。當接到用戶秒殺提交訂單的情況下，先將商品數(shù)量遞減（加鎖/解鎖）后再進行其他方面的處理，處理失敗在將數(shù)據(jù)遞增1（加鎖/解鎖），否則表示交易成功。
當商品數(shù)量遞減到0時，表示商品秒殺完畢，拒絕其他用戶的請求。

2、這個肯定不能直接操作數(shù)據(jù)庫的，會掛的。直接讀庫寫庫對數(shù)據(jù)庫壓力太大，要用緩存。
把你要賣出的商品比如10個商品放到緩存中；然后在memcache里設置一個計數(shù)器來記錄請求數(shù)，這個請求書你可以以你要秒殺賣出的商品數(shù)為基數(shù)，比如你想賣出10個商品，只允許100個請求進來。那當計數(shù)器達到100的時候，后面進來的就顯示秒殺結(jié)束，這樣可以減輕你的服務器的壓力。然后根據(jù)這100個請求，先付款的先得后付款的提示商品以秒殺完。

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

這個直接可以使用加鎖機制去解決，樂觀鎖或者悲觀鎖。
樂觀鎖:，就是在數(shù)據(jù)庫設計一個版本號的字段，每次修改都使其+1，這樣在提交時比對提交前的版本號就知道是不是并發(fā)提交了，但是有個缺點就是只能是應用中控制，如果有跨應用修改同一條數(shù)據(jù)樂觀鎖就沒辦法了，這個時候可以考慮悲觀鎖。

悲觀鎖:，就是直接在數(shù)據(jù)庫層面將數(shù)據(jù)鎖死，類似于oralce中使用select xxxxx from xxxx where xx=xx for update，這樣其他線程將無法提交數(shù)據(jù)。

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

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

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

悲觀鎖和樂觀鎖

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

　　悲觀鎖(Pessimistic Lock), 顧名思義，就是很悲觀，每次去拿數(shù)據(jù)的時候都認為別人會修改，所以每次在拿數(shù)據(jù)的時候都會上鎖，這樣別人想拿這個數(shù)據(jù)就會block直到它拿到鎖。傳統(tǒng)的關系型數(shù)據(jù)庫里邊就用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在做操作之前先上鎖。

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

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

實戰(zhàn)

對這個表的 amount 進行修改，開兩個命令行窗口

第一個窗口A;

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

第二個窗口B：

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

我們可以看到窗口A加了事物，鎖住了這條數(shù)據(jù)，窗口B執(zhí)行時會出現(xiàn)這樣的問題：

第一個窗口完整的提交事物：

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;

以上是“Mysql事務及數(shù)據(jù)一致性處理的示例分析”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關知識，歡迎關注億速云行業(yè)資訊頻道！