MySQL InnoDB之事務與鎖詳解

引題：為何引入事務？

1>.數據完整性

2>.數據安全性

3>.充分利用系統資源，提高系統並發處理的能力

1. 事務的特征

事務具有四個特性：原子性（Atomiocity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability），這四個特性簡稱ACID特性。

1.1原子性

事務是數據庫的邏輯工作單位，事務中包括的所有操作要麼都做，要麼都不做。

1.2 一致性

事務執行的結果必須是使數據庫從一個一致性的狀態變到另外一個一致性狀態。

1.3 隔離性

一個事務的執行不能被其他事務干擾。即一個事務內部的操作及使用的數據對其他

事務是隔離的，並發執行的各個事務之間互相不干擾。

1.4 持久性

一個事務一旦成功提交，對數據庫中數據的修改就是持久性的。接下來其他的其他

操作或故障不應該對其執行結果有任何影響。

2. MySQL的InnoDB引擎中事物與鎖

2.1 SELECT …… LOCK IN SHARE MODE

會話事務中查找的數據，加上一個共享鎖。若會話事務中查找的數據已經被其他會話事務加上獨占鎖的話，共享鎖會等待其結束再加，若等待時間過長就會顯示事務需要的鎖等待超時。

2.2 SELECT ….. FOR UPDATE

會話事務中查找的數據，加上一個讀更新瑣，其他會話事務將無法再加其他鎖，必須等待其結束。

2.3 INSERT、UPDATE、DELETE

會話事務會對DML語句操作的數據加上一個獨占鎖，其他會話的事務都將會等待其釋放獨占鎖。

2.4 gap and next key lock（間隙鎖）

InnoDB引擎會自動給會話事務中的共享鎖、更新瑣以及獨占鎖，需要加到一個區間值域的時候，再加上個間隙鎖（或稱范圍鎖），對不存在的數據也鎖住，防止出現幻寫。

備注：

以上2.1，2.2，2.3，2.4中描述的情況，跟MySQL所設置的事務隔離級別也有關系。

3.四種事務隔離模式

3.1 READ UNCOMMITED

SELECT的時候允許髒讀，即SELECT會讀取其他事務修改而還沒有提交的數據。

3.2 READ COMMITED

SELECT的時候無法重復讀，即同一個事務中兩次執行同樣的查詢語句，若在第一次與第二次查詢之間時間段，其他事務又剛好修改了其查詢的數據且提交了，則兩次讀到的數據不一致。

3.3 REPEATABLE READ

SELECT的時候可以重復讀，即同一個事務中兩次執行同樣的查詢語句，得到的數據始終都是一致的。

3.4 SERIALIZABLE

與可重復讀的唯一區別是，默認把普通的SELECT語句改成SELECT …. LOCK IN SHARE MODE。即為查詢語句涉及到的數據加上共享瑣，阻塞其他事務修改真實數據。

4. 驗證事務與鎖定示例

接下來，我們將以MySQL中的InnoDB引擎，解釋其如何實現ACID特性，不同隔離級別下事務與事務之間的影響。示例表結構：

CREATE TABLE `account ` (

`ID` int(11) NOT NULL AUTO_INCREMENT,

`VACCOUNT_ID` varchar(32) NOT NULL,

`GMT_CREATE` datetime NOT NULL,

PRIMARY KEY (`ID`),

KEY `idx_VACCOUNT_PARAMETER_VACCOUNTID ` (`VACCOUNT_ID`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

然後向表account中寫入10W條創建日期分布合理的帳號數據，以方便測試之用。

tx_isolation：SET GLOBAL tx_isolation='read-uncommitted' ID 事務1 事務1輸出 事務2 事務2輸出 1 START TRANSACTION;       2 SELECT VACCOUNT_ID from account where ID =1001; caimao101510           START TRANSACTION;   3     UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001;   4     SELECT VACCOUNT_ID from account where ID =1001; uncommitted 5 SELECT VACCOUNT_ID from account where ID =1001; uncommitted     6     ROLLBACK;   7 SELECT VACCOUNT_ID from account where ID =1001; caimao101510     8 COMMIT;       tx_isolation：SET GLOBAL tx_isolation='read-committed' ID 事務1 事務1輸出 事務2 事務2輸出 1 START TRANSACTION;       2 SELECT VACCOUNT_ID from account where ID =1001; caimao101510     3     START TRANSACTION;   4     UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001;   5     SELECT VACCOUNT_ID from account where ID =1001; uncommitted 6 SELECT VACCOUNT_ID from account where ID =1001; caimao101510     7     COMMIT;   8 SELECT VACCOUNT_ID from account where ID =1001; uncommitted     9 COMMIT;       tx_isolation：SET GLOBAL tx_isolation='REPEATABLE-READ' ID 事務1 事務1輸出 事務2 事務2輸出 1 START TRANSACTION;       2 SELECT VACCOUNT_ID from account where ID =1001; caimao101510     3     START TRANSACTION;   4     UPDATE account set VACCOUNT_ID='uncommitted' where ID =1001;   5     SELECT VACCOUNT_ID from account where ID =1001; uncommitted 6 SELECT VACCOUNT_ID from account where ID =1001; caimao101510