從根上了解 MySQL 的事務

事務的概念

MySQL事務是一個或者多個的資料庫操作，要麼全部執行成功，要麼全部失敗復原。

事務是通過事務日志來實作的，事務日志包括：redo log和undo log。

事務的狀态

活動的（active）

事務對應的資料庫操作正在執行過程中時，我們就說該事務處在活動的狀态。

部分送出的（partially committed）

當事務中的最後一個操作執行完成，但由于操作都在記憶體中執行，所造成的影響并沒有重新整理到磁盤時，我們就說該事務處在部分送出的狀态。

失敗的（failed）

當事務處在活動的或者部分送出的狀态時，可能遇到了某些錯誤（資料庫自身的錯誤、作業系統錯誤或者直接斷電等）而無法繼續執行，或者人為的停止目前事務的執行，我們就說該事務處在失敗的狀态。

中止的（aborted）

如果事務執行了半截而變為失敗的狀态，撤銷失敗事務對目前資料庫造成的影響，我們把這個撤銷的過程稱之為復原。

當復原操作執行完畢時，也就是資料庫恢複到了執行事務之前的狀态，我們就說該事務處在了中止的狀态。

送出的（committed）

當一個處在部分送出的狀态的事務将修改過的資料都同步到磁盤上之後，我們就可以說該事務處在了送出的狀态。

從圖中大家也可以看出了，隻有當事務處于送出的或者中止的狀态時，一個事務的生命周期才算是結束了。對于已經送出的事務來說，該事務對資料庫所做的修改将永久生效，對于處于中止狀态的事務，該事務對資料庫所做的所有修改都會被復原到沒執行該事務之前的狀态。

事務的作用

事務主要是為了保證複雜資料庫操作資料的一緻性，尤其是在并發通路資料時。

MySQL 事務主要用于處理操作量大，複雜度高的資料。

事務的特點

原子性（Atomicity，又稱不可分割性）

事務的資料操作，要麼全部執行成功，要麼全部失敗復原到執行之前的狀态，就像這個事務從來沒有執行過一樣。

隔離性（Isolation，又稱獨立性）

多個事務之間是互相隔離，互不影響的。資料庫允許多個并發事務同時對其資料進行讀寫和修改的能力，隔離性可以防止多個事務并發執行時由于交叉執行而導緻資料的不一緻。

四種隔離狀态：
1. 讀未送出（Read uncommitted）
2. 讀送出（Read committed）
3. 可重複讀（Repeatable read）
4. 串行化（Serializable）

一緻性（Consistency）

在事務操作之前和之後，資料都是保持一個相同的狀态，資料庫的完整性沒有被破壞。

原子性和隔離性，對一緻性有着至關重要的影響。

持久性（Durability）

當事務操作完成後，資料會被重新整理到磁盤永久儲存，即便是系統故障也不會丢失。

事務的文法

資料

建立資料表：
create table account(
    -> id int(10) auto_increment,
    -> name varchar(30),
    -> balance int(10),
    -> primary key (id));
插入資料：
insert into account(name,balance) values('老王媳婦',100),('老王',10);

mysql> select * from account;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | 老王媳婦 |     100    |
|  2 | 老王        |      10 |
+----+--------------+---------+

老王媳婦有100元存在自己的微信賬戶上了，專門給老王每個月發零花錢用的，表現好給的多，老王也有自己的小金庫，目前已經攢到了10元零花錢了，哈哈哈。

begin

事務啟動方式1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> 事務操作SQL......

start transaction [修飾符]

修飾符：
1. read only //隻讀
2. read write //讀寫 預設
3. WITH CONSISTENT SNAPSHOT //一緻性讀

事務啟動方式2

mysql> start transaction read only;
Query OK, 0 rows affected (0.00 sec)
mysql> 事務操作SQL......

如設定read only後，對資料進行修改會報錯：

mysql> start transaction read only;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance=banlance+30 where id = 2;
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.

commit

事務執行送出，送出成功則重新整理到磁盤

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

