MySQL 事務(wù) 異常 事務(wù)隔離的級(jí)別

事務(wù)

在你操作數(shù)據(jù)庫的同時(shí)，有可能其他用戶還會(huì)不斷地對(duì)數(shù)據(jù)進(jìn)行增刪改查操作。為了避免并行進(jìn)行時(shí)出現(xiàn)混亂，就產(chǎn)生了“事務(wù)”。事務(wù)就是要保證一組數(shù)據(jù)庫操作，要么全部成功要么全部失敗，以此來保證不混亂。

事務(wù)支持是在引擎層實(shí)現(xiàn)的，MySQL支持多系統(tǒng)，不是所有引擎都支持事務(wù)。

事務(wù)的特性（ACID）

• Atomicity（原子性）：原子即不可分割，即事務(wù)是進(jìn)行數(shù)據(jù)處理的基本單位
• Consistency（一致性）：事務(wù)使數(shù)據(jù)庫從一種狀態(tài)到另一種狀態(tài)，且數(shù)據(jù)庫的約束性不被破壞
• Isolation（隔離性）：指事務(wù)之間保持獨(dú)立性，不被其他事務(wù)影響的特性。分為四個(gè)隔離等級(jí)
• Durability（持久性）：事務(wù)提交后對(duì)數(shù)據(jù)庫的修改時(shí)“持久的”，持久性是用過日志來實(shí)現(xiàn)的（回滾日志+重做日志）。即使發(fā)生故障，數(shù)據(jù)修改依然有效，因?yàn)楫?dāng)事務(wù)完成，日志就會(huì)被更新，我們依然可以恢復(fù)到最后一次成功提交事務(wù)時(shí)的狀態(tài)。

原子性是基礎(chǔ)，一致性是約束，隔離性是手段，持久性是目的。

事務(wù)操作方式

Mysql默認(rèn)參數(shù)autocommit = 1，compelation = 0

autocommit = 1：每條SQL都自動(dòng)提交

autocommit = 0：不論是否使用START TRANSACTON或BEGIN,總要使用commit才能提交。有些客戶端框架會(huì)默認(rèn)連接后先執(zhí)行set autocommit = 0的命令。這就容易造成長連接，從而造成長事務(wù)。

盡量不要長事務(wù)，詳見"可重復(fù)讀的實(shí)現(xiàn)"

compelation = 0：執(zhí)行commit僅提交事務(wù)

compelation = 1：commit時(shí)相當(dāng)于commit and chain，開啟鏈?zhǔn)绞聞?wù)，提交事務(wù)后開啟一個(gè)相同的事務(wù)

compelation = 2： commit時(shí)相當(dāng)于commit and release，提交后會(huì)自動(dòng)斷開服務(wù)器連接

在MySQL默認(rèn)的設(shè)置下

• 使用START TRANSACTION或BEGIN顯式開啟一個(gè)事務(wù)，然后再用COMMIT提交

• 使用ROLLBACK進(jìn)行回滾，或者回滾到ROLLBACK TO [SAVEPOINT]。例如，在一個(gè)事務(wù)中為有唯一約束（包含主鍵）的字段插入了 多條有相同值的該字段記錄。 那么，在mysql默認(rèn)情況下就會(huì)產(chǎn)生錯(cuò)誤，再執(zhí)行ROLLBACK后就會(huì)只有未插入前的數(shù)據(jù)。如下列代碼，執(zhí)行后前后查詢結(jié)果一致。

  SELECT * FROM t;
  ...
  BEGIN;
  INSERT INTO t id VALUES 1;
  INSERT INTO t id VALUES 1;
  ROLLBACK;
  SELECT * FROM t;
  

  需要注意的是，插入錯(cuò)誤后只有手動(dòng)ROLLBACK后才會(huì)執(zhí)行后前后查詢結(jié)果一致，不執(zhí)行ROLLBACK的結(jié)果是后面的查詢結(jié)果會(huì)比前面多一條id為1的記錄。

• 連續(xù)的BEGIN這時(shí)數(shù)據(jù)庫會(huì)隱式地提交第一個(gè)BEGIN事務(wù)并進(jìn)入第二個(gè)事務(wù)

• 而ROLLBACK是僅針對(duì)當(dāng)前事務(wù)的。比如連續(xù)進(jìn)行了兩個(gè)BEGIN事務(wù)，在第二個(gè)事務(wù)中進(jìn)行ROLLBACK，數(shù)據(jù)庫僅能回滾到第一個(gè)事務(wù)提交后的狀態(tài)

三種異常

