事務(wù)

InnoDB事務(wù)模型目標(biāo)：將 多版本數(shù)據(jù)庫 的特性與傳統(tǒng)的 兩階段鎖定 相結(jié)合

事務(wù)隔離級別（Transaction Isolation Levels）

隔離是縮寫ACID中的I；隔離級別是一種設(shè)置，用于在多個事務(wù)同時進行更改和執(zhí)行查詢時結(jié)果的 可靠性、高效性、一致性 和 可重復(fù)性 之間的平衡

• REPEATABLE READ（默認(rèn)級別）

  • 同一事務(wù)中的一致性讀，將讀取第一次讀取建立的快照；（一致性非鎖定讀取）

  • 對于鎖定讀取 （SELECT帶有FOR UPDATE或LOCK IN SHARE MODE）， UPDATE和 DELETE語句，鎖定取決于該語句使用的是具有 唯一搜索條件的唯一索引 還是 范圍類型搜索條件 。

    • 對于具有 唯一搜索條件的唯一索引 ，InnoDB僅鎖定找到的索引記錄，而不鎖定其前的間隙。
    • 對于其他 搜索條件 ，InnoDB 使用間隙鎖或下一鍵鎖定鎖定掃描的索引范圍，以阻止其他會話插入該范圍所覆蓋的間隙。

    例：從此表開始
    CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
    INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
    COMMIT;

    # Session A
    START TRANSACTION;
    UPDATE t SET b = 5 WHERE b = 3;

    # Session B
    UPDATE t SET b = 4 WHERE b = 2;

    當(dāng)使用默認(rèn)REPEATABLE READ 隔離級別時,第一個UPDATE將在讀取的每一行上獲取一個x鎖，并且不會釋放其中的任何一個：
    x-lock(1,2); retain x-lock
    x-lock(2,3); update(2,3) to (2,5); retain x-lock
    x-lock(3,2); retain x-lock
    x-lock(4,3); update(4,3) to (4,5); retain x-lock
    x-lock(5,2); retain x-lock

    注：請考慮為什么我們實際應(yīng)用中強烈要求通過主鍵更新或刪除？

• READ COMMITTED

  • 同一事務(wù)中，每個一致性讀都將設(shè)置并讀取其自己的新快照

• READ UNCOMMITTED

  • SELECT語句以非鎖定方式執(zhí)行，但是可能會使用行的早期版本。因此，使用此隔離級別，此類讀取不一致。這也稱為 臟讀。

• SERIALIZABLE
  此級別類似于REPEATABLE READ，

  • 如果 autocommit禁用，InnoDB將所有普通SELECT 語句隱式轉(zhuǎn)換為SELECT ... LOCK IN SHARE MODE。
  • 如果 autocommit啟用，則 SELECT是其自身的事務(wù)。

  因此，它被認(rèn)為是只讀的，如果作為一致（非鎖定）讀取執(zhí)行并且不需要阻塞其他事務(wù)，則可以序列化。

自動提交、提交與回滾

• autocommit 0關(guān)閉，1開啟（默認(rèn)）；
• 開啟事務(wù) START TRANSACTION或 BEGIN
• 關(guān)閉或回滾事務(wù) COMMIT或 ROLLBACK

一致性非鎖定讀取

• 一致性讀意味著InnoDB數(shù)據(jù)庫會在一個時間點使用多版本的快照。
• 一致性讀不會在它訪問的表上設(shè)置任何鎖，因此其他會話可以在對該表執(zhí)行一致性讀的同時自由地修改那些表。

一致性讀

一種讀取操作，使用 快照信息可基于某個時間點顯示查詢結(jié)果，而不管同時運行的其他事務(wù)執(zhí)行的更改如何。如果查詢的數(shù)據(jù)已被另一個事務(wù)更改，則將根據(jù)撤消日志的內(nèi)容來重建原始數(shù)據(jù) 。通過強制事務(wù)等待其他事務(wù)完成，該技術(shù)避免了一些可以減少并發(fā)性的鎖定問題。

一致讀取不適用于某些DDL語句：
- 一致讀取無法解決問題DROP TABLE，因為MySQL無法使用已刪除InnoDB的表并破壞該表。
- 一致讀取無法解決問題 ALTER TABLE，因為該語句將創(chuàng)建原始表的臨時副本，并在構(gòu)建臨時副本時刪除原始表。當(dāng)您重新發(fā)出事務(wù)中的一致讀取時，新表中的行不可見，因為在獲取事務(wù)快照時這些行不存在。在這種情況下，事務(wù)返回一個錯誤： ER_TABLE_DEF_CHANGED， “ 表的定義發(fā)生了變化，請重試交易 ”。

