輕松獲知數據庫事務

標題索引

事務作用
事務流程
隔離級別
實例驗證

事務作用

? ? 事務是確保數據庫系統數據的完整性的功能，如現在互聯網行業支付業務，不管服務器出于什么原因異常中斷，客戶要么支付成功要么支付不成功，支付成功數據庫金額即會發生變化，支付不成功客戶的金額就不發生變化，確保了交易業務的穩定性。支持事物的引擎必須滿足ACID，滿足ACID后才能滿足事物，另外事物的回滾或恢復主要靠事物日志來完成，ACID含義分別如下：

????A:atomicity(原子性)：整個事物中所有的操作為命令執行最小單元，全部執行、執行一半失敗回滾或失敗回滾；

????C:consistency(一致性)：數據庫從一個狀態轉化為另外一個狀態，狀態在轉化前和轉換后一致；

????I:isolation(隔離性)：一個事物所做出的操作在提交之前，是不能被其他所見，因此隔離就出現多種隔離級別，具體包括read-uncommitted讀為提交、read-committed讀提交、repeatable-read可重復讀和serializable串行化；

????D:durability(持久性)：一旦事物提交，所做的會永久性保存數據庫中。

事務流程

????事務的工作流程具體可見下圖

???? 事物1.png

圖1-1?事物工作流程

????由上圖可知，當數據庫通過start transaction啟動一個事物，啟動事物后對數據庫進行一系列的操作，最后提交事物，提交事物又有兩種，第一種為commit提交，第二種rollback回滾，一旦提交事物數據庫即處于新的狀態保持持久性。另外在防止數據庫在事物提交后數據從內存寫入磁盤時，操作系統異常掉電導致無法保存，而啟用日志功能，只要啟用事物日志功能，事物先在磁盤連續空間寫寫日志，然后通過內存同步到磁盤，確保萬一內存同步磁盤時機器異常掉電，通過事物日志進行恢復數據庫數據。

隔離級別

? ? 隔離級別(INNODB默認隔離級別為repeatable read)：

????READ UNCOMMITTED(讀未提交)：此種隔離級別帶來問題有臟讀和不可重復讀。

????READ COMMITTED(讀提交)：此種隔離級別解決了臟讀，但仍然有不可重復度。

????REPEATABLE READ(可重讀)：此種隔離級別解決了臟讀和不可重復讀，帶來問題幻讀。

? ? SERIALIZABLE(可串行化)：此種隔離級別解決了臟讀、不可重復度和幻讀，但帶來的問題是加鎖讀。

? ? 問題解釋：

????臟讀：當用戶A修改數據但未提交，此時B用戶讀A修改后的數據，但是A用戶將數據進行rollback回滾，因此B用戶看到的是錯誤的數據；

? ? 不可重復讀：如用戶A啟動一個事務設置某一值設為ON，經查詢已經為ON狀態，但B用戶修改數值為OFF并提交，此時用戶A再次查詢時發現值又為OFF，或者數據庫中又多了一條語句，表現為在同一事務中每次查詢數據庫總是不一致；

????幻讀：當用戶A用戶在同一事務中看到某一值為ON，用戶B已經將值修改為OFF，并且已經提交，用戶B看到的值為OFF，但用戶A在此事務中一直看到的為ON，底層數據確實被修改為OFF，因此就體現了幻讀，除非提交后再次生成一個事務查看值才為OFF；

????加鎖讀：讀數據時加鎖，此時別人無法再讀。

實例驗證

????根據理論概述，進行驗證事物的工作流程和隔離級別，確保透徹了解事物的原理，具體操作如下

MariaDB?[(none)]>?show?processlist;??????????????????????????????????????#查看數據庫的進程列表，顯示有兩終端連接
+----+------+-----------+------+---------+------+-------+------------------+----------+
|?Id?|?User?|?Host??????|?db???|?Command?|?Time?|?State?|?Info?????????????|?Progress?|
+----+------+-----------+------+---------+------+-------+------------------+----------+
|??2?|?root?|?localhost?|?NULL?|?Sleep???|???23?|???????|?NULL?????????????|????0.000?|
|?10?|?root?|?localhost?|?NULL?|?Query???|????0?|?NULL??|?show?processlist?|????0.000?|
+----+------+-----------+------+---------+------+-------+------------------+----------+
2?rows?in?set?(0.00?sec)
MariaDB?[(none)]>?show?global?variables?like?'tx_isolation';???????????#驗證事物的隔離級別
+---------------+-----------------+
|?Variable_name?|?Value???????????|
+---------------+-----------------+
|?tx_isolation??|?REPEATABLE-READ?|
+---------------+-----------------+
1?row?in?set?(0.00?sec)

????驗證隔離級別?READ UNCOMMITTED（存在臟讀、不可重復讀）

????第一步：創建表并插入數據

