首頁 >資料庫 >mysql教程 >詳解Mysql 事務及資料的一致性處理

詳解Mysql 事務及資料的一致性處理

小云云原創: 2017-12-18 15:39:251701瀏覽

本文我們將和大家分享詳解Mysql 事務及資料的一致性處理。在工作中，我們經常會遇到這樣的問題，需要更新庫存，當我們查詢到可用的庫存準備修改時，這時，其他的用戶可能已經對這個庫存數據進行修改了，導致，我們查詢到的數據會有問題，下面我們就來看解決方法。

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

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

舉個例子:

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

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

為什麼不安全呢?

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

於是我們在MySQL 就可以這樣測試，程式碼如下:

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

此時products 資料中id=3 的資料被鎖住(註3)，其它事務必須等待此次事務提交後才能執行

SELECT * FROM products WHERE id=3 FOR UPDATE

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

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

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

更進階用法

如果我們需要先查詢，然後更新資料的話，最好可以這樣使用語句：

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

這樣，可以不用新增事物就可處理。

mysql處理高並發，防止庫存超賣

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

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

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

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

範例：

總庫存：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为请求减掉的库存数量

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

    }

}catch($e Exception){

    rollBack(回滚)

}

commit(提交事务)

以上程式碼就是我們平常控制庫存寫的程式碼了，大多數人都會這麼寫，看似問題不大，其實隱藏著巨大的漏洞。資料庫的存取其實就是對磁碟文件的訪問，資料庫中的表其實就是保存在磁碟上的一個個文件，甚至一個文件包含了多個表。例如由於高並發，目前有三個用戶a、b、c三個用戶進入到了這個事務中，這個時候會產生一個共享鎖，所以在select的時候，這三個用戶查到的庫存數量都是4個，同時也要注意，mysql innodb查到的結果是有版本控制的，再其他用戶更新沒有commit之前(也就是沒有產生新版本之前)，當前用戶查到的結果依然是就版本；

接著是update，假如這三個使用者同時到達update這裡，這個時候update更新語句會把並發串行化，也就是給同時到達這裡的是三個用戶排個序，一個一個執行，並產生排他鎖，在目前這個update語句commit之前，其他使用者等待執行，commit後，產生新的版本；這樣執行完後，庫存肯定為負數了。但根據以上描述，我們修改一下程式碼就不會出現超買現象了，程式碼如下：

beginTranse(开启事务)

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(&#39;库存不足&#39;);

    }

}catch($e Exception){

    rollBack(回滚)

}

commit(提交事务)

另外，更簡潔的方法：

beginTranse(开启事务)

try{

    //quantity为请求减掉的库存数量
    $dbca->query('update s_store set amount = amount - quantity where amount>=quantity and postID = 12345');

}catch($e Exception){

    rollBack(回滚)

}

commit(提交事务)

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

1、在秒杀的情况下，肯定不能如此高频率的去读写数据库，会严重造成性能问题的
必须使用缓存，将需要秒杀的商品放入缓存中，并使用锁来处理其并发情况。当接到用户秒杀提交订单的情况下，先将商品数量递减（加锁/解锁）后再进行其他方面的处理，处理失败在将数据递增1（加锁/解锁），否则表示交易成功。
当商品数量递减到0时，表示商品秒杀完毕，拒绝其他用户的请求。

2、这个肯定不能直接操作数据库的，会挂的。直接读库写库对数据库压力太大，要用缓存。
把你要卖出的商品比如10个商品放到缓存中；然后在memcache里设置一个计数器来记录请求数，这个请求书你可以以你要秒杀卖出的商品数为基数，比如你想卖出10个商品，只允许100个请求进来。那当计数器达到100的时候，后面进来的就显示秒杀结束，这样可以减轻你的服务器的压力。然后根据这100个请求，先付款的先得后付款的提示商品以秒杀完。

3、首先，多用户并发修改同一条记录时，肯定是后提交的用户将覆盖掉前者提交的结果了。

这个直接可以使用加锁机制去解决，乐观锁或者悲观锁。
乐观锁:，就是在数据库设计一个版本号的字段，每次修改都使其+1，这样在提交时比对提交前的版本号就知道是不是并发提交了，但是有个缺点就是只能是应用中控制，如果有跨应用修改同一条数据乐观锁就没办法了，这个时候可以考虑悲观锁。

悲观锁:，就是直接在数据库层面将数据锁死，类似于oralce中使用select xxxxx from xxxx where xx=xx for update，这样其他线程将无法提交数据。

除了加锁的方式也可以使用接收锁定的方式，思路是在数据库中设计一个状态标识位，用户在对数据进行修改前，将状态标识位标识为正在编辑的状态，这样其他用户要编辑此条记录时系统将发现有其他用户正在编辑，则拒绝其编辑的请求，类似于你在操作系统中某文件正在执行，然后你要修改该文件时，系统会提醒你该文件不可编辑或删除。

4、不建议在数据库层面加锁，建议通过服务端的内存锁（锁主键）。当某个用户要修改某个id的数据时，把要修改的id存入memcache，若其他用户触发修改此id的数据时，读到memcache有这个id的值时，就阻止那个用户修改。

5、实际应用中，并不是让mysql去直面大并发读写，会借助“外力”，比如缓存、利用主从库实现读写分离、分表、使用队列写入等方法来降低并发读写。

悲观锁和乐观锁

首先，多用户并发修改同一条记录时，肯定是后提交的用户将覆盖掉前者提交的结果了。这个直接可以使用加锁机制去解决，乐观锁或者悲观锁。

　　悲观锁(Pessimistic Lock), 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

　　乐观锁(Optimistic Lock), 顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。

　　两种锁各有优缺点，不能单纯的定义哪个好于哪个。乐观锁比较适合数据修改比较少，读取比较频繁的场景，即使出现了少量的冲突，这样也省去了大量的锁的开销，故而提高了系统的吞吐量。但是如果经常发生冲突（写数据比较多的情况下），上层应用不不断的retry，这样反而降低了性能，对于这种情况使用悲观锁就更合适。

实战

詳解Mysql 事務及資料的一致性處理

对这个表的 amount 进行修改，开两个命令行窗口

第一个窗口A;

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

第二个窗口B：

# 更新订单ID 124 的库存数量
UPDATE `order_tbl` SET amount = 1 WHERE order_id = 124;

我们可以看到窗口A加了事物，锁住了这条数据，窗口B执行时会出现这样的问题：

詳解Mysql 事務及資料的一致性處理

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

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 事務及資料的一致性處理

為什麼不安全呢?

更進階用法

mysql處理高並發，防止庫存超賣

悲观锁和乐观锁

实战

相關文章