讀取的類型因選擇子句（如INSERT INTO ... SELECT，）中的選擇而有所不同 UPDATE ... (SELECT)，并且 CREATE TABLE ... SELECT未指定FOR UPDATE或LOCK IN SHARE MODE：
- 默認(rèn)情況下，InnoDB使用更強的鎖定，并且SELECT部分的行為類似于 READ COMMITTED，其中每次一致的讀?。词故窃谕皇聞?wù)中）也會設(shè)置并讀取自己的新快照。
- 要在這種情況下，讀一致性，使 innodb_locks_unsafe_for_binlog 選項和事務(wù)的隔離級別設(shè)置為 READ UNCOMMITTED， READ COMMITTED或 REPEATABLE READ（比其他任何東西 SERIALIZABLE）。在這種情況下，不會對從選定表讀取的行設(shè)置鎖定。

鎖定讀取

如果查詢數(shù)據(jù)，然后在同一事務(wù)中插入或更新相關(guān)數(shù)據(jù)，則常規(guī)SELECT 語句不能提供足夠的保護。

• 共享鎖，SELECT ... LOCK IN SHARE MODE
• 排他鎖，SELECT ... FOR UPDATE

注： 只有在禁用自動提交時（以 START TRANSACTION 開始事務(wù)或通過設(shè)置 autocommit 為0，才可以進行鎖定讀?。?/p>

除非在子查詢中也指定了鎖定讀取子句，否則外部語句中的鎖定讀取子句不會鎖定嵌套子查詢中表的行。例如，以下語句不會鎖定t2 表中的行。

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

要鎖定t2中的行，請向子查詢添加鎖定的子語句：

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

示例

1. 使用一致性讀來查詢表 PARENT并驗證父行是否存在?？梢园踩貙⒆有胁迦氡砀?CHILD嗎？

不可以，因為其他會話可能會在您SELECT和您之間的時刻刪除父行 INSERT，而您卻沒有意識到。為避免此問題，請執(zhí)行以下 SELECT使用LOCK IN SHARE MODE：
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

2. 一個表中的整數(shù)計數(shù)器字段，該字段CHILD_CODES用于為每行數(shù)據(jù)分配唯一標(biāo)識符 CHILD。 不要使用一致讀取或共享模式讀取來讀取計數(shù)器的當(dāng)前值 ，因為數(shù)據(jù)庫的兩個用戶可能會看到該計數(shù)器的相同值，并且如果兩個事務(wù)嘗試使用以下方法添加行，則會發(fā)生重復(fù)鍵錯誤。

在這里，LOCK IN SHARE MODE這不是一個好的解決方案，因為如果兩個用戶同時讀取計數(shù)器，則其中至少有一個在嘗試更新計數(shù)器時會陷入死鎖狀態(tài)。
SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

幻影行

當(dāng)同一查詢在不同時間生成不同的行集時，在事務(wù)內(nèi)就會發(fā)生 所謂的幻像問題

例: 如果一個SELECT執(zhí)行兩次，但是第二次返回的行卻不是第一次返回的行，則該行是“ phantom ”行

思考：REPEATABLE READ隔離級別時，哪種一致性讀會出現(xiàn)幻讀？

示例

假設(shè)id該child表的列上有一個索引，并且您想要讀取并鎖定該表中所有標(biāo)識符值大于100的行，以期稍后更新所選行中的某些列：

SELECT * FROM child WHERE id > 100 FOR UPDATE;

死鎖

死鎖是指由于每個事務(wù)都持有對方需要的鎖而無法進行其他事務(wù)的情況。因為這兩個事務(wù)都在等待資源變得可用，所以都不會釋放它持有的鎖。

避免死鎖

• 為了減少死鎖的可能性，請 使用事務(wù) 而不是LOCK TABLES語句；
• 保持用于插入或更新數(shù)據(jù)的 事務(wù)足夠小 ，以使其長時間不保持打開狀態(tài)；
• 當(dāng)不同的事務(wù)更新多個表或大范圍的行時，SELECT ... FOR UPDATE在每個事務(wù)中 使用相同的操作順序；
• 在SELECT ... FOR UPDATE和 UPDATE ... WHERE 語句中 使用的列上創(chuàng)建索引。

注： 死鎖的可能性不受隔離級別的影響，因為隔離級別更改了讀取操作的行為，而死鎖則是由于寫入操作而發(fā)生的