MySQL學習——管理事務

摘要：本文主要學習了使用DCL語句管理事務的操作。

瞭解事務

什麼是事務

事務是一組邏輯處理單位，可以是執行一條SQL語句，也可以是執行幾個SQL語句。

事務用來保證數據由一種存儲情況變為另一種情況，組成事務的各個單元要麼都執行成功，要麼都執行失敗。

為什麼使用事務

如果只是簡單的一條SQL語句的執行，那麼是不需要事務的，但在一些複雜的情況下，一個操作會涉及到多條SQL語句的執行，這種情況下就有必要保證所有的操作全部成功或者全部失敗。

比如，小明給小紅轉賬的一個操作，就會涉及到從小明賬戶扣錢和給小紅賬戶充錢的兩個操作。只有兩個操作都成功執行了整個操作才算成功，這時就可以提交整個事務，可以說狀態由轉賬前變到了轉賬後。否則有任何一個操作執行失敗的話整個操作都要算做失敗，這時就需要恢復事務，保證兩個賬戶上的金額和轉賬前是一樣的，表示恢復到了轉賬前的狀態。

所以事務是為了保證一組操作的完整性而出現的，也是為了保證數據操作的安全。

支持使用事務的引擎

使用 show engines; 命令查看資料庫支持的存儲引擎，以及存儲引擎是否支持事務：

 1 mysql> show engines;
 2 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
 3 | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
 4 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
 5 | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
 6 | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
 7 | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
 8 | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
 9 | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
10 | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
11 | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
12 | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
13 | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
14 +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
15 9 rows in set (0.00 sec)
16 
17 mysql> 

發現預設的是InnoDB引擎，並且也支持事務。

基本術語

保存點(savepoint)：指在事務執行前，或者事務執行後，數據在資料庫里的一個存儲情況，有時也會被稱為狀態。

回退(rollback)：指撤銷事務的操作，事務執行期間執行的操作都將失效，事務會恢復到上一個狀態。

提交(commit)：值提交事務的操作，事務期間執行的操作全部生效，事務進入一個新的狀態。

事務的特性（ACID）

原子性（Atomicity）：指事務包含的所有操作要麼全部成功提交，要麼全部失敗回滾。

一致性（Consistency）：指事務必須使資料庫從一個一致性狀態變換到另一個一致性狀態。

隔離性（Isolation）：指當多個用戶併發訪問資料庫並且操作同一張表的時候，資料庫為每一個用戶開啟的事務，不能被其他事務的操作所干擾，多個併發事務之間要相互隔離。

持久性（Durability）：指一個事務一旦被提交了，那麼對資料庫中的數據的改變就是永久性的，哪怕是在資料庫系統遇到故障的情況下也不會丟失提交事務的操作。

多事務併發操作產生的問題

對資料庫事務的操作其實可以分為兩類：一種是讀取事務（Select），另一種是修改事務（Insert、Update、Delete）。

單個事務的情況下，事務操作不會產生併發問題，但是如果多個事務在同一時刻操作同一數據可能會影響最終期望的結果，產生併發問題。

主要的問題有四種：

1）更新丟失：更新時更新。兩個更新事務同時更新一個數據，就會導致一個事務的更新操作丟失。

2）臟讀：更新時讀取。一個更新事務更新一條數據時，另一個讀取事務讀取了還沒提交的數據，這時的數據是錯誤的，就會導致讀到臟數據。

3）不可重覆讀：讀取時更新。一個讀取事務多次讀取一條數據時，另一個更新事務修改並提交了這條數據，就會導致在更新事務提交的前後讀取到了不同的數據。

4）幻讀：讀取時插入或刪除。一個讀取事務讀取時，另一個插入事務插入了一條數據，或者另一個刪除任務刪除了一條數據，這樣就可能多讀或者少讀出一條數據，出現幻讀。

事務的隔離級別

因為多事務的併發問題的嚴重程度和解決問題產生的系統開銷不同，為瞭解決不同程度的問題，SQL標准定義了隔離級別，每個級別都有各自的具體規則，用來限定事務內外的哪些改變是可見的，哪些是不可見的。低級別的隔離級一般支持更高的併發處理，並擁有更低的系統開銷。

