輕松獲知數(shù)據(jù)庫(kù)事務(wù)

標(biāo)題索引

• 事務(wù)作用

• 事務(wù)流程

• 隔離級(jí)別

• 實(shí)例驗(yàn)證

事務(wù)作用

    事務(wù)是確保數(shù)據(jù)庫(kù)系統(tǒng)數(shù)據(jù)的完整性的功能，如現(xiàn)在互聯(lián)網(wǎng)行業(yè)支付業(yè)務(wù)，不管服務(wù)器出于什么原因異常中斷，客戶要么支付成功要么支付不成功，支付成功數(shù)據(jù)庫(kù)金額即會(huì)發(fā)生變化，支付不成功客戶的金額就不發(fā)生變化，確保了交易業(yè)務(wù)的穩(wěn)定性。支持事物的引擎必須滿足ACID，滿足ACID后才能滿足事物，另外事物的回滾或恢復(fù)主要靠事物日志來(lái)完成，ACID含義分別如下：

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

    C:consistency(一致性)：數(shù)據(jù)庫(kù)從一個(gè)狀態(tài)轉(zhuǎn)化為另外一個(gè)狀態(tài)，狀態(tài)在轉(zhuǎn)化前和轉(zhuǎn)換后一致；

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

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

事務(wù)流程

    事務(wù)的工作流程具體可見下圖

圖1-1 事物工作流程

    由上圖可知，當(dāng)數(shù)據(jù)庫(kù)通過(guò)start transaction啟動(dòng)一個(gè)事物，啟動(dòng)事物后對(duì)數(shù)據(jù)庫(kù)進(jìn)行一系列的操作，最后提交事物，提交事物又有兩種，第一種為commit提交，第二種rollback回滾，一旦提交事物數(shù)據(jù)庫(kù)即處于新的狀態(tài)保持持久性。另外在防止數(shù)據(jù)庫(kù)在事物提交后數(shù)據(jù)從內(nèi)存寫入磁盤時(shí)，操作系統(tǒng)異常掉電導(dǎo)致無(wú)法保存，而啟用日志功能，只要啟用事物日志功能，事物先在磁盤連續(xù)空間寫寫日志，然后通過(guò)內(nèi)存同步到磁盤，確保萬(wàn)一內(nèi)存同步磁盤時(shí)機(jī)器異常掉電，通過(guò)事物日志進(jìn)行恢復(fù)數(shù)據(jù)庫(kù)數(shù)據(jù)。

隔離級(jí)別

    隔離級(jí)別(INNODB默認(rèn)隔離級(jí)別為repeatable read)：

    READ UNCOMMITTED(讀未提交)：此種隔離級(jí)別帶來(lái)問(wèn)題有臟讀和不可重復(fù)讀。

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

    REPEATABLE READ(可重讀)：此種隔離級(jí)別解決了臟讀和不可重復(fù)讀，帶來(lái)問(wèn)題幻讀。

    SERIALIZABLE(可串行化)：此種隔離級(jí)別解決了臟讀、不可重復(fù)度和幻讀，但帶來(lái)的問(wèn)題是加鎖讀。

    問(wèn)題解釋：

    臟讀：當(dāng)用戶A修改數(shù)據(jù)但未提交，此時(shí)B用戶讀A修改后的數(shù)據(jù)，但是A用戶將數(shù)據(jù)進(jìn)行rollback回滾，因此B用戶看到的是錯(cuò)誤的數(shù)據(jù)；

    不可重復(fù)讀：如用戶A啟動(dòng)一個(gè)事務(wù)設(shè)置某一值設(shè)為ON，經(jīng)查詢已經(jīng)為ON狀態(tài)，但B用戶修改數(shù)值為OFF并提交，此時(shí)用戶A再次查詢時(shí)發(fā)現(xiàn)值又為OFF，或者數(shù)據(jù)庫(kù)中又多了一條語(yǔ)句，表現(xiàn)為在同一事務(wù)中每次查詢數(shù)據(jù)庫(kù)總是不一致；

    幻讀：當(dāng)用戶A用戶在同一事務(wù)中看到某一值為ON，用戶B已經(jīng)將值修改為OFF，并且已經(jīng)提交，用戶B看到的值為OFF，但用戶A在此事務(wù)中一直看到的為ON，底層數(shù)據(jù)確實(shí)被修改為OFF，因此就體現(xiàn)了幻讀，除非提交后再次生成一個(gè)事務(wù)查看值才為OFF；

    加鎖讀：讀數(shù)據(jù)時(shí)加鎖，此時(shí)別人無(wú)法再讀。

實(shí)例驗(yàn)證

    根據(jù)理論概述，進(jìn)行驗(yàn)證事物的工作流程和隔離級(jí)別，確保透徹了解事物的原理，具體操作如下

