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）Read Uncommitted（讀未提交）：可避免更新丟失的發生，
2）Read Committed（讀已提交）：可避免更新丟失、臟讀的發生，
3）Repeatable Read（可重復讀）：可避免更新丟失、臟讀、不可重復讀的發生，
4）Serializable（串行化）：可避免更新丟失、臟讀、不可重復讀、幻讀的發生，
以上四種隔離級別最高的是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 名稱;
事務的隔離級別
語法
查看當前事務的隔離級別：
1 select @@tx_isolation;
設定隔離級別：
1 set tx_isolation = "隔離級別名稱";
多事務并發問題
更新丟失
即便是在最低隔離級別Read Uncommitted的事務里，也能避免更新丟失問題：
當兩個事務同時更新同一資料時，左側事務執行成功，右側事務執行被阻塞，直到左側事務進行了提交或者回滾，或者右側事務因為阻塞超時而報錯，才能結束阻塞，
臟讀
在最低隔離級別Read Uncommitted的事務里，不能避免臟讀的問題：
在隔離級別Read Committed以及高于這個級別的事務里，可以避免臟讀的問題：
不可重復讀
在隔離級別Read Committed以及低于這個級別的事務里，不能避免不可重復讀的問題：
在隔離級別Repeatable Read以及高于這個級別的事務里，可以避免不可重復讀的問題：
幻讀
在隔離級別Repeatable Read以及低于這個級別的事務里，不能避免幻讀的問題，但如果MySQL資料庫使用的存盤引擎是InnoDB則可以避免幻讀的問題，
在隔離級別Read Committed的事務里，出現幻讀的問題：
在隔離級別Repeatable Read的事務里，如果MySQL資料庫使用的存盤引擎是InnoDB則可以避免幻讀的問題：
在最高級別Serializable的事務里，也可以避免幻讀的問題，不過最高級別的系統開銷很大，一般不會使用，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/105091.html
標籤：MySQL
上一篇：knn演算法的matlab實作
下一篇：MySQL聯合查詢問題請教