主要的隔離級別有四種：

1）Read Uncommitted（讀未提交）：最低的隔離級別，所有事務都可以看到其他未提交事務的執行結果。

2）Read Committed（讀已提交）：大多數資料庫系統的預設隔離級別，但不是MySQL預設的，一個事務只能看見已提交事務所做的改變。

3）Repeatable Read（可重覆讀）：MySQL的預設事務隔離級別，確保同一事務的多個實例在併發讀取數據時，會看到同樣的數據。

4）Serializable（串列化）：最高的隔離級別，通過強制事務排序解決多事務的併發問題。簡言之，它是在每個讀的數據行上加上共用鎖，但這麼做可能導致大量的超時現象和鎖競爭。

在MySQL中，實現了這四種隔離級別，分別解決了不同等級的併發問題：

1）Serializable（串列化）：可避免更新丟失、臟讀、不可重覆讀、幻讀的發生。

2）Repeatable Read（可重覆讀）：可避免更新丟失、臟讀、不可重覆讀的發生。

3）Read Committed（讀已提交）：可避免更新丟失、臟讀的發生。

4）Read Uncommitted（讀未提交）：可避免更新丟失的發生。

以上四種隔離級別最高的是Serializable級別，最低的是Read uncommitted級別，當然級別越高，執行效率就越低。

像Serializable這樣的級別，就是以鎖表的方式（類似於Java多線程中的鎖）使得其他的線程只能在鎖外等待，所以平時選用何種隔離級別應該根據實際情況。

MySQL的預設事務隔離級別是Repeatable Read級別，相比較其他存儲引擎，InnoDB和Falcon存儲引擎通過多版本併發控制（MVCC，Multiversion Concurrency Control）機制解決了幻讀的問題。

事務自動提交

語法

查詢事務自動提交：

1 select @@autocommit;

開啟自動提交：

1 set autocommit = 1;

關閉自動提交：

1 set autocommit = 0;

實例

 1 mysql> set autocommit = 0;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 mysql> select @@autocommit;
 5 +--------------+
 6 | @@autocommit |
 7 +--------------+
 8 |            0 |
 9 +--------------+
10 1 row in set (0.00 sec)
11 
12 mysql> 

初始化事務

語法

首先聲明初始化MySQL事務後所有的SQL語句為一個單元。語法如下：

1 start transaction

另外，用戶也可以使用 begin; 或者 begin work; 命令初始化事務，通常 start transaction; 命令後面跟隨的是組成事務的SQL語句。

實例

1 mysql> start transaction;
2 Query OK, 0 rows affected (0.00 sec)
3 
4 mysql> 

提交事務

語法

在用戶沒有提交事務之前，其他用戶查詢的結果不會顯示沒有提交的事務。只有用戶成功提交事務後，其他用戶才可能查詢到事務結果。語法如下：

1 commit;

也可以使用 commit work; 提交事務。

實例

 1 mysql> start transaction;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 mysql> update student set sex = '女' where id = 904;
 5 Query OK, 1 row affected (0.00 sec)
 6 Rows matched: 1  Changed: 1  Warnings: 0
 7 
 8 mysql> commit;
 9 Query OK, 0 rows affected (0.01 sec)
10 
11 mysql> 

回滾事務

語法

如果用戶想要回滾未提交的事務操作，可使用回滾事務。語法如下：

1 rollback;

也可以使用 rollback work; 回滾事務。

實例

 1 mysql> start transaction;
 2 Query OK, 0 rows affected (0.00 sec)
 3 
 4 mysql> update student set sex = '女' where id = 904;
 5 Query OK, 1 row affected (0.00 sec)
 6 Rows matched: 1  Changed: 1  Warnings: 0
 7 
 8 mysql> rollback;
 9 Query OK, 0 rows affected (0.00 sec)
10 
11 mysql> 

設置還原點

還原點必須要在事務內才能使用，否則會產生錯誤。

語法

創建還原點：

1 savepoint 名稱;

回滾還原點：

1 rollback to 名稱;

刪除還原點：

1 release savepoint 名稱;

事務的隔離級別

未完待續