MySQL事務與鎖

1.          事務與鎖

1.1.         事務

事務是一組資料操作執行步驟，這些步驟被視為一個工作單元，用於對多個語句進行分組，可以在多個客戶機併發訪問同一個表中的資料時使用；所有步驟都成功或都失敗，如果所有步驟正常，則執行，如果步驟出現錯誤或不完整，則取消；事務遵從 ACID 。

透過事務，您可以將一個或多個 SQL 語句作為一個工作單元來執行，這樣，所有語句或者都成功，或者都失敗。在與其他任何事務執行的工作隔離的情況下，可能會出現這種情況。如果所有語句都成功，則可以提交該事務，以便在資料庫中永久記錄其效果。如果在事務期間出現錯誤，則可以回滾以取消它。此前已在該事務中執行的任何語句將被撤消，從而使資料庫保持開始該事務之前的狀態。

注：在 MySQL 中，只有那些使用事務儲存引擎（如 InnoDB ）的表才支援事務。這些語句不會對非事務儲存引擎所管理的表產生任何明顯影響。

MySQL 事務遵從 ACID ：

• Atomic （原子性）：所有語句作為一個單元全部成功執行或全部取消。

• Consistent （一致性）：如果資料庫在事務開始時處於一致狀態，則在執行該事務期間將保留一致狀態。

• Isolated （隔離性）：事務之間不相互影響。

• Durable （永續性）：事務成功完成後，所做的所有更改都會準確地記錄在資料庫中。所做的更改不會丟失

1.1.1          事務 SQL 控制語句

• START TRANSACTION （或 BEGIN ）：顯式開始一個新事務

• SAVEPOINT ：分配事務過程中的一個位置，以供將來引用

• COMMIT ：永久記錄當前事務所做的更改

• ROLLBACK ：取消當前事務所做的更改

• ROLLBACK TO SAVEPOINT ：取消在 savepoint 之後執行的更改

• RELEASE SAVEPOINT ：刪除 savepoint 識別符號

• SET AUTOCOMMIT ：為當前連線禁用或啟用預設 autocommit 模式

1.1.2          AUTOCOMMIT 模式

如何設定 AUTOCOMMIT 模式決定了如何以及何時開始新事務。預設情況下， AUTOCOMMIT 處於全域性啟用狀態，這意味著會強制每個 SQL 語句隱式開始一個新事務。可以透過一個配置檔案全域性禁用 AUTOCOMMIT ，也可以透過設定 autocommit 變數為每個會話禁用它。啟用 AUTOCOMMIT 會限制每個語句，並進而影響其自身事務中的事務表。這樣可以有效地防止在一個事務中執行多個語句。這意味著，您將無法透過 COMMIT 或 ROLLBACK 作為一個單元提交或回滾多個語句。有時，會將這種情況誤認為根本沒有事務。但是，情況並非如此。啟用 AUTOCOMMIT 後，每個語句仍會以原子方式執行。例如，透過在插入多個行時比較違反約束限制的效果，便可看出啟用 AUTOCOMMIT 和根本不具有事務之間的差別。在非事務表（如 MyISAM ）中，一旦發生錯誤，語句就會終止，已經插入的行會保留在該表中。而對於 InnoDB 表，已經插入的所有行都會從該表中刪除，從而不會產生任何實際影響。

AUTOCOMMIT 確定開始新事務的方式和時間；預設情況下， AUTOCOMMIT 模式處於啟用狀態：作為一個事務隱式提交每個語句；

在 my.cnf 中將 AUTOCOMMIT 模式設定為 0 ，或者 SET GLOBAL AUTOCOMMIT=0;SET SESSION AUTOCOMMIT=0; SET @@AUTOCOMMIT :=0; 則禁用 AUTOCOMMIT ，事務會跨越多個語句，需要使用 COMMIT 或 ROLLBACK 結束事務；

使用 SELECT 檢查 AUTOCOMMIT 設定：

SELECT @@AUTOCOMMIT;

1.1.3          隱式提交

COMMIT 語句始終會 顯式提交 當前事務。其他事務控制語句（例如，本幻燈片列出的語句）還具有隱式提交當前事務的作用。除了這些事務控制語句之外，其他型別的語句可能也具有隱式提交併進而終止）當前事務的作用。這些語句的行為就像在執行實際語句之前發出 COMMIT 一樣。此外，這些語句本身並非事務語句，也就是說，如果成功，則無法回滾。通常，資料定義語句、據訪問和使用者管理語句以及 Lock 語句具有這種效果。

