mysql事務(wù)以及隔離級(jí)別
1. 簡(jiǎn)介
MySQL 事務(wù)主要用于處理操作量大稳摄，復(fù)雜度高的數(shù)據(jù)稚字。比如說(shuō)，在人員管理系統(tǒng)中，你刪除一個(gè)人員胆描，你即需要?jiǎng)h除人員的基本資料瘫想，也要?jiǎng)h除和該人員相關(guān)的信息，如信箱昌讲，文章等等国夜，這樣，這些數(shù)據(jù)庫(kù)操作語(yǔ)句就構(gòu)成一個(gè)事務(wù)剧蚣！
- 在 MySQL 中只有使用了 Innodb 數(shù)據(jù)庫(kù)引擎的數(shù)據(jù)庫(kù)或表才支持事務(wù)支竹。
- 事務(wù)處理可以用來(lái)維護(hù)數(shù)據(jù)庫(kù)的完整性，保證成批的 SQL 語(yǔ)句要么全部執(zhí)行鸠按，要么全部不執(zhí)行礼搁。
- 事務(wù)用來(lái)管理 insert,update,delete 語(yǔ)句
2. 事務(wù)的基本要素ACID
一般來(lái)說(shuō)，事務(wù)是必須滿足4個(gè)條件（ACID）：：原子性（Atomicity目尖，或稱不可分割性）馒吴、一致性（Consistency）、隔離性（Isolation瑟曲，又稱獨(dú)立性）饮戳、持久性（Durability）。
- 原子性：一個(gè)事務(wù)（transaction）中的所有操作洞拨，要么全部完成扯罐，要么全部不完成，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)烦衣。事務(wù)在執(zhí)行過(guò)程中發(fā)生錯(cuò)誤歹河，會(huì)被回滾（Rollback）到事務(wù)開(kāi)始前的狀態(tài)，就像這個(gè)事務(wù)從來(lái)沒(méi)有執(zhí)行過(guò)一樣花吟。
- 一致性：在事務(wù)開(kāi)始之前和事務(wù)結(jié)束以后秸歧，數(shù)據(jù)庫(kù)的完整性沒(méi)有被破壞。這表示寫(xiě)入的資料必須完全符合所有的預(yù)設(shè)規(guī)則衅澈，這包含資料的精確度键菱、串聯(lián)性以及后續(xù)數(shù)據(jù)庫(kù)可以自發(fā)性地完成預(yù)定的工作。比如A向B轉(zhuǎn)賬今布，不可能A扣了錢(qián)经备，B卻沒(méi)收到。
- 隔離性：數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫(xiě)和修改的能力部默，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致弄喘。事務(wù)隔離分為不同級(jí)別，包括讀未提交（Read uncommitted）甩牺、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（Serializable）累奈。
- 持久性：事務(wù)完成后贬派，事務(wù)對(duì)數(shù)據(jù)庫(kù)的所有更新將被保存到數(shù)據(jù)庫(kù)急但，不能回滾。
3. 事務(wù)的并發(fā)問(wèn)題
- 臟讀：允許讀取未提交的臟數(shù)據(jù)搞乏，比如：事務(wù)A讀取了事務(wù)B更新的數(shù)據(jù)波桩，然后B回滾操作，那么A讀取到的數(shù)據(jù)是臟數(shù)據(jù)请敦；
- 不可重復(fù)讀：如果你在時(shí)間點(diǎn)t1讀取了一些記錄镐躲，在t2時(shí)間點(diǎn)也想重新讀取一樣的數(shù)據(jù)時(shí)，這些記錄可能已經(jīng)被改變侍筛，或者消失萤皂，比如：事務(wù) A 多次讀取同一數(shù)據(jù)，事務(wù) B 在事務(wù)A多次讀取的過(guò)程中匣椰，對(duì)數(shù)據(jù)作了更新并提交裆熙，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時(shí)，結(jié)果不一致禽笑。
- 幻讀：系統(tǒng)管理員A將數(shù)據(jù)庫(kù)中所有學(xué)生的成績(jī)從具體分?jǐn)?shù)改為ABCDE等級(jí)入录，但是系統(tǒng)管理員B就在這個(gè)時(shí)候插入了一條具體分?jǐn)?shù)的記錄搪柑，當(dāng)系統(tǒng)管理員A改結(jié)束后發(fā)現(xiàn)還有一條記錄沒(méi)有改過(guò)來(lái)油狂，就好像發(fā)生了幻覺(jué)一樣评甜，這就叫幻讀非迹。
不可重復(fù)讀的和幻讀很容易混淆火惊，不可重復(fù)讀側(cè)重于修改狼钮，幻讀側(cè)重于新增或刪除卧抗。解決不可重復(fù)讀的問(wèn)題只需鎖住滿足條件的行响逢，解決幻讀需要鎖表望蜡。
4. 事務(wù)的4種隔離級(jí)別
為了解決上面事務(wù)的并發(fā)問(wèn)題唤崭，sql標(biāo)準(zhǔn)提出了4種隔離級(jí)別，下面是每種隔離級(jí)別能夠解決的問(wèn)題對(duì)應(yīng)關(guān)系：
| 事務(wù)隔離級(jí)別 | 臟讀 | 不可重復(fù)讀 | 幻讀 |
|---|---|---|---|
| read-uncommitted | N | N | N |
| read-committed | Y | N | N |
| repeatable-read(default) | Y | Y | N |
| serializable | Y | Y | Y |
mysql的默認(rèn)隔離級(jí)別是Repeatable脖律。
查看系統(tǒng)級(jí)和會(huì)話級(jí)的隔離級(jí)別：
mysql> select @@global.tx_isolation,@@tx_isolation;
+-----------------------+-----------------+
| @@global.tx_isolation | @@tx_isolation |
+-----------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 2 warnings (0.01 sec)
下面用例子說(shuō)明一下這四種隔離級(jí)別：
1. read-uncommitted
更改隔離級(jí)別為read-uncommitted：
mysql> set session tx_isolation='read-uncommitted';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)
首先谢肾，準(zhǔn)備一些測(cè)試數(shù)據(jù)：
mysql> select * from user;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 25 |
| 2 | lisi | 26 |
| 3 | wangwu | 27 |
| 4 | nike | 28 |
| 5 | lucy | 29 |
+----+----------+------+
5 rows in set (0.00 sec)
客戶端A：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 25 |
| 2 | lisi | 26 |
| 3 | wangwu | 27 |
| 4 | nike | 28 |
| 5 | lucy | 29 |
+----+----------+------+
5 rows in set (0.00 sec)
客戶端B：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set age=52 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
客戶端A：
mysql> select * from user;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 52 |
| 2 | lisi | 26 |
| 3 | wangwu | 27 |
| 4 | nike | 28 |
| 5 | lucy | 29 |
+----+----------+------+
5 rows in set (0.00 sec)
可以看到，客戶端B的事務(wù)還沒(méi)有提交小泉，在客戶端A的事務(wù)內(nèi)就看到了更新的數(shù)據(jù)芦疏。
客戶端B：
mysql> rollback;
Query OK, 0 rows affected (0.02 sec)
客戶端A：
mysql> select * from user;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 25 |
| 2 | lisi | 26 |
| 3 | wangwu | 27 |
| 4 | nike | 28 |
| 5 | lucy | 29 |
+----+----------+------+
5 rows in set (0.00 sec)
由于客戶端B的事務(wù)回滾，客戶端A讀取的數(shù)據(jù)又變成了原始數(shù)據(jù)微姊，因此上一次客戶端A讀取的數(shù)據(jù)變成了臟數(shù)據(jù)酸茴。在并發(fā)事務(wù)中，這種讀取數(shù)據(jù)的問(wèn)題就叫做臟讀兢交。
2. read-commited
要解決上面的問(wèn)題薪捍，可以把數(shù)據(jù)庫(kù)的隔離級(jí)別改成read-commited。
客戶端A：
mysql> set session tx_isolation='read-committed';
Query OK, 0 rows affected, 1 warning (0.00 sec)
再按照上述步驟測(cè)試一下，發(fā)現(xiàn)臟讀問(wèn)題已經(jīng)解決酪穿，在事務(wù)B沒(méi)有commit之前凳干，事務(wù)A不會(huì)讀取到臟數(shù)據(jù)。
下面演示一下不可重復(fù)讀的問(wèn)題被济。
客戶端A：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 25 |
| 2 | lisi | 26 |
| 3 | wangwu | 27 |
| 4 | nike | 28 |
| 5 | lucy | 29 |
+----+----------+------+
5 rows in set (0.00 sec)
客戶端B：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set age=52 where name='zhangsan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
客戶端A：
mysql> select * from user;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 52 |
| 2 | lisi | 26 |
| 3 | wangwu | 27 |
| 4 | nike | 28 |
| 5 | lucy | 29 |
+----+----------+------+
5 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
可以看到在客戶端B的事務(wù)提交前后救赐，客戶端A讀取到的數(shù)據(jù)不一樣了。也就是重復(fù)讀取相同的數(shù)據(jù)有不同的結(jié)果只磷。
個(gè)人理解经磅，臟讀也屬于不可重復(fù)讀的一個(gè)范疇，只是臟讀在事務(wù)B未提交之前就導(dǎo)致兩次讀取數(shù)據(jù)不一樣钮追，不可重復(fù)讀在事務(wù)B提交之后導(dǎo)致兩次讀取結(jié)果不一樣预厌。還有就是臟讀之所以叫臟數(shù)據(jù)，是因?yàn)檫@條數(shù)據(jù)沒(méi)有真正的在數(shù)據(jù)庫(kù)中保存過(guò)畏陕，這是事務(wù)的一個(gè)中間狀態(tài)配乓。而不可重復(fù)讀兩次讀取不同的數(shù)據(jù)實(shí)際都已經(jīng)存在于數(shù)據(jù)庫(kù)中了。
3. repeatable-read
要解決不可重復(fù)讀的問(wèn)題惠毁，可以將數(shù)據(jù)庫(kù)的隔離級(jí)別改為repeatable-read犹芹。
客戶端A：
mysql> set session tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)
再按照上述步驟測(cè)試一下，發(fā)現(xiàn)不可重復(fù)讀的問(wèn)題已經(jīng)解決鞠绰，在事務(wù)B沒(méi)有commit之后腰埂，事務(wù)A讀取的數(shù)據(jù)沒(méi)有變化，關(guān)閉這個(gè)事務(wù)重新打開(kāi)一個(gè)事務(wù)才會(huì)讀到更新后的數(shù)據(jù)蜈膨。
下面演示一下幻讀的問(wèn)題屿笼。
客戶端A：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 25 |
| 2 | lisi | 26 |
| 3 | wangwu | 27 |
| 4 | nike | 28 |
| 5 | lucy | 29 |
+----+----------+------+
5 rows in set (0.00 sec)
客戶端B：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user values(6,'shell',30);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
客戶端A：
mysql> insert into user values(6,'shell',30);
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
mysql> select * from user;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 25 |
| 2 | lisi | 26 |
| 3 | wangwu | 27 |
| 4 | nike | 28 |
| 5 | lucy | 29 |
+----+----------+------+
5 rows in set (0.00 sec)
可以看到，對(duì)于客戶端A來(lái)說(shuō)翁巍，命名沒(méi)有id為6的數(shù)據(jù)驴一，但是還是插入失敗，再查詢一下還是沒(méi)有啊灶壶，感覺(jué)產(chǎn)生了幻覺(jué)肝断，這就是幻讀問(wèn)題〕哿荩幻讀和不可重復(fù)讀的區(qū)別在于胸懈，不可重復(fù)讀重點(diǎn)是更新后的讀取，幻讀重點(diǎn)是插入刪除這些操作恰响，解決不可重復(fù)讀趣钱，只需要對(duì)對(duì)應(yīng)的數(shù)據(jù)行加鎖就行了。解決幻讀則需要對(duì)整張表加鎖胚宦。
如果兩個(gè)事務(wù)B沒(méi)有提交之前事務(wù)A執(zhí)行插入會(huì)如何呢首有？我們來(lái)看一下：
客戶端A：
mysql> insert into user values(6,'shell',30);
可以看到如果插入的id和事務(wù)B一樣燕垃，那么事務(wù)A的操作會(huì)被阻塞，直到事務(wù)B提交commit后绞灼，才會(huì)報(bào)錯(cuò)：
客戶端A：
mysql> insert into user values(8,'svn',32);
ERROR 1062 (23000): Duplicate entry '8' for key 'PRIMARY'
如果客戶端A插入到的數(shù)據(jù)事務(wù)B不沖突利术，那么會(huì)立即返回成功：
客戶端A：
mysql> insert into user values(9,'svn',32);
Query OK, 1 row affected (0.00 sec)
4. serializable
要解決幻讀的問(wèn)題，可以將數(shù)據(jù)庫(kù)的隔離級(jí)別改為serializable低矮。
客戶端A：
mysql> set session tx_isolation='serializable';
Query OK, 0 rows affected, 1 warning (0.00 sec)
再按照上述步驟測(cè)試一下，發(fā)現(xiàn)幻讀的問(wèn)題已經(jīng)解決被冒，當(dāng)事務(wù)B嘗試insert的事務(wù)军掂，被阻塞，也就是事務(wù)A將整張表鎖住了昨悼。直到事務(wù)A提交commit以后蝗锥，事務(wù)B的操作才會(huì)返回結(jié)果。
在這種情況下率触，只允許一個(gè)事務(wù)在執(zhí)行终议，其它事務(wù)必須等待這個(gè)事務(wù)執(zhí)行完后才能執(zhí)行。沒(méi)有并發(fā)葱蝗，只是單純的串行穴张。
5. 總結(jié)
- mysql中默認(rèn)事務(wù)隔離級(jí)別是可重復(fù)讀時(shí)并不會(huì)鎖住讀取到的行;
- 事務(wù)隔離級(jí)別為讀提交時(shí)，寫(xiě)數(shù)據(jù)只會(huì)鎖住相應(yīng)的行;
- 事務(wù)隔離級(jí)別為可重復(fù)讀時(shí)两曼，如果有索引（包括主鍵索引）的時(shí)候皂甘，以索引列為條件更新數(shù)據(jù)，會(huì)存在間隙鎖間隙鎖悼凑、行鎖偿枕、下一鍵鎖的問(wèn)題，從而鎖住一些行户辫；如果沒(méi)有索引渐夸，更新數(shù)據(jù)時(shí)會(huì)鎖住整張表;
- 事務(wù)隔離級(jí)別為串行化時(shí)，讀寫(xiě)數(shù)據(jù)都會(huì)鎖住整張表;
- 隔離級(jí)別越高渔欢，越能保證數(shù)據(jù)的完整性和一致性墓塌，但是對(duì)并發(fā)性能的影響也越大，魚(yú)和熊掌不可兼得啊膘茎。對(duì)于多數(shù)應(yīng)用程序桃纯，可以優(yōu)先考慮把數(shù)據(jù)庫(kù)系統(tǒng)的隔離級(jí)別設(shè)為Read Committed，它能夠避免臟讀取披坏，而且具有較好的并發(fā)性能态坦。盡管它會(huì)導(dǎo)致不可重復(fù)讀、幻讀這些并發(fā)問(wèn)題棒拂，在可能出現(xiàn)這類問(wèn)題的個(gè)別場(chǎng)合伞梯，可以由應(yīng)用程序采用悲觀鎖或樂(lè)觀鎖來(lái)控制玫氢。
參考：
http://www.runoob.com/mysql/mysql-transaction.html
