mysql預存程序之事務篇

標籤：blog   io   ar   使用   sp   java   for   strong   資料   

mysql預存程序之事務篇   

事務的四大特徵：

ACID:Atomic（原子性）、Consistent（一致性）、Isolated（獨立性）、Durable （持久性）

 

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

sql代碼 

1. MyISAM：不支援事務，用於唯讀程式提高效能  

2. InnoDB：支援ACID事務、行級鎖、並發  

3. Berkeley DB：支援事務  

 

交易隔離等級標準：

ANSI（美國國家標準局）標準定義了4個隔離等級，MySQL的InnoDB都支援： 

sql代碼 

1. READ UNCOMMITTED：最低層級的隔離，通常又稱為dirty read，它允許一個事務讀取還沒commit的資料，這樣可能會提高效能，但是dirty read可能不是我們想要的  

2. READ COMMITTED：在一個事務中只允許已經commit的記錄可見，如果session中select還在查詢中，另一session此時insert一條記錄，則新添加的資料不可見  

3. REPEATABLE READ：在一個事務開始後，其他session對資料庫的修改在本事務中不可見，直到本事務commit或rollback。在一個事務中重複select的結果一樣，除非本事務中update資料庫。

4. SERIALIZABLE：最進階別的隔離，只允許事務串列執行。為了達到此目的，資料庫會鎖住每行已經讀取的記錄，其他session不能修改資料直到前一事務結束，事務commit或取消時才釋放鎖。  

Mysql的預設隔離等級是：REPEATABLE READ

READ UNCOMMITTED層級會導致資料完整性的嚴重問題，需要自己控制如何保持資料完整性

SERIALIZABLE會導致效能問題並增加死結的機率

 

Mysql事務動作陳述式：

1.  START TRANSACTION：開始事務，autocommit設為0，如果已經有一個事務在運行，則會觸發一個隱藏的COMMIT  

2.  COMMIT：提交事務，儲存更改，釋放鎖  

3.  ROLLBACK：復原本事務對資料庫的所有更改，然後結束事務，釋放鎖  

4.  SAVEPOINT savepoint_name：建立一個savepoint識別符來ROLLBACK TO SAVEPOINT  

5.  ROLLBACK TO SAVEPOINT savepoint_name：復原到從savepoint_name開始對資料庫的所有更改，這樣就允許復原事務中的一部分，保證更改的一個子集被提交  

6.  SET TRANSACTION：允許設定事務的隔離等級  

7.  LOCK TABLES：允許顯式的鎖住一個或多個table，會隱式的關閉當前開啟的事務，建議在執行LOCK TABLES語句之前顯式的commit或rollback。我們一般所以一般在事務代碼裡不會使用LOCK TABLES  

 

定義事務

MySQL預設的行為是在每條SQL語句執行後執行一個COMMIT語句，從而有效將每條語句獨立為一個事務。 
在複雜的應用情境下這種方式就不能滿足需求了。 
為了開啟事務，允許在COMMIT和ROLLBACK之前多條語句被執行，我們需要做以下兩步： 
1, 設定MySQL的autocommit屬性為0，預設為1 
2，使用START TRANSACTION語句顯式的開啟一個事務

上面已經說了，當使用START TRANSACTION開始一個事物的時候，則SET autocommit=0不會起作用，因為START TRANSACTION會隱式的提交session中所有當前的更改，結束已有的事務，並開啟一個新的事務。

 

 

使用SET AUTOCOMMIT語句的預存程序例子： 

sql代碼 

1. CREATE PROCEDURE tfer_funds  

2.     (from_account int, to_account int, tfer_amount numeric(10,2))  

3. BEGIN  

4.     SET autocommit=0;  

5.   

6.     UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account;  

7.   

8.     UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account;  

9.   

10.    COMMIT;  

11.END;  

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

sql代碼 

1. CREATE PROCEDURE tfer_funds  

2.     (from_account int, to_account int, tfer_amount numeric(10,2))  

3. BEGIN  

4.     START TRANSACTION;  

5.   

6.     UPDATE account_balance SET balance=balance-tfer_amount WHERE account_id=from_account;  

7.   

8.     UPDATE account_balance SET balance=balance+tfer_amount WHERE account_id=to_account;  

9.   

10.    COMMIT;  

11.END;  

 

 

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

Java代碼 

1. ALTER FUNCTION  

2. ALTER PROCEDURE  

3. ALTER TABLE  

4. BEGIN  

5. CREATE DATABASE  

6. CREATE FUNCTION  

7. CREATE INDEX  

8. CREATE PROCEDURE  

9. CREATE TABLE  

10.DROP DATABASE  

11.DROP FUNCTION  

12.DROP INDEX  

13.DROP PROCEDURE  

14.DROP TABLE  

15.UNLOCK TABLES  

16.LOAD MASTER DATA  

17.LOCK TABLES  

18.RENAME TABLE  

19.TRUNCATE TABLE  

20.SET AUTOCOMMIT=1  

21.START TRANSACTION  

 

關於savepoint當前先不做學習

 

 

 

事務和鎖

事務的ACID屬性只能通過限制資料庫的同步更改來實現，通過對資料加鎖來實現。 
直到事務觸發COMMIT或ROLLBACK語句時鎖才釋放。
這樣做的缺點是後面的事務必須等前面的事務完成才能開始執行，輸送量隨著等待鎖釋放的時間增長而遞減。

 

Mysql的innodb通過行級鎖來最小化鎖競爭。這樣修改同一table裡其他行的資料沒有限制，而且讀資料可以始終沒有等待。

 

可以在SELECT語句裡使用FOR UPDATE或LOCK IN SHARE MODE語句來加上行級鎖 

1. 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鎖 

 

 

 

 

下面瞭解一下死結，悲觀鎖，樂觀鎖，但是不深入掌握，當前只掌握概念

 

Sql代碼 

1. 死結發生於兩個事務相互等待彼此釋放鎖的情景  
2. 悲觀鎖：在讀取資料時鎖住那幾行，其他對這幾行的更新需要等到悲觀鎖結束時才能繼續  
3. 樂觀所：讀取資料時不鎖，更新時檢查是否資料已經被更新過，如果是則取消當前更新  

 

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

 

事務設計指南

 

1，保持事務短小  

2，盡量避免事務中rollback  

3，盡量避免savepoint  

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

5，為輸送量要求苛刻的事務考慮樂觀鎖  

6，顯示聲明開啟事務  

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

mysql預存程序之事務篇