MySQL交易處理

標籤：

MySQL交易處理2011-03-06 16:26 2984人閱讀 評論(0) 收藏 舉報mysqltransactionspostgresql資料庫sybasenull

MySQL5.X 都已經發布好久了，但是還有很多人認為MySQL是不支援交易處理的，這不得不怪他們是孤陋寡聞的，其實，只要你的MySQL版本支援BDB或 InnoDB表類型，那麼你的MySQL就具有交易處理的能力。這裡面，又以InnoDB表類型用的最多，雖然後來發生了諸如Oracle收購 InnoDB等令MySQL不爽的事情，但那些商業上的鬥爭與技術無關，下面以InnoDB表類型為例簡單說一下MySQL中的事務。

先來明確一下事務涉及的相關知識：

事務都應該具備ACID特徵。所謂ACID是Atomic（原子性），Consistent（一致性），Isolated（隔離性），Durable（持久性）四個詞的首字母所寫，下面以“銀行轉帳”為例來分別說明一下它們的含義：

原子性：組成交易處理的語句形成了一個邏輯單元，不能只執行其中的一部分。換句話說，事務是不可分割的最小單元。比如：銀行轉帳過程中，必須同時從一個帳戶減去轉帳金額，並加到另一個帳戶中，只改變一個帳戶是不合理的。

一致性：在交易處理執行前後，資料庫是一致的。也就是說，事務應該正確的轉換系統狀態。比如：銀行轉帳過程中，要麼轉帳金額從一個帳戶轉入另一個帳戶，要麼兩個帳戶都不變，沒有其他的情況。

隔離性：一個交易處理對另一個交易處理沒有影響。就是說任何事務都不可能看到一個處在不完整狀態下的事務。比如說，銀行轉帳過程中，在轉帳事務沒有提交之前，另一個轉帳事務只能處於等待狀態。

持久性：交易處理的效果能夠被永久儲存下來。反過來說，事務應當能夠承受所有的失敗，包括伺服器、進程、通訊以及媒體失敗等等。比如：銀行轉帳過程中，轉帳後帳戶的狀態要能被儲存下來。

再來看看哪些問題會用到交易處理：

 

這裡不說“銀行轉帳”的例子了，說一個大家實際更容易遇到的“網上購書”的例子。先假設一下問題的背景：網上購書，某書（資料庫編號為123）只剩最後一本，而這個時候，兩個使用者對這本書幾乎同時發出了購買請求，讓我們看看整個過程：

在具體分析之前，先來看看資料表的定義：

-------------------------------------------------------------------------------

