這篇文章給大家介紹MySQL中SET TRANSACTION會不會影響事務(wù)，內(nèi)容非常詳細，感興趣的小伙伴們可以參考借鑒，希望對大家能有所幫助。
成都創(chuàng)新互聯(lián)提供高防服務(wù)器租用、云服務(wù)器、香港服務(wù)器、四川主機托管等
MySQL支持SQL:1992標(biāo)準(zhǔn)中的所有事務(wù)隔離級別，使用SET TRANSACTION來設(shè)置不同的事務(wù)隔離級別或訪問模式。
我們都知道，MySQL的內(nèi)置引擎中只有InnoDB、NDB支持事務(wù)，而又以InnoDB引擎對于事務(wù)的支持最全面也使用最廣泛，所以本文的討論都是基于InnoDB引擎，實驗中用的表都是基于InnoDB的表。
| Feature | MyISAM | Memory | InnoDB | Archive | NDB |
|---|---|---|---|---|---|
| Transactions | No | No | Yes | No | Yes |
MySQL中可以使用SET TRANSACTION來影響事務(wù)特性，此語句可以指定一個或多個由逗號分隔的特征值列表，每個特征值設(shè)置事務(wù)隔離級別或訪問模式。此語句在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}語法很簡單清晰，這里有幾個關(guān)鍵概念需要理解清楚。
事務(wù)隔離是數(shù)據(jù)庫的基礎(chǔ)能力，ACID中的I指的就是事務(wù)隔離，通俗點講就是多個用戶并發(fā)訪問數(shù)據(jù)庫時，數(shù)據(jù)庫為每一個用戶開啟的事務(wù)，不能被其他事務(wù)的操作數(shù)據(jù)所干擾，多個并發(fā)事務(wù)之間要相互隔離。
那么到底如何做才算是相互隔離呢？SQL:1992標(biāo)準(zhǔn)規(guī)定了四種事務(wù)隔離級別：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是靠不同的鎖策略實現(xiàn)每個事務(wù)隔離級別，隔離級別越高付出的鎖成本也就會越高。我們通過例子來看看不同級別的區(qū)別。
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)上面創(chuàng)建了表testtx，并插入了3條數(shù)據(jù)，表示A有6000元，B有8000元，C有9000元。
REPEATABLE READ，同一事務(wù)內(nèi)的consistent reads讀取由第一次讀取建立的快照。這意味著，如果在同一事務(wù)中發(fā)出多個普通（非鎖定）SELECT語句，則這些SELECT語句查到的數(shù)據(jù)保持一致。
創(chuàng)建會話1，關(guān)閉MySQL默認的事務(wù)自動提交模式（相關(guān)知識可以參考 MySQL中的事務(wù)控制語句）。
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)
創(chuàng)建會話2，關(guān)閉MySQL默認的事務(wù)自動提交模式（相關(guān)知識可以參考 MySQL中的事務(wù)控制語句）。
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)
創(chuàng)建會話3，關(guān)閉MySQL默認的事務(wù)自動提交模式（相關(guān)知識可以參考 MySQL中的事務(wù)控制語句）。
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轉(zhuǎn)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對轉(zhuǎn)賬進行確認，即提交。
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還未看到金額變化。因為他們還在自己的事務(wù)中（由自己session第一個select * from testtx即隱式開啟了事務(wù)），根據(jù)REPEATABLE READ事務(wù)隔離的原則確實不應(yīng)該看到。
當(dāng)session2、session3結(jié)束當(dāng)前事務(wù)后，再去查詢就能看到變化了。
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，即使在同一事務(wù)中，每個consistent read操作都設(shè)置并讀取自己的新快照。
我們將數(shù)據(jù)還原，并調(diào)整三個會話的事務(wù)隔離級別均為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轉(zhuǎn)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對轉(zhuǎn)賬進行確認，即提交。
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均看到金額變化。因為他們雖然還在自己的事務(wù)中（由自己session第一個select * from testtx即隱式開啟了事務(wù)），根據(jù)READ COMMITTED事務(wù)隔離的原則應(yīng)該看到。
READ UNCOMMITTED，SELECT語句是以非鎖定方式執(zhí)行的，但可能會使用數(shù)據(jù)的早期版本，這樣的讀取是不一致的，因此也被稱為臟讀。
我們將數(shù)據(jù)還原，并調(diào)整三個會話的事務(wù)隔離級別均為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轉(zhuǎn)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未看到金額變化。因為他們雖然還在自己的事務(wù)中（由自己session第一個select * from testtx即隱式開啟了事務(wù)），根據(jù)READ UNCOMMITTED事務(wù)隔離的原則，session3沒有看到金額變化是因為使用了數(shù)據(jù)的早期版本。這里需要特別注意，有時可能是session2會看到金額變化、有時可能是session3會看到金額變化、有時可能是session2和session3都會看到金額變化、有時可能是session2和session3都不會看到金額變化，這個是由MySQL根據(jù)數(shù)據(jù)的版本情況即時確定的。
A對轉(zhuǎn)賬進行確認，即提交。
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語句轉(zhuǎn)換為SELECT … LOCK IN SHARE MODE。在自動提交模式下，SELECT在自己的事務(wù)里，以事務(wù)的原則運行。
因為效果和REPEATABLE READ類似，我這里就不再演示了，有興趣的同學(xué)可以自己驗證。SERIALIZABLE執(zhí)行的規(guī)則比REPEATABLE READ更為嚴格，主要用于特殊情況，如XA事務(wù)、解決并發(fā)和死鎖問題等場景。
事務(wù)的訪問模式很容易理解，就是指在事務(wù)中如何對表中的數(shù)據(jù)進行使用，分為READ WRITE和READ ONLY，默認是READ WRITE。
還是testtx這張表，我們開啟一個READ ONLY事務(wù)，對其中的數(shù)據(jù)進行修改，看看會發(fā)生什么。
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模式的事務(wù)修改數(shù)據(jù)時會報錯。
細心的同學(xué)可能已經(jīng)注意到，在SET TRANSACTION時有可選關(guān)鍵字GLOBAL和SESSION，它們決定了事務(wù)屬性的作用范圍。
使用GLOBAL時，該語句影響所有后續(xù)會話，現(xiàn)有會話不受影響。
使用SESSION時，該語句影響當(dāng)前會話中的所有后續(xù)事務(wù)。
不使用GLOBAL或SESSION時，該語句僅影響會話中執(zhí)行的下一個事務(wù)。
關(guān)于MySQL中SET TRANSACTION會不會影響事務(wù)就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。