MariaDB?[test]>?create?table?employee(id?int,name?varchar(20),age?char(3));?????????????????????????????#創建表
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[test]>?insert?into?employee??values(1,'tangseng',38),(2,'sunwukong',505),(3,'zhubajie',485),(4,'shaheshang',408);??????#給表中添加用戶
Query?OK,?4?rows?affected?(0.01?sec)
Records:?4??Duplicates:?0??Warnings:?0

?????第二步：在兩個數據庫連接線程的會話變量中設置隔離級別為READ-UNCOMMITTED

會話1
MariaDB?[(none)]>?set?tx_isolation='READ-UNCOMMITTED';
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[test]>?start?transaction;?????????????????#開啟事物
Query?OK,?0?rows?affected?(0.00?sec)

會話2
MariaDB?[(none)]>?set?tx_isolation='READ-UNCOMMITTED';
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[(none)]>
MariaDB?[test]>?start?transaction;?????????????????#開啟事物
Query?OK,?0?rows?affected?(0.00?sec)

? ? 第三步：兩邊同時啟用事物,其中會話1添加bailongma，但不提交，在會話2上查看驗證

會話1?????????????????????#插入數據但未提交，會話2上查詢后驗證
MariaDB?[test]>?insert?into?employee?values(5,'bailongma',300);
Query?OK,?1?row?affected?(0.00?sec)
會話2?????????????????????#經查詢驗證會話1尚未提交已經可以讀取，若會話1回滾，會話2讀取數據為臟數據
MariaDB?[test]>?select?*?from?employee;
+------+------------+------+
|?id???|?name???????|?age??|
+------+------------+------+
|????1?|?tangseng???|?38???|
|????2?|?sunwukong??|?505??|
|????3?|?zhubajie???|?485??|
|????4?|?shaheshang?|?408??|
|????5?|?bailongma??|?300??|
+------+------------+------+
5?rows?in?set?(0.00?sec)

? ? 驗證隔離級別READ-COMMITTED（解決臟讀問題，存在不可重復讀）

????第一步：創建表并插入數據

MariaDB?[test]>?create?table?employee(id?int,name?varchar(20),age?char(3));?????????????????????????????#創建表
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[test]>?insert?into?employee??values(1,'tangseng',38),(2,'sunwukong',505),(3,'zhubajie',485),(4,'shaheshang',408);??????#給表中添加用戶
Query?OK,?4?rows?affected?(0.01?sec)
Records:?4??Duplicates:?0??Warnings:?0

?????第二步：在兩個數據庫連接線程的會話變量中設置隔離級別為READ-COMMITTED

會話1
MariaDB?[(none)]>?set?tx_isolation='READ-COMMITTED';
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[test]>?start?transaction;??????????????#開啟事務
Query?OK,?0?rows?affected?(0.00?sec)

會話2
MariaDB?[(none)]>?set?tx_isolation='READ-COMMITTED';
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[test]>?start?transaction;??????????????#開啟事務
Query?OK,?0?rows?affected?(0.00?sec)

????第三步：在兩個連接數據庫的線程進程添加bailongma，但不提交進行驗證

會話1
MariaDB?[test]>?insert?into?employee?values(5,'bailongma',305);
Query?OK,?1?row?affected?(0.00?sec)
會話2??????????????#在會話1未提交時，會話2是無法讀取數據
MariaDB?[test]>?select?*?from?employee;
+------+------------+------+
|?id???|?name???????|?age??|
+------+------------+------+
|????1?|?tangseng???|?38???|
|????2?|?sunwukong??|?505??|
|????3?|?zhubajie???|?485??|
|????4?|?shaheshang?|?408??|
+------+------------+------+
4?rows?in?set?(0.00?sec)

????第四步：在連會話1上進行提交，然后在會話2上進行驗證

會話1
MariaDB?[test]>?commit;
Query?OK,?0?rows?affected?(0.00?sec)
會話2?????????????????
MariaDB?[test]>?select?*?from?employee;
+------+------------+------+
|?id???|?name???????|?age??|
+------+------------+------+
|????1?|?tangseng???|?38???|
|????2?|?sunwukong??|?505??|
|????3?|?zhubajie???|?485??|
|????4?|?shaheshang?|?408??|
|????5?|?bailongma??|?305??|
+------+------------+------+
5?rows?in?set?(0.00?sec)????#說明讀提交可以解決臟讀的問題

????驗隔離級別REPEATABLE READ(解決臟讀和重復讀的問題，帶來新的問題幻讀)

????第一步：創建表并插入數據

MariaDB?[test]>?create?table?employee(id?int,name?varchar(20),age?char(3));?????????????????????????????#創建表
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[test]>?insert?into?employee??values(1,'tangseng',38),(2,'sunwukong',505),(3,'zhubajie',485),(4,'shaheshang',408);??????#給表中添加用戶
Query?OK,?4?rows?affected?(0.01?sec)
Records:?4??Duplicates:?0??Warnings:?0

????第二步：在兩個數據庫連接線程的會話變量中設置隔離級別為REPEATABLE-READ

會話1
MariaDB?[(none)]>?set?tx_isolation='REPEATABLE-READ';
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[test]>?start?transaction;??????????????#開啟事務
Query?OK,?0?rows?affected?(0.00?sec)

會話2
MariaDB?[(none)]>?set?tx_isolation='REPEATABLE-READ';
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[test]>?start?transaction;??????????????#開啟事務
Query?OK,?0?rows?affected?(0.00?sec)

? ? 第三步：先會話2中開啟一個事物查詢表中數據，然后在會話1中添加bailongma用戶，再次在會話2中的同一事務中查看表中數據（發現會話1中數據已經發生變化，會話2的同一事物中任然是之前的數據，因此解決了事物的可重復讀）

會話2???????????#開啟一個事物并查詢表中數據
MariaDB?[test]>?select?*?from?employee;
+------+------------+------+
|?id???|?name???????|?age??|
+------+------------+------+
|????1?|?tangseng???|?38???|
|????2?|?sunwukong??|?505??|
|????3?|?zhubajie???|?485??|
|????4?|?shaheshang?|?408??|
+------+------------+------+
4?rows?in?set?(0.00?sec)
會話1????????#添加bailongma數據后，提交并查詢
MariaDB?[test]>?insert?into?employee?values(5,'bailongma',305);
Query?OK,?1?row?affected?(0.00?sec)MariaDB?[test]>?commit;
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[test]>?select?*?from?employee;
+------+------------+------+
|?id???|?name???????|?age??|
+------+------------+------+
|????1?|?tangseng???|?38???|
|????2?|?sunwukong??|?505??|
|????3?|?zhubajie???|?485??|
|????4?|?shaheshang?|?408??|
|????5?|?bailongma??|?305??|
+------+------------+------+
5?rows?in?set?(0.00?sec)
會話2??????#在會話2上再次查詢，結果任然是4條數據，原因是會話2上的事物并未提交，并且解決了可重復讀，因此只能看到4條，除非提交事物后再次查詢；
MariaDB?[test]>?select?*?from?employee;
+------+------------+------+
|?id???|?name???????|?age??|
+------+------------+------+
|????1?|?tangseng???|?38???|
|????2?|?sunwukong??|?505??|
|????3?|?zhubajie???|?485??|
|????4?|?shaheshang?|?408??|
+------+------------+------+
4?rows?in?set?(0.00?sec)
MariaDB?[test]>?select?*?from?employee;
+------+------------+------+
|?id???|?name???????|?age??|
+------+------------+------+
|????1?|?tangseng???|?38???|
|????2?|?sunwukong??|?505??|
|????3?|?zhubajie???|?485??|
|????4?|?shaheshang?|?408??|
|????5?|?bailongma??|?305??|
+------+------------+------+
5?rows?in?set?(0.00?sec)

????驗隔離級別SERIALIZABLE(解決重復讀的問題，需注意每次操作都需要重啟新的事物和提交，因為有加鎖，一個事物只能是一組語句)

?????第一步：創建表并插入數據

MariaDB?[test]>?create?table?employee(id?int,name?varchar(20),age?char(3));?????????????????????????????#創建表
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[test]>?insert?into?employee??values(1,'tangseng',38),(2,'sunwukong',505),(3,'zhubajie',485),(4,'shaheshang',408);??????#給表中添加用戶
Query?OK,?4?rows?affected?(0.01?sec)
Records:?4??Duplicates:?0??Warnings:?0

????第二步：在兩個數據庫連接線程的會話變量中設置隔離級別為SERIALIZABLE

會話1
MariaDB?[(none)]>?set?tx_isolation='SERIALIZABLE';
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[test]>?start?transaction;??????????????#開啟事物
Query?OK,?0?rows?affected?(0.00?sec)

會話2
MariaDB?[(none)]>?set?tx_isolation='SERIALIZABLE';
Query?OK,?0?rows?affected?(0.00?sec)
MariaDB?[test]>?start?transaction;??????????????#開啟事物
Query?OK,?0?rows?affected?(0.00?sec)

????第三步：當會話1上進行插入bailongma用戶前，在會話2上進查詢并提交，會話1添加bailongma并提交，然后再次在會話2上進行查詢

會話1???????#添加用戶后，并未提交
MariaDB?[test]>?insert?into?employee?values(5,'bailongma',305);
Query?OK,?1?row?affected?(0.00?sec)
會話2???????#在會話1上未提交時，會話1對表進行加鎖，因此會話2上時無法查詢，因此解決幻讀
MariaDB?[test]>?select?*?from?employee;
ERROR?1205?(HY000):?Lock?wait?timeout?exceeded;?try?restarting?transaction

轉載于:https://blog.51cto.com/weiboxue/2045010