存儲過程與事務應用兩三事

項目開發中通常會有一些複雜業務場景，比如多個資料表的插入更新等，這些業務通常需要事務的支援。

spring等架構支援對事務的管理，可以更好的實作存儲和業務邏輯的分離，不過代碼或者架構層面的事務管理，最終還是落實到資料庫上，存儲過程作為一組預編譯的sql語句，性能會優于代碼層面的多次sql操作。處理一些要求原子性等的業務，可以在存儲過程中結合應用事務。

維基百科對事務的定義中，一個資料庫事務的存在包含有以下兩個目的：

為資料庫操作序列提供了一個從失敗中恢複到正常狀态的方法，同時提供了資料庫即使在異常狀态下仍能保持一緻性的方法。

當多個應用程式在并發通路資料庫時，可以在這些應用程式之間提供一個隔離方法，以防止彼此的操作互相幹擾。

事務的原子性和一緻性可以通過定義復原等操作完成，在資料庫的并發控制中，通過隔離級别用來平衡系統性能和事務的隔離性。

sql标準定義了4類隔離級别，用來限定事務内外的哪些改變是可見的，哪些是不可見的。低級别的隔離級一般支援更高的并發處理，并擁有更低的系統開銷。

read uncommitted（讀取未送出内容）：最低級别的隔離，它允許一個事務讀取還沒commit的資料，這樣會發生髒讀（dirty read），實際很少采用

read committed（讀取送出内容）：在一個事務中隻允許已經commit的記錄可見。read committed滿足了隔離的簡單定義：一個事務隻能看見已經送出事務所做的改變。但是可能會出現不可重複讀，指在一個事務内，多次讀同一資料，在這個事務還沒有結束時，另外一個事務也通路該同一資料。那麼在第一個事務中的兩次讀資料之間，由于第二個事務的修改，那麼第一個事務兩次讀到的的資料可能是不一樣的

repeatable read（可重複讀）：在一個事務開始後，其他連接配接對資料庫的修改在本事務中不可見，直到本事務結束。這樣保證在一個事務中重複讀的結果一樣，除非本事務中update資料庫。 這是innodb引擎的預設事務隔離級别，它確定同一事務的多個執行個體在并發讀取資料時，會看到同樣的資料行。但是可重複讀下可能會出現幻讀，幻讀指當使用者讀取某一範圍的資料行時，另一個事務又在該範圍内插入了新行，當使用者再讀取該範圍的資料行時，會發現有新的“幻影” 行。innodb和存儲引擎通過多版本并發控制（mvcc）機制解決了該問題

serializable（可串行化）：最進階别的隔離，隻允許事務串行執行。為了達到此目的，資料庫會鎖住每行已經讀取的記錄，其他連接配接不能修改資料直到前一事務結束，在這個級别，可能導緻大量的逾時現象和鎖競争

myisam：不支援事務，用于隻讀程式提高性能

innodb：支援acid事務、行級鎖、并發

berkeley db：支援事務

<code>select @@global.tx_isolation,@@tx_isolation;</code>

可以使用如下語句設定mysql的session隔離級别：

mysql預設的行為是在每條sql語句執行後執行一個commit語句，進而有效的将每條語句獨立為一個事務。

為了打開事務，需要允許在commit和rollback之前多條語句被執行：

（1）設定mysql的autocommit屬性為0，預設為1，通過下面的指令檢視是否打開自動送出：

<code>select @@autocommit;</code>

或者

<code>show variables like 'autocommit';</code>

（2）使用start transaction語句顯式的打開一個事務

在存儲過程中應用事務需要注意對復原的處理。

在處理事務時，可以使用sqlexception捕獲sql錯誤，然後進行相關處理；在存儲過程中，我們可以捕獲sql異常，判斷復原(rollback)還是送出(commit)，根據業務邏輯定義不同的操作。

下面的語句定義發生異常後立即復原：

更多的情況，我們需要通過異常來進行一些業務處理，這時候可以通過定義一個變量來處理異常：

select...update并發更新是常見的一類資料庫業務，比如使用者支付更新賬戶餘額，購物車下單扣減庫存等，需要通過首先進行一次查詢操作，比較餘額/庫存等，判斷是否進行下一步操作。

這類業務需要注意的是請求并發時的處理，對庫存等的查詢和更新，例如多個請求同時讀取庫存并且進行扣減操作，如果不能正确的進行資料隔離，可能會出現超賣等問題。

實作的方式也比較多，在innodb預設的的repeatable read隔離級别下，可以使用事務結合獨占的行級鎖進行更新，確定不同請求同時操作庫存和扣減發生沖突。還有一種方式是使用cas樂觀鎖，在扣減動作發生時比較目前的庫存值和上次查詢操作的庫存值，最後結合affect rows判斷操作是否成功。

這裡模拟一個簡化的商品下單操作，包括使用者下單，首先查庫存，庫存足夠則更新庫存，訂單表增加一條記錄。在存儲過程中使用事務加獨占鎖完成功能。

order表

product表

建立一個存儲過程，傳入商品id,使用者id和購買數量，傳回操作結果和庫存數量：