rollback

事務執行復原，回到事務操作之前的狀态。

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

這裡需要強調一下，ROLLBACK語句是我們程式員手動的去復原事務時才去使用的，如果事務在執行過程中遇到了某些錯誤而無法繼續執行的話，事務自身會自動的復原。

完整的送出例子

1月份，老王的表現很不錯，老王媳婦給他獎勵20元零花錢。

執行步驟：
1. 從老王媳婦賬戶讀取資料
2. 從老王媳婦賬戶上減掉20元
3. 從老王賬戶讀取資料
4. 給老王賬戶增加20元
5. 執行送出成功
6. 此時老王媳婦賬戶隻有80元啦，而老王賬戶有30元啦，老王高興不得了咯

mysql> begin;
Query OK, 0 rows affected (0.01 sec)

mysql> update account set balance=balance-20 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update account set balance=balance+20 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

賬戶餘額：
mysql> select * from account;
+----+--------------+---------+
| id | name  | balance |
+----+--------------+---------+
| 1 | 老王媳婦 | 80 |
| 2 | 老王  | 30 |
+----+--------------+---------+

完整的復原例子

2月份，老王本來表現得很棒，堅持幹家務活和遛狗，老王媳婦要給他25元的零花錢，可老王不經誇啊，老王媳婦正在給老王轉零花錢時，突然看到桌子上老王手機收到一條小女生發來的微信：親愛的王哥....，老王媳婦特别生氣，一怒之下撤回了轉賬，取消這個月的零花錢。

執行步驟：
1. 從老王媳婦賬戶讀取資料
2. 從老王媳婦賬戶上減掉25元
3. 從老王賬戶讀取資料
4. 給老王賬戶增加25元
5. 此時老王媳婦撤回之前的操作
6. 此時，老王和老王媳婦的賬戶餘額還是保持操作之前的數目

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance=balance-25 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update account set balance=balance+25 where id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

賬戶餘額：
mysql> select * from account;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | 老王媳婦 |      80 |
|  2 | 老王       |      30 |
+----+--------------+---------+

事務支援的存儲引擎

1. InnoDB
2. NDB

不支援的存儲引擎，比如在MyISAM上操作事務，事務不會生效，SQL語句直接自動執行送出，是以復原對于不支援事務的存儲引擎是無效的。

create table tb1(
    -> id int(10) auto_increment,
    -> name varchar(30),
    -> primary key (id)
    -> )engine=myisam charset=utf8mb4;
    
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb1(name) values('Tom');
Query OK, 1 row affected (0.01 sec)

mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | Tom |
+----+------+
1 row in set (0.00 sec)

mysql> rollback;//復原無效
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select * from tb1;
+----+------+
| id | name |
+----+------+
| 1 | Tom |
+----+------+
1 row in set (0.00 sec)

事務的設定與檢視

檢視事務開啟情況：
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+

預設是事務自動送出的，每執行一條SQL就自動送出。

此時需要操作事務，則需要顯式開啟（begin or start transaction）和送出（commit）或復原（rollback）。

如設定成OFF，則需要執行送出（commit）或復原（rollback）操作時才會真正執行事務。

關閉自動送出方式

第一種

顯式的的使用START TRANSACTION或者BEGIN語句開啟一個事務。

第二種

把系統變量autocommit的值設定為OFF。

SET autocommit = OFF;

隐式送出情況

當我們使用START TRANSACTION或者BEGIN語句開啟了一個事務，或者把系統變量autocommit的值設定為OFF時，事務就不會進行自動送出，但是如果我們輸入了某些語句之後就會悄悄的送出掉，就像我們輸入了COMMIT語句了一樣，這種因為某些特殊的語句而導緻事務送出的情況稱為隐式送出

定義或修改資料庫對象的資料定義語言（Data definition language，縮寫為：DDL）

所謂的資料庫對象，指的就是資料庫、表、視圖、存儲過程等等這些東西。當我們使用CREATE、ALTER、DROP等語句去修改這些所謂的資料庫對象時，就會隐式的送出前邊語句所屬于的事務。