• 臟讀：事務(wù)并行進(jìn)行時(shí)，事務(wù)A讀到了事務(wù)B中新增但未提交的內(nèi)容。讀到了其他事務(wù)回滾前的臟數(shù)據(jù)。
• 不可重復(fù)讀：事務(wù)并行進(jìn)行，事務(wù)A對(duì)表進(jìn)行查詢時(shí)事務(wù)B對(duì)表某行進(jìn)行修改，導(dǎo)致事務(wù)A發(fā)現(xiàn)兩次讀取時(shí)的結(jié)果不同。
• 幻讀：事務(wù)并行進(jìn)行，事務(wù)A進(jìn)行查詢時(shí)事務(wù)B對(duì)表新增數(shù)據(jù)，導(dǎo)致事務(wù)A兩次讀取時(shí)讀到更多的數(shù)據(jù)。

用于解決異常的四個(gè)事務(wù)隔離級(jí)別

隔離得越嚴(yán)實(shí)，效率就會(huì)越低，我們需要在生產(chǎn)中尋找一個(gè)平衡點(diǎn)，按業(yè)務(wù)決定。

對(duì)于隔離級(jí)別從低到高分別是

• 讀未提交（READ UNCOMMIT） --- 允許讀到未提交的數(shù)據(jù) --- 不使用鎖，無法避免三種異常

• 讀已提交（READ COMMIT） --- 只能讀到已提交的數(shù)據(jù) --- 其本身可避免臟讀（也是Oracle和SQL

  Server默認(rèn)的隔離級(jí)別） --- 可以編寫帶鎖的SQL語句來避免“不可重復(fù)讀”和“幻讀”

• 可重復(fù)讀（REPEATABLE READ） --- 事務(wù)在執(zhí)行期間看到的數(shù)據(jù)必須前后一致 --- 避免 “臟讀”和“不可重復(fù)讀” （是MySQL默認(rèn)的隔離級(jí)別）

• 可串行化（SERIALIZABLE）--- 將所有事務(wù)串行化，是最高隔離等級(jí)，可以避免所有異常，但是犧牲了并發(fā)性

隔離級(jí)別效用說明例題：

• 讀未提交：V1=2 V2=2 V3=2
• 讀已提交：V1=1 V2=2 V3=2
• 可重復(fù)讀：V1=1 V2=1 V3=2 (事務(wù)在執(zhí)行期間看到的數(shù)據(jù)必須一致)
• 可串行化：V1=2 V2=2 V3=2 （串行化時(shí)B在執(zhí)行“將1改為2”時(shí)會(huì)被鎖住，直到事務(wù)A被提交）

查詢mysql當(dāng)前的隔離等級(jí)

mysql> show variables like 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set (0.02 sec)

“可重復(fù)讀”的實(shí)現(xiàn)

• 隔離的實(shí)現(xiàn)主要有讀寫鎖和多版本并發(fā)處理（MVCC）兩種方式。（因?yàn)樽x寫鎖降低了事務(wù)并發(fā)效率，為了讓讀寫之間也不沖突，就發(fā)明了MVCC）

• 事務(wù)隔離的實(shí)現(xiàn)拓展閱讀見此處，有一定了解后再整理自己的版本

• 下面簡單介紹MVCC利用ReadView（快照）實(shí)現(xiàn)可重復(fù)讀

• MySQL中每條記錄在更新的時(shí)候都會(huì)同時(shí)記錄一條回滾日志

在將1依次改為2,3,4的過程中，回滾日志有如下記錄
ReadView_A：將2改為1
ReadView_B：將3改為2
ReadView_C：將4改為3
而當(dāng)前：
	值為4

這就讓同一條記錄在系統(tǒng)中可以有多個(gè)版本，這就是MVCC，此時(shí)如果有"將4改為5"的事務(wù)，它并不會(huì)影響回滾日志中的A、B、C。

• 盡量不要使用長事務(wù)：長事務(wù)意味著有很多舊的ReadView（影響回滾空間，5.5之前甚至有可能回滾空間大于真實(shí)數(shù)據(jù)），這會(huì)導(dǎo)致占用大量的存儲(chǔ)空間，而且長事務(wù)還會(huì)占用鎖資源，有可能拖垮整個(gè)庫。

• 刪除回滾日志：默認(rèn)情況下，系統(tǒng)會(huì)自行判斷，當(dāng)沒有事務(wù)需要用到這些回滾日志時(shí)就會(huì)被刪除。在上述例子中，如果修改為4之后就commit（相當(dāng)短的事務(wù)），即會(huì)造成“沒有事務(wù)需要用到這些回滾日志”，上述回滾日志就會(huì)被刪除。

  也就是說事務(wù)未提交前可以回滾，提交后回滾日志會(huì)被刪除（5.7實(shí)驗(yàn)通過）