注：有很多例外情況，而且這些語句並非都能在所有版本的伺服器上導致隱式提交。但是，建議將所有非 DML 語句都視為可導致隱式提交。有關導致隱式提交的完整語句列表，請參閱《 MySQL 參考手冊》： http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

隱式提交 會終止當前事務。用於隱式提交的 SQL 語句：

l   START TRANSACTION

l   SET AUTOCOMMIT = 1

導致提交的非事務語句：

l   資料定義語句（ ALTER 、 CREATE 和 DROP ）

l   管理語句（ GRANT 、 REVOKE 和 SET PASSWORD ）

l   Lock 語句（ LOCK TABLES 和 UNLOCK TABLES ）

導致隱式提交的語句示例：

Mysql>TRUNCATE TABLE

Mysql>LOAD DATA INFILE

1.1.4          事務儲存引擎

使用 SHOW ENGINES 列出引擎特徵：

mysql> SHOW ENGINES\G

********************* 2. row *********************

Engine: InnoDB

Support: DEFAULT

Comment: Supports transactions, row-level locking,

and foreign keys

Transactions: YES

XA: YES

Savepoints: YES

********************* 1. row *********************

Engine: MyISAM

Support: YES

Comment: MyISAM storage engine

Transactions: NO

XA: NO

Savepoints: NO

...

要確保事務儲存引擎已編譯到 MySQL 伺服器中，並且可以在執行時使用，可使用 SHOW ENGINES 語句。 Support 列中的值為 YES 或 NO ，用於指示該引擎是否可以使用。如果該值為 DISABLED 則表示該引擎存在，但已關閉。值 DEFAULT 用於指示伺服器在預設情況下使用的儲存引擎。指定為 DEFAULT 的引擎應視為可用。 Transactions 、 XA 和 Savepoints 列用於指示該儲存引擎是否支援這些功能。

 

1.2.         事務隔離級別

1.2.1          隔離級別介紹

如果一個客戶機的事務更改了資料，其他客戶機的事務是應發現這些更改還是應與其隔離，事務隔離級別可以確定同時進行的事務在訪問相同資料時彼此互動的方式。使用儲存引擎可實現隔離級別。隔離級別選項在不同的資料庫伺服器之間是不一樣的，因此， InnoDB 所實現的級別可能與其他資料庫系統所實現的級別並不完全對應。 InnoDB 可實現四種隔離級別，用於控制事務所做的更改在多大程度上可由其他同時進行的事務注意到。四種隔離級別如下：

l   READ UNCOMMITTED ：允許事務檢視其他事務所進行的未提交更改；允許發生“髒” 讀、不可重複讀和虛讀。

l   READ COMMITTED ：允許事務檢視其他事務所進行的已提交更改；允許發生不可重複讀和虛讀。未提交的更改仍不可見。

l   REPEATABLE READ ：確保每個事務的 SELECT 輸出一致， InnoDB 的預設級別 ；無論其他事務所做的更改是否已提交，兩次都會獲得相同的結果。換句話說，也就是不同的事務會對相同的資料產生一致的結果。

l   SERIALIZABLE ：將一個事務的結果與其他事務完全隔離；與 REPEATABLE READ 類似，但其限制性更強，即一個事務所選的行不能由其他事務更改，直到第一個事務完成為止。

                                             

1.2.2          設定隔離級別

系統預設事務級別為： repeatable-read

方法一、 伺服器啟動時設定級別。

–   在 mysqld 命令中使用 --transaction-isolation 選項。

–   在配置檔案中設定 transaction-isolation ：

[mysqld]

transaction-isolation = <isolation_level>

在配置檔案中或在命令列上將 <isolation_level> 值設定為：

l   READ-UNCOMMITTED

l   REPEATABLE-READ

l   SERIALIZABLE

 

方法二、使用 SET TRANSACTION ISOLATION LEVEL 語句為正在執行的伺服器設定。

– 語法示例：

SET GLOBAL TRANSACTION ISOLATION LEVEL <isolation_level>;

SET SESSION TRANSACTION ISOLATION LEVEL <isolation_level>;

<isolation_level>;

對於 SET TRANSACTION ISOLATION LEVEL 語句，將 <isolation_level> 值設定為：

l   READ UNCOMMITTED