BEGIN;
SELECT ... # 事務中的一條語句
UPDATE ... # 事務中的一條語句
... # 事務中的其它語句
CREATE TABLE ... # 此語句會隐式的送出前邊語句所屬于的事務

隐式使用或修改mysql資料庫中的表

隐式使用或修改mysql資料庫中的表。

當我們使用ALTER USER、CREATE USER、DROP USER、GRANT、RENAME USER、REVOKE、SET PASSWORD等語句時也會隐式的送出前邊語句所屬于的事務。

事務控制或關于鎖定的語句

事務控制或關于鎖定的語句。

當我們在一個事務還沒送出或者復原時就又使用START TRANSACTION或者BEGIN語句開啟了另一個事務時，會隐式的送出上一個事務。

BEGIN;
SELECT ... # 事務中的一條語句
UPDATE ... # 事務中的一條語句
... # 事務中的其它語句
BEGIN; # 此語句會隐式的送出前邊語句所屬于的事務

或者目前的autocommit系統變量的值為OFF，我們手動把它調為ON時，也會隐式的送出前邊語句所屬的事務。

或者使用LOCK TABLES、UNLOCK TABLES等關于鎖定的語句也會隐式的送出前邊語句所屬的事務。

加載資料的語句

比如我們使用LOAD DATA語句來批量往資料庫中導入資料時，也會隐式的送出前邊語句所屬的事務。

關于MySQL複制的一些語句

使用START SLAVE、STOP SLAVE、RESET SLAVE、CHANGE MASTER TO等語句時也會隐式的送出前邊語句所屬的事務。

其它的一些語句

使用ANALYZE TABLE、CACHE INDEX、CHECK TABLE、FLUSH、 LOAD INDEX INTO CACHE、OPTIMIZE TABLE、REPAIR TABLE、RESET等語句也會隐式的送出前邊語句所屬的事務。

事務的儲存點

概念

在事務對應的資料庫語句中打幾個點，我們在調用ROLLBACK語句時可以指定會滾到哪個點，而不是回到最初的原點。

有了事務的儲存點，我們在進行複雜的事務操作時，我們不用擔心一出錯直接復原到最初狀态，就如一夜回到解放前。

使用文法

1. SAVEPOINT 儲存點名稱;//标記儲存點
2. ROLLBACK TO [SAVEPOINT] 儲存點名稱;//復原到某一個儲存點
3. RELEASE SAVEPOINT 儲存點名稱;//删除

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set balance=balance-20 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> savepoint action1;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from account;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | 老王媳婦 |      60 |
|  2 | 老王       |      30 |
+----+--------------+---------+

mysql> update account set balance=balance+30 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> rollback to action1;//復原到action1儲存點
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+--------------+---------+
| id | name         | balance |
+----+--------------+---------+
|  1 | 老王媳婦 |      60 |
|  2 | 老王       |      30 |
+----+--------------+---------+

參考：掘金小冊《MySQL 是怎樣運作的：從根兒上了解 MySQL》書籍《MySQL高性能》

從根上了解 MySQL 的事務

事務的概念

事務的狀态

活動的（active）

部分送出的（partially committed）

失敗的（failed）

中止的（aborted）

送出的（committed）

事務的作用

事務的特點

原子性（Atomicity，又稱不可分割性）

隔離性（Isolation，又稱獨立性）

一緻性（Consistency）

持久性（Durability）

事務的文法

資料

begin

start transaction [修飾符]

commit

rollback

完整的送出例子

完整的復原例子

事務支援的存儲引擎

事務的設定與檢視

關閉自動送出方式

第一種

第二種

隐式送出情況

定義或修改資料庫對象的資料定義語言（Data definition language，縮寫為：DDL）

隐式使用或修改mysql資料庫中的表

事務控制或關于鎖定的語句

加載資料的語句

關于MySQL複制的一些語句

其它的一些語句

事務的儲存點

概念

使用文法

繼續閱讀