mysql存儲過程事務管理簡析

存儲程式提供了一個絕佳的機制來定義、封裝和管理事務。

1，MySQL的事務支援

1）MySQL的事務支援不是綁定在MySQL伺服器本身，而是與存儲引擎相關：

MyISAM：不支援事務，用于隻讀程式提高性能 
InnoDB：支援ACID事務、行級鎖、并發 
Berkeley DB：支援事務 
MyISAM：不支援事務，用于隻讀程式提高性能 
InnoDB：支援ACID事務、行級鎖、并發 
Berkeley DB：支援事務

2）隔離級别：

隔離級别決定了一個session中的事務可能對另一個session的影響、并發session對資料庫的操作、一個session中所見資料的一緻性

ANSI标準定義了4個隔離級别，MySQL的InnoDB都支援：

READ UNCOMMITTED：最低級别的隔離，通常又稱為dirty read，它允許一個事務讀取還沒commit的資料，這樣可能會提高性能，但是dirty read可能不是我們想要的 
READ COMMITTED：在一個事務中隻允許已經commit的記錄可見，如果session中select還在查詢中，另一session此時insert一條記錄，則新添加的資料不可見 
REPEATABLE READ：在一個事務開始後，其他session對資料庫的修改在本事務中不可見，直到本事務commit或rollback。在一個事務中重複select的結果一樣，除非本事務中update資料庫。 
SERIALIZABLE：最進階别的隔離，隻允許事務串行執行。為了達到此目的，資料庫會鎖住每行已經讀取的記錄，其他session不能修改資料直到前一事務結束，事務commit或取消時才釋放鎖。 
READ UNCOMMITTED：最低級别的隔離，通常又稱為dirty read，它允許一個事務讀取還沒commit的資料，這樣可能會提高性能，但是dirty read可能不是我們想要的 
READ COMMITTED：在一個事務中隻允許已經commit的記錄可見，如果session中select還在查詢中，另一session此時insert一條記錄，則新添加的資料不可見 
REPEATABLE READ：在一個事務開始後，其他session對資料庫的修改在本事務中不可見，直到本事務commit或rollback。在一個事務中重複select的結果一樣，除非本事務中update資料庫。 
SERIALIZABLE：最進階别的隔離，隻允許事務串行執行。為了達到此目的，資料庫會鎖住每行已經讀取的記錄，其他session不能修改資料直到前一事務結束，事務commit或取消時才釋放鎖。

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

set transaction isolation level {read uncommitted | read committed | repeatable read | serializable} 
set transaction isolation level {read uncommitted | read committed | repeatable read | serializable}

MySQL預設的隔離級别是REPEATABLE READ，在設定隔離級别為READ UNCOMMITTED或SERIALIZABLE時要小心，READ UNCOMMITTED會導緻資料完整性的嚴重問題，而SERIALIZABLE會導緻性能問題并增加死鎖的機率。

3)事務管理語句：

START TRANSACTION：開始事務，autocommit設為0，如果已經有一個事務在運作，則會觸發一個隐藏的COMMIT 
COMMIT：送出事務，儲存更改，釋放鎖 
ROLLBACK：復原本事務對資料庫的所有更改，然後結束事務，釋放鎖 
SAVEPOINT savepoint_name：建立一個savepoint識别符來ROLLBACK TO SAVEPOINT 
ROLLBACK TO SAVEPOINT savepoint_name：復原到從savepoint_name開始對資料庫的所有更改，這樣就允許復原事務中的一部分，保證更改的一個子集被送出 
SET TRANSACTION：允許設定事務的隔離級别 
LOCK TABLES：允許顯式的鎖住一個或多個table，會隐式的關閉目前打開的事務，建議在執行LOCK TABLES語句之前顯式的commit或rollback。我們一般是以一般在事務代碼裡不會使用LOCK TABLES 
START TRANSACTION：開始事務，autocommit設為0，如果已經有一個事務在運作，則會觸發一個隐藏的COMMIT 
COMMIT：送出事務，儲存更改，釋放鎖 
ROLLBACK：復原本事務對資料庫的所有更改，然後結束事務，釋放鎖 
SAVEPOINT savepoint_name：建立一個savepoint識别符來ROLLBACK TO SAVEPOINT 
ROLLBACK TO SAVEPOINT savepoint_name：復原到從savepoint_name開始對資料庫的所有更改，這樣就允許復原事務中的一部分，保證更改的一個子集被送出 
SET TRANSACTION：允許設定事務的隔離級别 
LOCK TABLES：允許顯式的鎖住一個或多個table，會隐式的關閉目前打開的事務，建議在執行LOCK TABLES語句之前顯式的commit或rollback。我們一般是以一般在事務代碼裡不會使用LOCK TABLES