l   REPEATABLE READ

l   SERIALIZABLE 。

此事務級別可以全域性設定，也可以按會話設定。如果沒有顯式指定，則事務隔離級別將按會話進行設定。例如，以下語句會將當前 mysql 會話的隔離級別設定為 READ COMITTED ：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

該語句相當於：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

要設定所有後續 mysql 連線的預設級別，請使用 GLOBAL 關鍵字，而不是 SESSION ：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

注：設定的全域性預設事務隔離級別適用於從設定時起所有新建立的客戶機連線。現有連線不受影響。

方法三、 SET GLOBAL TX_ISOLATION

需要 SUPER 許可權

Mysql>set global tx_isolation=’ READ-COMMITTED’

         Mysql>select @@tx_isolation;

         Mysql>show variables like ‘tx_isolation’;

 

transaction_isolation MySQL 5.7.20 引入，目的是替換即將棄用的 tx_isolation （ MySQL 8.0 ）；

 (root@localhost) [information_schema]> show variables like '%isolat%';

+-----------------------+-----------------+

| Variable_name         | Value           |

+-----------------------+-----------------+

| transaction_isolation | REPEATABLE-READ |

| tx_isolation          | REPEATABLE-READ |

+-----------------------+-----------------+

transaction_isolation was added in MySQL 5.7.20 as an alias for tx_isolation, which is now deprecated and is removed in MySQL 8.0. Applications should be adjusted to use transaction_isolation in preference to tx_isolation.

 

1.3.         鎖概念

MySQL 使用多執行緒體系結構，多個客戶機訪問一個表時會出現問題，有必要對客戶機進行協調； Lock 是一種防止出現併發問題的機制，由伺服器管理， Lock 供一個客戶機訪問，限制其他客戶機訪問， Lock 型別：共享鎖、互斥鎖

Lock 機制可以防止因多個客戶機同時訪問資料而出現的問題。該機制會以某個客戶機的身份 Lock 資料，以限制其他客戶機訪問該資料，直到釋放 Lock 為止。該 Lock 允許持有鎖的客戶機訪問資料，而限制與之爭用訪問許可權的其他客戶機可以執行的操作。 Lock 機制的結果是，將對資料的訪問序列化，這樣，在多個客戶機要執行相互衝突的操作時，每個客戶機都必須輪流等待。並非所有型別的併發訪問都會產生衝突，因此，允許客戶機訪問資料所需的 Lock 型別取決於該客戶機是希望讀取還是希望寫入：

• 如果某個客戶機希望讀取資料，則希望讀取相同資料的其他客戶機不會產生衝突，它們可以同時進行讀取。但是，如果另一個客戶機希望寫入（修改）資料，則它必須等待，直到讀取完成為止。

• 如果某個客戶機希望寫入資料，則所有其他客戶機都必須等待，直到寫入完成，而無論這些客戶機是想讀取還是想寫入。

讀取器必須阻止寫入器，但不能阻止其他讀取器。寫入器必須同時阻止讀取器和寫入器。透過讀取鎖和寫入鎖，可以強制實施這些限制。利用 Lock ，可以使客戶機進入等待狀態，直到能夠安全地訪問資料為止。藉助這種方式， Lock 可以禁止併發進行相互衝突的更改並禁止讀取正在更改的資料，從而可以防止資料損壞。

1.3.1          顯式行鎖

InnoDB 支援兩種型別的行 Lock ：

• LOCK IN SHARE MODE ：使用共享鎖 Lock 每一行

SELECT * FROM Country WHERE Code='AUS' LOCK IN SHARE MODE\G

• FOR UPDATE ：使用互斥鎖 Lock 每一行

SELECT counter_field INTO @@counter_field

FROM child_codes FOR UPDATE;

UPDATE child_codes SET counter_field =

@@counter_field + 1;

InnoDB 支援兩種 Lock 修飾符，這兩種修飾符可以新增到 SELECT 語句的末尾：

• LOCK IN SHARE MODE 子句： 共享鎖，也就是說，雖然任何其他事務都無法獲得互斥鎖，但其他事務可以同時使用共享鎖。由於正常讀取不會 Lock 任何內容，因此它們不會受 Lock 的影響。

• FOR UPDATE 子句： 使用互斥鎖來 Lock 選定的每一行，以防止其他物件獲得這些行上的任何鎖，但允許讀取這些行。