create table book (     book_id unsigned int(10) not null auto_increment,     book_name varchar(100) not null,     book_price float(5, 2) not null, #我假設每本書的價格不會超過999.99元     book_number int(10) not null,     primary key (book_id) ) type = innodb; #engine = innodb也行

-------------------------------------------------------------------------------

對於使用者甲來說，他的動作稍微比乙快一點點，其購買過程所觸發的動作大致是這樣的：

-------------------------------------------------------------------------------

1. SELECT book_number FROM book WHERE  book_id = 123;

book_number大於零，購買行為並更新book_number

2. UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

購書成功

-------------------------------------------------------------------------------

而對於使用者乙來說，他的動作稍微比甲慢一點點，其購買過程所觸發的動作和甲相同：

-------------------------------------------------------------------------------

1. SELECT book_number FROM book WHERE  book_id = 123;

這個時候，甲剛剛進行完第一步的操作，還沒來得及做第二步操作，所以book_number一定大於零

2. UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

購書成功

-------------------------------------------------------------------------------

表面上看甲乙的操作都成功了，他們都買到了書，但是庫存只有一本，他們怎麼可能都成功呢？再看看資料表裡book_number的內容，已經變成 “-1”了，這當然是不能允許的（實際上，聲明這樣的列類型應該加上unsigned的屬性，以保證其不能為負，這裡是為了說明問題所以沒有這樣設定）

好了，問題陳述清楚了，再來看看怎麼利用事務來解決這個問題，開啟MySQL手冊，可以看到想用事務來保護你的SQL正確執行其實很簡單，基本就是三個語句：開始，提交，復原。

-------------------------------------------------------------------------------

開始：START TRANSACTION或BEGIN語句可以開始一項新的事務

提交：COMMIT可以提交當前事務，是變更成為永久變更

復原：ROLLBACK可以復原當前事務，取消其變更

此外，SET AUTOCOMMIT = {0 | 1}可以禁用或啟用預設的autocommit模式，用於當前串連。

-------------------------------------------------------------------------------

那是不是只要用事務語句包一下我們的SQL語句就能保證正確了呢？比如下面代碼：

-------------------------------------------------------------------------------

BEGIN;

SELECT book_number FROM book WHERE  book_id = 123;

// ...

UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

COMMIT;

-------------------------------------------------------------------------------

答案是否定了，這樣依然不能避免問題的發生，如果想避免這樣的情況，實際應該如下：

-------------------------------------------------------------------------------

BEGIN;

SELECT book_number FROM book WHERE  book_id = 123 FOR UPDATE ;

// ...

UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

COMMIT;

-------------------------------------------------------------------------------

由於加入了FOR UPDATE，所以會在此條記錄上加上一個行鎖，如果此事務沒有完全結束，那麼其他的事務在使用SELECT ... FOR UPDATE請求的時候就會處於等待狀態，直到上一個事務結束，它才能繼續，從而避免了問題的發生，需要注意的是，如果你其他的事務使用的是不帶FOR UPDATE的SELECT語句，將得不到這種保護。

最後看看PHP + MySQL事務操作的代碼示範：

實際LAMP應用中，一般PHP使用AdoDB操作MySQL，下面給出AdoDB相應的代碼方便大家查閱：

-------------------------------------------------------------------------------

<?php  // ... 
$adodb -> startTrans ();

//實際，getOne所調用的查詢也可以直接放到rowLock來進行，這裡只是為了示範效果能更明顯些。 
$adodb -> rowLock ( ‘book‘ ,  ‘book_id = 123‘ ); 
$bookNumber  =  $adodb -> getOne ( "SELECT book_number FROM book WHERE  book_id = 123" ); 
$adodb -> execute ( "UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123" ); 
$adodb -> completeTrans (); 
// ...  ?>

-------------------------------------------------------------------------------

其中，rowLock的方法就是調用的FOR UPDATE來實現的行鎖，你可能會想把“FOR UPDATE”直接寫到$adodb->getOne()調用的那條SQL語句裡面去實現行鎖的功能，不錯，那樣確實可以，但是並不是所有的資料庫 都使用“FOR UPDATE”文法來實現行鎖功能，比如Sybase使用“HOLDLOCK”的文法來實現行鎖功能，所以為了你的資料庫抽象層保持可移植性，我還是勸你 用rowLock來實現行鎖功能，至於可移植性就交給AdoDB好了，嗯，有點扯遠了，今兒就說到這裡了。

-------------------------------------------------------------------------------

附：

AdoDB中存在一個setTransactionMode()方法，能夠設定事務的隔離等級，如下：

SetTransactionMode allows you to pass in the transaction mode to use for all subsequent transactions for that connection session. Note: if you have persistent connections and using mysql or mssql, you might have to explicitly reset your transaction mode at the beginning of each page request. This is only supported in postgresql, mssql, mysql with InnoDB and oci8 currently. For example:

$db->SetTransactionMode("SERIALIZABLE"); $db->BeginTrans(); $db->Execute(...); $db->Execute(...); $db->CommiTrans();

$db->SetTransactionMode(""); // restore to default $db->StartTrans(); $db->Execute(...); $db->Execute(...); $db->CompleteTrans();

Supported values to pass in:

    * READ UNCOMMITTED (allows dirty reads, but fastest)     * READ COMMITTED (default postgres, mssql and oci8)     * REPEATABLE READ (default mysql)     * SERIALIZABLE (slowest and most restrictive)

You can also pass in database specific values such as ‘SNAPSHOT‘ for mssql or ‘READ ONLY‘ for oci8/postgres.

MySQL交易處理