2，

定義事務

MySQL預設的行為是在每條SQL語句執行後執行一個COMMIT語句，進而有效的将每條語句獨立為一個事務。在複雜的應用場景下這種方式就不能滿足需求了。為了打開事務，允許在COMMIT和ROLLBACK之前多條語句被執行，我們需要做以下兩步(也可以人為是兩種方式)：

1）設定MySQL的autocommit屬性為0，預設為1

2）使用START TRANSACTION語句顯式的打開一個事務（然後autocommit屬性會自動被設定為0）

如果已經打開一個事務，則SET autocommit=0不會起作用，因為START TRANSACTION會隐式的送出session中所有目前的更改，結束已有的事務，并打開一個新的事務。

使用SET AUTOCOMMIT語句的存儲過程例子:

delimiter $$ 
use test$$ 
create procedure t_insert_table() 
begin 
/** 标記是否出錯 */ 
declare t_error int default 0; 
/** 如果出現sql異常，則将t_error設定為1後繼續執行後面的操作 */ 
declare continue handler for sqlexception set t_error=1; -- 出錯處理 
/** 顯示的開啟事務，啟動它後，autocommit值會自動設定為0 */ 
start transaction; 
insert into t_bom_test(parent_id,child_id) values('C','XXXX'); 
insert into t_trigger_test(name,age) values('zhangsan',34); 
/** 标記被改變,表示事務應該復原 */ 
if t_error=1 then 
rollback; -- 事務復原 
else 
commit; -- 事務送出 
end if; 
end$$ 
delimiter ;

通常COMMIT或ROLLBACK語句執行時才完成一個事務，但是有些DDL語句等會隐式觸發COMMIT，是以應該在事務中盡可能少用或注意一下：

ALTER FUNCTION 
ALTER PROCEDURE 
ALTER TABLE 
BEGIN 
CREATE DATABASE 
CREATE FUNCTION 
CREATE INDEX 
CREATE PROCEDURE 
CREATE TABLE 
DROP DATABASE 
DROP FUNCTION 
DROP INDEX 
DROP PROCEDURE 
DROP TABLE 
UNLOCK TABLES 
LOAD MASTER DATA 
LOCK TABLES 
RENAME TABLE 
TRUNCATE TABLE 
SET AUTOCOMMIT=1 
START TRANSACTION 
ALTER FUNCTION 
ALTER PROCEDURE 
ALTER TABLE 
BEGIN 
CREATE DATABASE 
CREATE FUNCTION 
CREATE INDEX 
CREATE PROCEDURE 
CREATE TABLE 
DROP DATABASE 
DROP FUNCTION 
DROP INDEX 
DROP PROCEDURE 
DROP TABLE 
UNLOCK TABLES 
LOAD MASTER DATA 
LOCK TABLES 
RENAME TABLE 
TRUNCATE TABLE 
SET AUTOCOMMIT=1 
START TRANSACTION

3，

使用Savepoint

使用savepoint復原難免有些性能消耗，一般可以用IF改寫 savepoint的良好使用的場景之一是“嵌套事務”，你可能希望程式執行一個小的事務，但是不希望復原外面更大的事務：