在 REPEATABLE READ 隔離級別中，可以將 LOCK IN SHARE MODE 新增到 SELECT 操作中，這樣，如果其他事務想修改選定行，則它們必須等待當前事務完成。這一點與 SERIALIZABLE 隔離級別的工作方式類似，對於該隔離級別， InnoDB 會隱式將 LOCK IN SHARE MODE 新增到 SELECT 語句中，而不會包含任何顯式 Lock 修飾符。如果選擇了在未提交的事務中修改的行，則會 Lock SELECT ，直到該事務提交為止。

1.3.2          死鎖

如果多個事務都需要訪問資料，而另一個事務已經以互斥方式 Lock 該資料，則會發生死鎖。在兩個或更多事務之間發生迴圈依賴性時。例如， T1 等待由 T2 Lock 的資源，而 T2 等待由 T3 Lock 的資源，同時 T3 又等待由 T1 Lock 的資源。 InnoDB 會檢測並中止（回滾）其中一個事務，並允許另一個事務完成。

死鎖是事務資料庫中的一個經典問題，它們並不具有危害性，除非它們經常發生，從而使您根本無法執行某些事務。死鎖發生的條件如下：

• 事務獲得多個表上的 Lock ，但順序相反。

• 諸如 UPDATE 或 SELECT ... FOR UPDATE 等語句 Lock 了一系列索引記錄和間隙，其中，每個事務因計時問題而僅獲取了部分 Lock 。

• 存在多個事務，其中每個事務都在等待另一個事務完成，從而構成一個迴圈。例如， T1 正在等待 T2 ， T2 正在等待 T3 ， T3 正在等待 T1 。

如果 InnoDB 對某個事務執行完整回滾，則該事務所設定的所有 Lock 都會被釋放。但是，如果因出現錯誤而僅回滾了一個 SQL 語句，則該語句所設定的某些 Lock 可能會保留。發生此問題的原因是， InnoDB 儲存行鎖的格式使它此後無法識別鎖和語句之間的對應關係。如果 SELECT 語句在事務中呼叫一個儲存函式，而該函式中的一個語句出現錯誤，則該語句將回滾。同時，如果此後執行 ROLLBACK ，則整個事務將回滾。

有關 InnoDB 死鎖的更多資訊，請參閱《 MySQL 參考手冊》：

http://dev.mysql.com/doc/refman/5.6/en/innodb-deadlock-detection.html 。

 

事務示例：死鎖

會話 1	會話 2
s1> START   TRANSACTION;   s1> UPDATE Country   -> SET Name = 'Sakila'   -> WHERE Code = 'SWE';
	s2> START   TRANSACTION;   s2> UPDATE Country   -> SET Name = 'World Cup Winner'   -> WHERE Code = 'ITA';
s1> DELETE   FROM Country   -> WHERE Code = 'ITA';
	s2> UPDATE   Country   -> SET population=1   -> WHERE Code = 'SWE';   ERROR 1213 (40001): Deadlock   found when trying to get lock;   try restarting transaction
Query OK, 1 row   affected (0.0 sec)

第一條 DELETE 語句在等待鎖時掛起。在執行 UPDATE 語句期間，由於兩個會話出現衝突，因此，在會話 2 中檢測到死鎖。 UPDATE 將被中止，從而允許會話 1 中的 DELETE 完成。

1.3.3          隱式鎖

MySQL 伺服器會根據所發出的命令以及所使用的儲存引擎來 Lock 表（或行）：

操作	InnoDB	MyISAM
SELECT	無鎖 *	表級別共享鎖
UPDATE/DELETE	行級別互斥鎖	表級別互斥鎖
ALTER TABLE	表級別共享鎖	表級別共享鎖

* 無鎖，除非使用了 SERIALIZABLE 級別、 LOCK IN SHARE MODE 或 FOR UPDATE

InnoDB 表會使用行級別 Lock ，以使多個會話和應用程式能夠同時讀取和寫入同一個表，而不會相互等待，也不會產生不一致的結果。對於此儲存引擎，請避免使用 LOCK TABLES 語句；它不會提供任何額外的保護，卻會減少併發性。

利用自動行級別 Lock ，可以使這些表適用於儲存最重要資料的最繁忙資料庫，同時還能簡化應用邏輯，因為您無需對錶進行 Lock 和解鎖。這樣， InnoDB 儲存引擎就成為 MySQL 5.6 中的預設設定