轉(zhuǎn)載 MySQL存儲過程之事務管理

hnynes

轉(zhuǎn)載 MySQL存儲過程之事務管理

MySQL存儲過程之事務管理

ACID:Atomic、Consistent、Isolated、Durable
存儲程序提供了一個絕佳的機制來定義、封裝和管理事務。

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

MyISAM：不支持事務，用于只讀程序提高性能 InnoDB：支持ACID事務、行級鎖、并發(fā) Berkeley DB：支持事務

隔離級別：
隔離級別決定了一個session中的事務可能對另一個session的影響、并發(fā)session對數(shù)據(jù)庫的操作、一個session中所見數(shù)據(jù)的一致性
ANSI標準定義了4個隔離級別，MySQL的InnoDB都支持：

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

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

SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

MySQL默認的隔離級別是REPEATABLE READ，在設置隔離級別為READ UNCOMMITTED或SERIALIZABLE時要小心，READ UNCOMMITTED會導致數(shù)據(jù)完整性的嚴重問題，而SERIALIZABLE會導致性能問題并增加死鎖的機率

事務管理語句：

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

2，定義事務
MySQL默認的行為是在每條SQL語句執(zhí)行后執(zhí)行一個COMMIT語句，從而有效的將每條語句獨立為一個事務。
在復雜的應用場景下這種方式就不能滿足需求了。
為了打開事務，允許在COMMIT和ROLLBACK之前多條語句被執(zhí)行，我們需要做以下兩步：
1, 設置MySQL的autocommit屬性為0，默認為1
2，使用START TRANSACTION語句顯式的打開一個事務

如果已經(jīng)打開一個事務，則SET autocommit=0不會起作用，因為START TRANSACTION會隱式的提交session中所有當前的更改，結束已有的事務，并打開一個新的事務。

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

CREATE PROCEDURE tfer_funds     (from_account int, to_account int, tfer_amount numeric(10,2)) BEGIN     SET autocommit=0;        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; END;

使用START TRANSACITON打開事務的例子：

CREATE PROCEDURE tfer_funds     (from_account int, to_account int, tfer_amount numeric(10,2)) BEGIN     START TRANSACTION;        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; END;

通常COMMIT或ROLLBACK語句執(zhí)行時才完成一個事務，但是有些DDL語句等會隱式觸發(fā)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

3，使用Savepoint
使用savepoint回滾難免有些性能消耗，一般可以用IF改寫
savepoint的良好使用的場景之一是“嵌套事務”，你可能希望程序執(zhí)行一個小的事務，但是不希望回滾外面更大的事務：

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屬性只能通過限制數(shù)據(jù)庫的同步更改來實現(xiàn)，從而通過對修改數(shù)據(jù)加鎖來實現(xiàn)。
直到事務觸發(fā)COMMIT或ROLLBACK語句時鎖才釋放。
缺點是后面的事務必須等前面的事務完成才能開始執(zhí)行，吞吐量隨著等待鎖釋放的時間增長而遞減。
MySQL/InnoDB通過行級鎖來最小化鎖競爭。這樣修改同一table里其他行的數(shù)據(jù)沒有限制，而且讀數(shù)據(jù)可以始終沒有等待。
可以在SELECT語句里使用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同F(xiàn)OR UPDATE，但是允許其他session的SELECT語句執(zhí)行并允許獲取SHARE MODE鎖

死鎖：
死鎖發(fā)生于兩個事務相互等待彼此釋放鎖的情景
當MySQL/InnoDB檢查到死鎖時，它會強制一個事務rollback并觸發(fā)一條錯誤消息
對InnoDB而言，所選擇的rollback的事務是完成工作最少的事務（所修改的行最少）

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

死鎖在任何數(shù)據(jù)庫系統(tǒng)里都可能發(fā)生，但是對MySQL/InnoDB這種行級鎖數(shù)據(jù)庫而言可能性相對較少。
可以通過使用一致的順序來鎖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不能檢測到死鎖，此時會拋出“l(fā)ock wait timeuot”1205錯誤

樂觀所和悲觀鎖策略：
悲觀鎖：在讀取數(shù)據(jù)時鎖住那幾行，其他對這幾行的更新需要等到悲觀鎖結束時才能繼續(xù)
樂觀所：讀取數(shù)據(jù)時不鎖，更新時檢查是否數(shù)據(jù)已經(jīng)被更新過，如果是則取消當前更新
一般在悲觀鎖的等待時間過長而不能接受時我們才會選擇樂觀鎖
悲觀鎖的例子：

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$$

事務設計指南:

1，保持事務短小 2，盡量避免事務中rollback 3，盡量避免savepoint 4，默認情況下，依賴于悲觀鎖 5，為吞吐量要求苛刻的事務考慮樂觀鎖 6，顯示聲明打開事務 7，鎖的行越少越好，鎖的時間越短越好