mysql存儲(chǔ)過程事務(wù)管理簡(jiǎn)析

更新時(shí)間：2012年11月24日 19:32:14 作者：

本文將提供了一個(gè)絕佳的機(jī)制來(lái)定義、封裝和管理事務(wù)，需要的朋友可以參考下

ACID:Atomic、Consistent、Isolated、Durable
存儲(chǔ)程序提供了一個(gè)絕佳的機(jī)制來(lái)定義、封裝和管理事務(wù)。
1，MySQL的事務(wù)支持
1）MySQL的事務(wù)支持不是綁定在MySQL服務(wù)器本身，而是與存儲(chǔ)引擎相關(guān)：
Sql代碼

復(fù)制代碼代碼如下:

 
MyISAM：不支持事務(wù)，用于只讀程序提高性能 
InnoDB：支持ACID事務(wù)、行級(jí)鎖、并發(fā) 
Berkeley DB：支持事務(wù) 
MyISAM：不支持事務(wù)，用于只讀程序提高性能 
InnoDB：支持ACID事務(wù)、行級(jí)鎖、并發(fā) 
Berkeley DB：支持事務(wù) 2） 隔離級(jí)別： 

隔離級(jí)別決定了一個(gè)session中的事務(wù)可能對(duì)另一個(gè)session的影響、并發(fā)session對(duì)數(shù)據(jù)庫(kù)的操作、一個(gè)session中所見數(shù)據(jù)的一致性
ANSI標(biāo)準(zhǔn)定義了4個(gè)隔離級(jí)別，MySQL的InnoDB都支持：
Java代碼

復(fù)制代碼代碼如下:

 
READ UNCOMMITTED：最低級(jí)別的隔離，通常又稱為dirty read，它允許一個(gè)事務(wù)讀取還沒commit的數(shù)據(jù)，這樣可能會(huì)提高性能，但是dirty read可能不是我們想要的 
READ COMMITTED：在一個(gè)事務(wù)中只允許已經(jīng)commit的記錄可見，如果session中select還在查詢中，另一session此時(shí)insert一條記錄，則新添加的數(shù)據(jù)不可見 
REPEATABLE READ：在一個(gè)事務(wù)開始后，其他session對(duì)數(shù)據(jù)庫(kù)的修改在本事務(wù)中不可見，直到本事務(wù)commit或rollback。在一個(gè)事務(wù)中重復(fù)select的結(jié)果一樣，除非本事務(wù)中update數(shù)據(jù)庫(kù)。 
SERIALIZABLE：最高級(jí)別的隔離，只允許事務(wù)串行執(zhí)行。為了達(dá)到此目的，數(shù)據(jù)庫(kù)會(huì)鎖住每行已經(jīng)讀取的記錄，其他session不能修改數(shù)據(jù)直到前一事務(wù)結(jié)束，事務(wù)commit或取消時(shí)才釋放鎖。 
READ UNCOMMITTED：最低級(jí)別的隔離，通常又稱為dirty read，它允許一個(gè)事務(wù)讀取還沒commit的數(shù)據(jù)，這樣可能會(huì)提高性能，但是dirty read可能不是我們想要的 
READ COMMITTED：在一個(gè)事務(wù)中只允許已經(jīng)commit的記錄可見，如果session中select還在查詢中，另一session此時(shí)insert一條記錄，則新添加的數(shù)據(jù)不可見 
REPEATABLE READ：在一個(gè)事務(wù)開始后，其他session對(duì)數(shù)據(jù)庫(kù)的修改在本事務(wù)中不可見，直到本事務(wù)commit或rollback。在一個(gè)事務(wù)中重復(fù)select的結(jié)果一樣，除非本事務(wù)中update數(shù)據(jù)庫(kù)。 
SERIALIZABLE：最高級(jí)別的隔離，只允許事務(wù)串行執(zhí)行。為了達(dá)到此目的，數(shù)據(jù)庫(kù)會(huì)鎖住每行已經(jīng)讀取的記錄，其他session不能修改數(shù)據(jù)直到前一事務(wù)結(jié)束，事務(wù)commit或取消時(shí)才釋放鎖。 可以使用如下語(yǔ)句設(shè)置MySQL的session隔離級(jí)別： 

Sql代碼

復(fù)制代碼代碼如下:

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

MySQL默認(rèn)的隔離級(jí)別是REPEATABLE READ，在設(shè)置隔離級(jí)別為READ UNCOMMITTED或SERIALIZABLE時(shí)要小心，READ UNCOMMITTED會(huì)導(dǎo)致數(shù)據(jù)完整性的嚴(yán)重問題，而SERIALIZABLE會(huì)導(dǎo)致性能問題并增加死鎖的機(jī)率
3)事務(wù)管理語(yǔ)句：
Sql代碼

