3分鐘搞懂MySQL事務隔離級別及SET TRANSACTION影響事務
原文連結: https://mp.weixin.qq.com/s/aNku9GLk8oWlxrPa7Se0Eg
我們都知道,MySQL的內建引擎中只有InnoDB、NDB支援事務,而又以InnoDB引擎對於事務的支援最全面也使用最廣泛,所以本文的討論都是基於InnoDB引擎,實驗中用的表都是基於InnoDB的表。
Feature | MyISAM | Memory | InnoDB | Archive | NDB |
---|---|---|---|---|---|
Transactions | No | No | Yes | No | Yes |
MySQL中可以使用SET TRANSACTION來影響事務特性,此語句可以指定一個或多個由逗號分隔的特徵值列表,每個特徵值設定事務隔離級別或訪問模式。此語句在MySQL 5.7中的完整語法
SET [GLOBAL | SESSION] TRANSACTION transaction_characteristic [, transaction_characteristic] ... transaction_characteristic: { ISOLATION LEVEL level | access_mode }level: { REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED | SERIALIZABLE} access_mode: { READ WRITE | READ ONLY}
語法很簡單清晰,這裡有幾個關鍵概念需要理解清楚。
Transaction Isolation Levels(事務隔離級別)
事務隔離是資料庫的基礎能力,ACID中的I指的就是事務隔離,通俗點講就是多個使用者併發訪問資料庫時,資料庫為每一個使用者開啟的事務,不能被其他事務的運算元據所干擾,多個併發事務之間要相互隔離。
那麼到底如何做才算是相互隔離呢?SQL:1992標準規定了四種事務隔離級別:READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。
InnoDB對四種隔離級別都支援,預設級別是REPEATABLE READ。
root@database-one 07:43: [(none)]> select @@tx_isolation; +-----------------+| @@tx_isolation | +-----------------+| REPEATABLE-READ | +-----------------+1 row in set (0.00 sec)
新建會話進行驗證,會話的預設隔離級別確實REPEATABLE-READ。
InnoDB是靠不同的鎖策略實現每個事務隔離級別,隔離級別越高付出的鎖成本也就會越高。我們透過例子來看看不同級別的區別。
root@database-one 08:38: [gftest]> create table testtx(name varchar(10),money decimal(10,2)) engine=innodb; Query OK, 0 rows affected (0.12 sec) root@database-one 08:42: [gftest]> insert into testtx values('A',6000),('B',8000),('C',9000); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 root@database-one 08:43: [gftest]> select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
上面建立了表testtx,並插入了3條資料,表示A有6000元,B有8000元,C有9000元。
- REPEATABLE READ,同一事務內的consistent reads讀取由第一次讀取建立的快照。這意味著,如果在同一事務中發出多個普通(非鎖定)SELECT語句,則這些SELECT語句查到的資料保持一致。
建立會話1,關閉MySQL預設的事務自動提交模式(相關知識可以參考 )。
root@database-one 08:58: [(none)]> prompt \u@database-one \R:\m:\s [\d] session1> PROMPT set to '\u@database-one \R:\m:\s [\d] session1>'root@database-one 08:58:41 [(none)] session1>use gftest; Database changed root@database-one 08:58:55 [gftest] session1>SET autocommit=0; Query OK, 0 rows affected (0.00 sec) root@database-one 08:59:21 [gftest] session1>show variables like 'autocommit'; +---------------+-------+| Variable_name | Value | +---------------+-------+| autocommit | OFF | +---------------+-------+1 row in set (0.02 sec) root@database-one 08:59:36 [gftest] session1>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
建立會話2,關閉MySQL預設的事務自動提交模式(相關知識可以參考 )。
root@database-one 09:01: [(none)]> prompt \u@database-one \R:\m:\s [\d] session2> PROMPT set to '\u@database-one \R:\m:\s [\d] session2>'root@database-one 09:02:13 [(none)] session2>use gftest; Database changed root@database-one 09:02:24 [gftest] session2>SET autocommit=0; Query OK, 0 rows affected (0.00 sec) root@database-one 09:02:30 [gftest] session2>show variables like 'autocommit'; +---------------+-------+| Variable_name | Value | +---------------+-------+| autocommit | OFF | +---------------+-------+1 row in set (0.00 sec) root@database-one 09:02:37 [gftest] session2>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
建立會話3,關閉MySQL預設的事務自動提交模式(相關知識可以參考 )。
root@database-one 09:03: [(none)]> prompt \u@database-one \R:\m:\s [\d] session3> PROMPT set to '\u@database-one \R:\m:\s [\d] session3>'root@database-one 09:03:44 [(none)] session3>use gftest; Database changed root@database-one 09:03:47 [gftest] session3>SET autocommit=0; Query OK, 0 rows affected (0.00 sec) root@database-one 09:03:56 [gftest] session3>show variables like 'autocommit'; +---------------+-------+| Variable_name | Value | +---------------+-------+| autocommit | OFF | +---------------+-------+1 row in set (0.01 sec) root@database-one 09:04:04 [gftest] session3>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
A給B轉100元。在session1中模擬。
root@database-one 09:06:03 [gftest] session1>update testtx set money=money-100 where name='A'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@database-one 09:07:34 [gftest] session1>update testtx set money=money+100 where name='B'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@database-one 09:07:58 [gftest] session1>select * from testtx; +------+---------+| name | money | +------+---------+| A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
session1看到了金額進行了變化,但還未進行提交。
此時,分別去session2、session3進行查詢。
root@database-one 09:02:45 [gftest] session2> root@database-one 09:12:23 [gftest] session2>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
root@database-one 09:04:10 [gftest] session3> root@database-one 09:14:12 [gftest] session3>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
session2、session3均未看到金額變化。
A對轉賬進行確認,即提交。
root@database-one 09:09:28 [gftest] session1>commit; Query OK, 0 rows affected (0.00 sec) root@database-one 09:18:03 [gftest] session1>select * from testtx; +------+---------+| name | money | +------+---------+| A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
此時,再分別去session2、session3進行查詢。
root@database-one 09:12:28 [gftest] session2> root@database-one 09:18:15 [gftest] session2>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
root@database-one 09:14:22 [gftest] session3> root@database-one 09:18:24 [gftest] session3>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
session2、session3還未看到金額變化。因為他們還在自己的事務中(由自己session第一個select * from testtx即隱式開啟了事務),根據REPEATABLE READ事務隔離的原則確實不應該看到。
當session2、session3結束當前事務後,再去查詢就能看到變化了。
root@database-one 09:18:20 [gftest] session2> root@database-one 09:26:58 [gftest] session2>commit; Query OK, 0 rows affected (0.00 sec) root@database-one 09:27:05 [gftest] session2>select * from testtx; +------+---------+| name | money | +------+---------+| A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
root@database-one 09:18:26 [gftest] session3> root@database-one 09:27:17 [gftest] session3>rollback; Query OK, 0 rows affected (0.00 sec) root@database-one 09:27:24 [gftest] session3>select * from testtx; +------+---------+| name | money | +------+---------+| A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
- READ COMMITTED,即使在同一事務中,每個consistent read操作都設定並讀取自己的新快照。
我們將資料還原,並調整三個會話的事務隔離級別均為READ COMMITTED。
root@database-one 09:38:42 [gftest] session1>update testtx set money=6000 where name='A'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 root@database-one 09:39:20 [gftest] session1>update testtx set money=8000 where name='B'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@database-one 09:39:44 [gftest] session1>commit; Query OK, 0 rows affected (0.00 sec) root@database-one 09:39:49 [gftest] session1>SET SESSION TRANSACTION ISOLATION LEVEL read committed; Query OK, 0 rows affected (0.00 sec) root@database-one 09:40:33 [gftest] session1>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
root@database-one 09:41:31 [gftest] session2>SET SESSION TRANSACTION ISOLATION LEVEL read committed; Query OK, 0 rows affected (0.00 sec) root@database-one 09:41:44 [gftest] session2>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
root@database-one 09:42:16 [gftest] session3>SET SESSION TRANSACTION ISOLATION LEVEL read committed; Query OK, 0 rows affected (0.01 sec) root@database-one 09:42:24 [gftest] session3>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
A給B轉100元。在session1中模擬。
root@database-one 09:40:42 [gftest] session1>update testtx set money=money-100 where name='A'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@database-one 09:44:10 [gftest] session1>update testtx set money=money+100 where name='B'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@database-one 09:44:20 [gftest] session1>select * from testtx; +------+---------+| name | money | +------+---------+| A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
session1看到了金額進行了變化,但還未進行提交。
此時,分別去session2、session3進行查詢。
root@database-one 09:42:28 [gftest] session3> root@database-one 09:47:15 [gftest] session3>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
root@database-one 09:42:28 [gftest] session3> root@database-one 09:47:15 [gftest] session3>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
session2、session3均未看到金額變化。
A對轉賬進行確認,即提交。
root@database-one 09:50:37 [gftest] session1>commit; Query OK, 0 rows affected (0.03 sec) root@database-one 09:50:43 [gftest] session1>select * from testtx; +------+---------+| name | money | +------+---------+| A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
此時,再分別去session2、session3視角進行查詢。
root@database-one 09:48:02 [gftest] session2> root@database-one 09:52:18 [gftest] session2>select * from testtx; +------+---------+| name | money | +------+---------+| A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
root@database-one 09:48:18 [gftest] session3> root@database-one 09:53:11 [gftest] session3>select * from testtx; +------+---------+| name | money | +------+---------+| A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
session2、session3均看到金額變化。因為他們雖然還在自己的事務中(由自己session第一個select * from testtx即隱式開啟了事務),根據READ COMMITTED事務隔離的原則應該看到。
- READ UNCOMMITTED,SELECT語句是以非鎖定方式執行的,但可能會使用資料的早期版本,這樣的讀取是不一致的,因此也被稱為髒讀。
我們將資料還原,並調整三個會話的事務隔離級別均為READ COMMITTED。
root@database-one 10:02:49 [gftest] session1>update testtx set money=6000 where name='A'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@database-one 10:03:10 [gftest] session1>update testtx set money=8000 where name='B'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@database-one 10:03:20 [gftest] session1>commit; Query OK, 0 rows affected (0.00 sec) root@database-one 10:03:30 [gftest] session1>SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted; Query OK, 0 rows affected (0.00 sec) root@database-one 10:03:49 [gftest] session1>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
root@database-one 10:02:52 [gftest] session2>SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted; Query OK, 0 rows affected (0.00 sec) root@database-one 10:04:58 [gftest] session2>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
root@database-one 10:05:35 [gftest] session3>SET SESSION TRANSACTION ISOLATION LEVEL read uncommitted; Query OK, 0 rows affected (0.00 sec) root@database-one 10:05:37 [gftest] session3>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
A給B轉100元。在session1中模擬。
root@database-one 10:06:43 [gftest] session1>update testtx set money=money-100 where name='A'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@database-one 10:06:47 [gftest] session1>update testtx set money=money+100 where name='B'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 root@database-one 10:06:57 [gftest] session1>select * from testtx; +------+---------+| name | money | +------+---------+| A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
session1看到了金額進行了變化,但還未進行提交。
此時,分別去session2、session3進行查詢。
root@database-one 10:05:07 [gftest] session2> root@database-one 10:08:34 [gftest] session2>select * from testtx; +------+---------+| name | money | +------+---------+| A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
root@database-one 10:06:02 [gftest] session3> root@database-one 10:08:42 [gftest] session3>select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
session2看到金額變化,session3未看到金額變化。因為他們雖然還在自己的事務中(由自己session第一個select * from testtx即隱式開啟了事務),根據READ UNCOMMITTED事務隔離的原則,session3沒有看到金額變化是因為使用了資料的早期版本。這裡需要特別注意,有時可能是session2會看到金額變化、有時可能是session3會看到金額變化、有時可能是session2和session3都會看到金額變化、有時可能是session2和session3都不會看到金額變化,這個是由MySQL根據資料的版本情況即時確定的。
A對轉賬進行確認,即提交。
root@database-one 10:35:52 [gftest] session1>commit; Query OK, 0 rows affected (0.01 sec) root@database-one 10:36:01 [gftest] session1>select * from testtx; +------+---------+| name | money | +------+---------+| A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
此時,再分別去session2、session3視角進行查詢。
root@database-one 10:09:24 [gftest] session2> root@database-one 11:09:45 [gftest] session2>select * from testtx; +------+---------+| name | money | +------+---------+| A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
root@database-one 11:08:29 [gftest] session3> root@database-one 11:11:54 [gftest] session3>select * from testtx; +------+---------+| name | money | +------+---------+| A | 5900.00 | | B | 8100.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec)
session2、session3均看到金額變化。
- SERIALIZABLE,這個級別類似於REPEATABLE READ,但更嚴格。在非自動提交模式下,InnoDB隱式地將所有SELECT語句轉換為SELECT … LOCK IN SHARE MODE。在自動提交模式下,SELECT在自己的事務裡,以事務的原則執行。
因為效果和REPEATABLE READ類似,我這裡就不再演示了,有興趣的同學可以自己驗證。SERIALIZABLE執行的規則比REPEATABLE READ更為嚴格,主要用於特殊情況,如XA事務、解決併發和死鎖問題等場景。
Transaction Access Mode(事務訪問模式)
事務的訪問模式很容易理解,就是指在事務中如何對錶中的資料進行使用,分為READ WRITE和READ ONLY,預設是READ WRITE。
還是testtx這張表,我們開啟一個READ ONLY事務,對其中的資料進行修改,看看會發生什麼。
root@database-one 11:56: [gftest]> select @@tx_isolation,@@autocommit; +-----------------+--------------+| @@tx_isolation | @@autocommit | +-----------------+--------------+| REPEATABLE-READ | 1 | +-----------------+--------------+1 row in set (0.00 sec) root@database-one 11:57: [gftest]> SET SESSION TRANSACTION read only; Query OK, 0 rows affected (0.00 sec) root@database-one 11:57: [gftest]> start transaction; Query OK, 0 rows affected (0.00 sec) root@database-one 11:59: [gftest]> select * from testtx; +------+---------+| name | money | +------+---------+| A | 6000.00 | | B | 8000.00 | | C | 9000.00 | +------+---------+3 rows in set (0.00 sec) root@database-one 11:59: [gftest]> update testtx set money=0 where name='A'; ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.
可以看到,READ ONLY模式的事務修改資料時會報錯。
Transaction Characteristic Scope(事務屬性的作用範圍)
細心的同學可能已經注意到,在SET TRANSACTION時有可選關鍵字GLOBAL和SESSION,它們決定了事務屬性的作用範圍。
- 使用GLOBAL時,該語句影響所有後續會話,現有會話不受影響。
- 使用SESSION時,該語句影響當前會話中的所有後續事務。
- 不使用GLOBAL或SESSION時,該語句僅影響會話中執行的下一個事務。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/31556440/viewspace-2687101/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 徹底搞懂 MySQL 事務的隔離級別MySql
- MySQL 事務隔離級別MySql
- MySQL事務隔離級別MySql
- [Mysql]事務/隔離級別MySql
- MySQL事務的隔離級別MySql
- MySQL的事務隔離級別MySql
- 理解MySQL事務隔離級別MySql
- mysql修改事務隔離級別MySql
- MySQL的事務處理及隔離級別MySql
- MySQL事務隔離級別和MVCCMySqlMVC
- mysql如何修改事務隔離級別MySql
- mysql事務隔離級別和鎖MySql
- 啥是 MySQL 事務隔離級別?MySql
- Mysql 四種事務隔離級別MySql
- Mysql鎖與事務隔離級別MySql
- 理解mysql的事務隔離級別MySql
- MySQL事務隔離級別詳解MySql
- oracle事務隔離級別transaction isolation level初識Oracle
- PostgreSQL事務隔離級別SQL
- 事務、特性、隔離級別
- MySQL 的四種事務隔離級別MySql
- MySQL 事務的隔離級別初窺MySql
- MySQL的四種事務隔離級別MySql
- MySQL 事務隔離級別實現原理MySql
- 事務基礎特性及隔離級別
- 【MySQL】MySQL的四種事務隔離級別MySql
- MySQL事務隔離MySql
- MySQL 事務隔離MySql
- SqlServer事務詳解(事務隔離性和隔離級別詳解)SQLServer
- Oracle-事務隔離級別Oracle
- JDBC 事務(一) 隔離級別JDBC
- MySQL的事務隔離級別是什麼?MySql
- MySQL 事務隔離級別解析和實戰MySql
- 四個案例看懂 MySQL 事務隔離級別MySql
- Mysql事務隔離級別與鎖機制MySql
- MySQL事務隔離級別的實現原理MySql
- 【mysql】官方的SQL事務隔離級別文件MySql
- 資料庫事務與事務的隔離級別資料庫