CREATE PROCEDURE nested_tfer_funds 
(in_from_acct INTEGER, 
in_to_acct INTEGER, 
in_tfer_amount DECIMAL(8,2)) 
BEGIN 
DECLARE txn_error INTEGER DEFAULT 0; 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN 
SET txn_error=1; 
END 
SAVEPINT savepint_tfer; 
UPDATE account_balance 
SET balance=balance-in_tfer_amount 
WHERE account_id=in_from_acct; 
IF txn_error THEN 
ROLLBACK TO savepoint_tfer; 
SELECT 'Transfer aborted'; 
ELSE 
UPDATE account_balance 
SET balance=balance+in_tfer_amount 
WHERE account_id=in_to_acct; 
IF txn_error THEN 
ROLLBACK TO savepoint_tfer; 
SELECT 'Transfer aborted'; 
END IF: 
END IF; 
END; 
CREATE PROCEDURE nested_tfer_funds 
(in_from_acct INTEGER, 
in_to_acct INTEGER, 
in_tfer_amount DECIMAL(8,2)) 
BEGIN 
DECLARE txn_error INTEGER DEFAULT 0; 
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN 
SET txn_error=1; 
END 
SAVEPINT savepint_tfer; 
UPDATE account_balance 
SET balance=balance-in_tfer_amount 
WHERE account_id=in_from_acct; 
IF txn_error THEN 
ROLLBACK TO savepoint_tfer; 
SELECT 'Transfer aborted'; 
ELSE 
UPDATE account_balance 
SET balance=balance+in_tfer_amount 
WHERE account_id=in_to_acct; 
IF txn_error THEN 
ROLLBACK TO savepoint_tfer; 
SELECT 'Transfer aborted'; 
END IF: 
END IF; 
END;

4，

事務和鎖

事務的ACID屬性隻能通過限制資料庫的同步更改來實作，進而通過對修改資料加鎖來實作。直到事務觸發COMMIT或ROLLBACK語句時鎖才釋放。

缺點是後面的事務必須等前面的事務完成才能開始執行，吞吐量随着等待鎖釋放的時間增長而遞減。

MySQL/InnoDB通過行級鎖來最小化鎖競争。這樣修改同一table裡其他行的資料沒有限制，而且讀資料可以始終沒有等待。可以在SELECT語句裡使用FOR UPDATE或LOCK IN SHARE MODE語句來加上行級鎖

SELECT select_statement options [FOR UPDATE|LOCK IN SHARE MODE] 
SELECT select_statement options [FOR UPDATE|LOCK IN SHARE MODE]

FOR UPDATE會鎖住該SELECT語句傳回的行，其他SELECT和DML語句必須等待該SELECT語句所在的事務完成

LOCK IN SHARE MODE同FOR UPDATE，但是允許其他session的SELECT語句執行并允許擷取SHARE MODE鎖

死鎖：

死鎖發生于兩個事務互相等待彼此釋放鎖的情景

當MySQL/InnoDB檢查到死鎖時，它會強制一個事務rollback并觸發一條錯誤消息

對InnoDB而言，所選擇的rollback的事務是完成工作最少的事務（所修改的行最少）

mysql > CALL tfer_funds(1,2,300); 
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死鎖在任何資料庫系統裡都可能發生，但是對MySQL/InnoDB這種行級鎖資料庫而言可能性相對較少。

可以通過使用一緻的順序來鎖row或table以及讓事務保持盡可能短來減少死鎖的頻率。

如果死鎖不容易debug，你可以向你的程式中添加一些邏輯來處理死鎖并重試事務，但這部分代碼多了以後很難維護。是以，比較好的避免死鎖的方式是在做任何修改之前按一定的順序添加行級鎖，這樣就能避免死鎖:

CREATE PROCEDURE tfer_funds3 
(from_account INT, to_account INT, tfer_amount NUMERIC(10,2)) 
BEGIN 
DECLARE local_account_id INT; 
DECLARE lock_cursor CURSOR FOR 
SELECT account_id 
FROM account_balance 
WHERE account_id IN (from_account, to_account) 
ORDER BY account_id 
FOR UPDATE; 
START TRANSACTION; 
OPEN lock_cursor; 
FETCH lock_cursor INTO local_account_id; 
UPDATE account_balance 
SET balance=balance-tfer_amount 
WHERE account_id=from_account; 
UPDATE account_balance 
SET balance=balance+tfer_amount 
WHERE account_id=to_account; 
CLOSE lock_cursor; 
COMMIT; 
END;

設定死鎖ttl: innodb_lock_wait_timeout，預設為50秒

如果你在一個事務中混合使用InnoDB和非InnoDB表，則MySQL不能檢測到死鎖，此時會抛出“lock wait timeuot”1205錯誤

樂觀所和悲觀鎖政策：

悲觀鎖：在讀取資料時鎖住那幾行，其他對這幾行的更新需要等到悲觀鎖結束時才能繼續

樂觀所：讀取資料時不鎖，更新時檢查是否資料已經被更新過，如果是則取消目前更新

一般在悲觀鎖的等待時間過長而不能接受時我們才會選擇樂觀鎖

悲觀鎖的例子：

CREATE PROCEDURE tfer_funds 
(from_account INT, to_account INT,tfer_amount NUMERIC(10,2), 
OUT status INT, OUT message VARCHAR(30)) 
BEGIN 
DECLARE from_account_balance NUMERIC(10,2); 
START TRANSACTION; 
SELECT balance 
INTO from_account_balance 
FROM account_balance 
WHERE account_id=from_account 
FOR UPDATE; 
IF from_account_balance>=tfer_amount THEN 
UPDATE account_balance 
SET balance=balance-tfer_amount 
WHERE account_id=from_account; 
UPDATE account_balance 
SET balance=balance+tfer_amount 
WHERE account_id=to_account; 
COMMIT; 
SET status=0; 
SET message='OK'; 
ELSE 
ROLLBACK; 
SET status=-1; 
SET message='Insufficient funds'; 
END IF; 
END;

樂觀鎖的例子：

CREATE PROCEDURE tfer_funds 
(from_account INT, to_account INT, tfer_amount NUMERIC(10,2), 
OUT status INT, OUT message VARCHAR(30) ) 
BEGIN 
DECLARE from_account_balance NUMERIC(8,2); 
DECLARE from_account_balance2 NUMERIC(8,2); 
DECLARE from_account_timestamp1 TIMESTAMP; 
DECLARE from_account_timestamp2 TIMESTAMP; 
SELECT account_timestamp,balance 
INTO from_account_timestamp1,from_account_balance 
FROM account_balance 
WHERE account_id=from_account; 
IF (from_account_balance>=tfer_amount) THEN 
-- Here we perform some long running validation that 
-- might take a few minutes */ 
CALL long_running_validation(from_account); 
START TRANSACTION; 
-- Make sure the account row has not been updated since 
-- our initial check 
SELECT account_timestamp, balance 
INTO from_account_timestamp2,from_account_balance2 
FROM account_balance 
WHERE account_id=from_account 
FOR UPDATE; 
IF (from_account_timestamp1 <> from_account_timestamp2 OR 
from_account_balance <> from_account_balance2) THEN 
ROLLBACK; 
SET status=-1; 
SET message=CONCAT("Transaction cancelled due to concurrent update", 
" of account" ,from_account); 
ELSE 
UPDATE account_balance 
SET balance=balance-tfer_amount 
WHERE account_id=from_account; 
UPDATE account_balance 
SET balance=balance+tfer_amount 
WHERE account_id=to_account; 
COMMIT; 
SET status=0; 
SET message="OK"; 
END IF; 
ELSE 
ROLLBACK; 
SET status=-1; 
SET message="Insufficient funds"; 
END IF; 
END$$

5，

事務設計指南

1）保持事務短小

2）盡量避免事務中rollback

3）盡量避免savepoint

4）預設情況下，依賴于悲觀鎖

5）為吞吐量要求苛刻的事務考慮樂觀鎖

6）顯示聲明打開事務

7）鎖的行越少越好，鎖的時間越短越好

mysql存儲過程事務管理簡析

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

詳解STM32單片機的堆棧

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述