復(fù)制代碼代碼如下:

 
START TRANSACTION：開始事務(wù)，autocommit設(shè)為0，如果已經(jīng)有一個(gè)事務(wù)在運(yùn)行，則會(huì)觸發(fā)一個(gè)隱藏的COMMIT 
COMMIT：提交事務(wù)，保存更改，釋放鎖 
ROLLBACK：回滾本事務(wù)對(duì)數(shù)據(jù)庫(kù)的所有更改，然后結(jié)束事務(wù)，釋放鎖 
SAVEPOINT savepoint_name：創(chuàng)建一個(gè)savepoint識(shí)別符來(lái)ROLLBACK TO SAVEPOINT 
ROLLBACK TO SAVEPOINT savepoint_name：回滾到從savepoint_name開始對(duì)數(shù)據(jù)庫(kù)的所有更改，這樣就允許回滾事務(wù)中的一部分，保證更改的一個(gè)子集被提交 
SET TRANSACTION：允許設(shè)置事務(wù)的隔離級(jí)別 
LOCK TABLES：允許顯式的鎖住一個(gè)或多個(gè)table，會(huì)隱式的關(guān)閉當(dāng)前打開的事務(wù)，建議在執(zhí)行LOCK TABLES語(yǔ)句之前顯式的commit或rollback。我們一般所以一般在事務(wù)代碼里不會(huì)使用LOCK TABLES 
START TRANSACTION：開始事務(wù)，autocommit設(shè)為0，如果已經(jīng)有一個(gè)事務(wù)在運(yùn)行，則會(huì)觸發(fā)一個(gè)隱藏的COMMIT 
COMMIT：提交事務(wù)，保存更改，釋放鎖 
ROLLBACK：回滾本事務(wù)對(duì)數(shù)據(jù)庫(kù)的所有更改，然后結(jié)束事務(wù)，釋放鎖 
SAVEPOINT savepoint_name：創(chuàng)建一個(gè)savepoint識(shí)別符來(lái)ROLLBACK TO SAVEPOINT 
ROLLBACK TO SAVEPOINT savepoint_name：回滾到從savepoint_name開始對(duì)數(shù)據(jù)庫(kù)的所有更改，這樣就允許回滾事務(wù)中的一部分，保證更改的一個(gè)子集被提交 
SET TRANSACTION：允許設(shè)置事務(wù)的隔離級(jí)別 
LOCK TABLES：允許顯式的鎖住一個(gè)或多個(gè)table，會(huì)隱式的關(guān)閉當(dāng)前打開的事務(wù)，建議在執(zhí)行LOCK TABLES語(yǔ)句之前顯式的commit或rollback。我們一般所以一般在事務(wù)代碼里不會(huì)使用LOCK TABLES 

2，定義事務(wù)
MySQL默認(rèn)的行為是在每條SQL語(yǔ)句執(zhí)行后執(zhí)行一個(gè)COMMIT語(yǔ)句，從而有效的將每條語(yǔ)句獨(dú)立為一個(gè)事務(wù)。
在復(fù)雜的應(yīng)用場(chǎng)景下這種方式就不能滿足需求了。
為了打開事務(wù)，允許在COMMIT和ROLLBACK之前多條語(yǔ)句被執(zhí)行，我們需要做以下兩步(也可以人為是兩種方式)：
1, 設(shè)置MySQL的autocommit屬性為0，默認(rèn)為1
2，使用START TRANSACTION語(yǔ)句顯式的打開一個(gè)事務(wù)（然后autocommit屬性會(huì)自動(dòng)被設(shè)置為0）
如果已經(jīng)打開一個(gè)事務(wù)，則SET autocommit=0不會(huì)起作用，因?yàn)镾TART TRANSACTION會(huì)隱式的提交session中所有當(dāng)前的更改，結(jié)束已有的事務(wù)，并打開一個(gè)新的事務(wù)。
使用SET AUTOCOMMIT語(yǔ)句的存儲(chǔ)過程例子:
Sql代碼

復(fù)制代碼代碼如下:

 
delimiter $$ 
use test$$ 
create procedure t_insert_table() 
begin 
/** 標(biāo)記是否出錯(cuò) */ 
declare t_error int default 0; 
/** 如果出現(xiàn)sql異常，則將t_error設(shè)置為1后繼續(xù)執(zhí)行后面的操作 */ 
declare continue handler for sqlexception set t_error=1; -- 出錯(cuò)處理 
/** 顯示的開啟事務(wù)，啟動(dòng)它后，autocommit值會(huì)自動(dòng)設(shè)置為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); 
/** 標(biāo)記被改變,表示事務(wù)應(yīng)該回滾 */ 
if t_error=1 then 
rollback; -- 事務(wù)回滾 
else 
commit; -- 事務(wù)提交 
end if; 
end$$ 
delimiter ; 
delimiter $$ 
use test$$ 
create procedure t_insert_table() 
begin 
/** 標(biāo)記是否出錯(cuò) */ 
declare t_error int default 0; 
/** 如果出現(xiàn)sql異常，則將t_error設(shè)置為1后繼續(xù)執(zhí)行后面的操作 */ 
declare continue handler for sqlexception set t_error=1; -- 出錯(cuò)處理 
/** 顯示的開啟事務(wù)，啟動(dòng)它后，autocommit值會(huì)自動(dòng)設(shè)置為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); 
/** 標(biāo)記被改變,表示事務(wù)應(yīng)該回滾 */ 
if t_error=1 then 
rollback; -- 事務(wù)回滾 
else 
commit; -- 事務(wù)提交 
end if; 
end$$ 
delimiter ; 