MariaDB [(none)]> show processlist;                                      #查看數(shù)據(jù)庫(kù)的進(jìn)程列表，顯示有兩終端連接
+----+------+-----------+------+---------+------+-------+------------------+----------+
| 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';           #驗(yàn)證事物的隔離級(jí)別
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)

    驗(yàn)證隔離級(jí)別 READ UNCOMMITTED（存在臟讀、不可重復(fù)讀）

    第一步：創(chuàng)建表并插入數(shù)據(jù)

MariaDB [test]> create table employee(id int,name varchar(20),age char(3));                             #創(chuàng)建表
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

     第二步：在兩個(gè)數(shù)據(jù)庫(kù)連接線程的會(huì)話變量中設(shè)置隔離級(jí)別為READ-UNCOMMITTED

會(huì)話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)

會(huì)話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)

    第三步：兩邊同時(shí)啟用事物,其中會(huì)話1添加bailongma，但不提交，在會(huì)話2上查看驗(yàn)證

會(huì)話1                     #插入數(shù)據(jù)但未提交，會(huì)話2上查詢后驗(yàn)證
MariaDB [test]> insert into employee values(5,'bailongma',300);
Query OK, 1 row affected (0.00 sec)
會(huì)話2                     #經(jīng)查詢驗(yàn)證會(huì)話1尚未提交已經(jīng)可以讀取，若會(huì)話1回滾，會(huì)話2讀取數(shù)據(jù)為臟數(shù)據(jù)
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)

    驗(yàn)證隔離級(jí)別READ-COMMITTED（解決臟讀問(wèn)題，存在不可重復(fù)讀）

    第一步：創(chuàng)建表并插入數(shù)據(jù)

MariaDB [test]> create table employee(id int,name varchar(20),age char(3));                             #創(chuàng)建表
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

     第二步：在兩個(gè)數(shù)據(jù)庫(kù)連接線程的會(huì)話變量中設(shè)置隔離級(jí)別為READ-COMMITTED

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

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

    第三步：在兩個(gè)連接數(shù)據(jù)庫(kù)的線程進(jìn)程添加bailongma，但不提交進(jìn)行驗(yàn)證

會(huì)話1
MariaDB [test]> insert into employee values(5,'bailongma',305);
Query OK, 1 row affected (0.00 sec)
會(huì)話2              #在會(huì)話1未提交時(shí)，會(huì)話2是無(wú)法讀取數(shù)據(jù)
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)

    第四步：在連會(huì)話1上進(jìn)行提交，然后在會(huì)話2上進(jìn)行驗(yàn)證

會(huì)話1
MariaDB [test]> commit;
Query OK, 0 rows affected (0.00 sec)
會(huì)話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)    #說(shuō)明讀提交可以解決臟讀的問(wèn)題

    驗(yàn)隔離級(jí)別REPEATABLE READ(解決臟讀和重復(fù)讀的問(wèn)題，帶來(lái)新的問(wèn)題幻讀)

    第一步：創(chuàng)建表并插入數(shù)據(jù)

MariaDB [test]> create table employee(id int,name varchar(20),age char(3));                             #創(chuàng)建表
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

    第二步：在兩個(gè)數(shù)據(jù)庫(kù)連接線程的會(huì)話變量中設(shè)置隔離級(jí)別為REPEATABLE-READ

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

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

    第三步：先會(huì)話2中開啟一個(gè)事物查詢表中數(shù)據(jù)，然后在會(huì)話1中添加bailongma用戶，再次在會(huì)話2中的同一事務(wù)中查看表中數(shù)據(jù)（發(fā)現(xiàn)會(huì)話1中數(shù)據(jù)已經(jīng)發(fā)生變化，會(huì)話2的同一事物中任然是之前的數(shù)據(jù)，因此解決了事物的可重復(fù)讀）

會(huì)話2           #開啟一個(gè)事物并查詢表中數(shù)據(jù)
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)
會(huì)話1        #添加bailongma數(shù)據(jù)后，提交并查詢
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)
會(huì)話2      #在會(huì)話2上再次查詢，結(jié)果任然是4條數(shù)據(jù)，原因是會(huì)話2上的事物并未提交，并且解決了可重復(fù)讀，因此只能看到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)

    驗(yàn)隔離級(jí)別SERIALIZABLE(解決重復(fù)讀的問(wèn)題，需注意每次操作都需要重啟新的事物和提交，因?yàn)橛屑渔i，一個(gè)事物只能是一組語(yǔ)句)

     第一步：創(chuàng)建表并插入數(shù)據(jù)

MariaDB [test]> create table employee(id int,name varchar(20),age char(3));                             #創(chuàng)建表
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

    第二步：在兩個(gè)數(shù)據(jù)庫(kù)連接線程的會(huì)話變量中設(shè)置隔離級(jí)別為SERIALIZABLE

會(huì)話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)

會(huì)話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)

    第三步：當(dāng)會(huì)話1上進(jìn)行插入bailongma用戶前，在會(huì)話2上進(jìn)查詢并提交，會(huì)話1添加bailongma并提交，然后再次在會(huì)話2上進(jìn)行查詢

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