通常COMMIT或ROLLBACK語(yǔ)句執(zhí)行時(shí)才完成一個(gè)事務(wù)，但是有些DDL語(yǔ)句等會(huì)隱式觸發(fā)COMMIT，所以應(yīng)該在事務(wù)中盡可能少用或注意一下：
Sql代碼

復(fù)制代碼代碼如下:

 
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的良好使用的場(chǎng)景之一是“嵌套事務(wù)”，你可能希望程序執(zhí)行一個(gè)小的事務(wù)，但是不希望回滾外面更大的事務(wù)：
Sql代碼

復(fù)制代碼代碼如下:

 
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，事務(wù)和鎖
事務(wù)的ACID屬性只能通過限制數(shù)據(jù)庫(kù)的同步更改來(lái)實(shí)現(xiàn)，從而通過對(duì)修改數(shù)據(jù)加鎖來(lái)實(shí)現(xiàn)。
直到事務(wù)觸發(fā)COMMIT或ROLLBACK語(yǔ)句時(shí)鎖才釋放。
缺點(diǎn)是后面的事務(wù)必須等前面的事務(wù)完成才能開始執(zhí)行，吞吐量隨著等待鎖釋放的時(shí)間增長(zhǎng)而遞減。
MySQL/InnoDB通過行級(jí)鎖來(lái)最小化鎖競(jìng)爭(zhēng)。這樣修改同一table里其他行的數(shù)據(jù)沒有限制，而且讀數(shù)據(jù)可以始終沒有等待。
可以在SELECT語(yǔ)句里使用FOR UPDATE或LOCK IN SHARE MODE語(yǔ)句來(lái)加上行級(jí)鎖
Sql代碼

復(fù)制代碼代碼如下:

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

FOR UPDATE會(huì)鎖住該SELECT語(yǔ)句返回的行，其他SELECT和DML語(yǔ)句必須等待該SELECT語(yǔ)句所在的事務(wù)完成
LOCK IN SHARE MODE同F(xiàn)OR UPDATE，但是允許其他session的SELECT語(yǔ)句執(zhí)行并允許獲取SHARE MODE鎖
死鎖：
死鎖發(fā)生于兩個(gè)事務(wù)相互等待彼此釋放鎖的情景
當(dāng)MySQL/InnoDB檢查到死鎖時(shí)，它會(huì)強(qiáng)制一個(gè)事務(wù)rollback并觸發(fā)一條錯(cuò)誤消息
對(duì)InnoDB而言，所選擇的rollback的事務(wù)是完成工作最少的事務(wù)（所修改的行最少）
Java代碼

復(fù)制代碼代碼如下:

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

死鎖在任何數(shù)據(jù)庫(kù)系統(tǒng)里都可能發(fā)生，但是對(duì)MySQL/InnoDB這種行級(jí)鎖數(shù)據(jù)庫(kù)而言可能性相對(duì)較少。
可以通過使用一致的順序來(lái)鎖row或table以及讓事務(wù)保持盡可能短來(lái)減少死鎖的頻率。
如果死鎖不容易debug，你可以向你的程序中添加一些邏輯來(lái)處理死鎖并重試事務(wù)，但這部分代碼多了以后很難維護(hù)
所以，比較好的避免死鎖的方式是在做任何修改之前按一定的順序添加行級(jí)鎖，這樣就能避免死鎖:
Java代碼

復(fù)制代碼代碼如下:

 
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; 

設(shè)置死鎖ttl: innodb_lock_wait_timeout，默認(rèn)為50秒
如果你在一個(gè)事務(wù)中混合使用InnoDB和非InnoDB表，則MySQL不能檢測(cè)到死鎖，此時(shí)會(huì)拋出“l(fā)ock wait timeuot”1205錯(cuò)誤
樂觀所和悲觀鎖策略：
悲觀鎖：在讀取數(shù)據(jù)時(shí)鎖住那幾行，其他對(duì)這幾行的更新需要等到悲觀鎖結(jié)束時(shí)才能繼續(xù)
樂觀所：讀取數(shù)據(jù)時(shí)不鎖，更新時(shí)檢查是否數(shù)據(jù)已經(jīng)被更新過，如果是則取消當(dāng)前更新
一般在悲觀鎖的等待時(shí)間過長(zhǎng)而不能接受時(shí)我們才會(huì)選擇樂觀鎖
悲觀鎖的例子：
Java代碼

復(fù)制代碼代碼如下:

 
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; 

樂觀鎖的例子：
Java代碼

復(fù)制代碼代碼如下:

 
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，事務(wù)設(shè)計(jì)指南
1，保持事務(wù)短小
2，盡量避免事務(wù)中rollback
3，盡量避免savepoint
4，默認(rèn)情況下，依賴于悲觀鎖
5，為吞吐量要求苛刻的事務(wù)考慮樂觀鎖
6，顯示聲明打開事務(wù)
7，鎖的行越少越好，鎖的時(shí)間越短越好

您可能感興趣的文章: