MySQL事務(wù)與鎖的知識點(diǎn)總結

這篇文章主要介紹“事務(wù)與鎖的知識點(diǎn)總結”，在日常操作中，相信很多人在MySQL事務(wù)與鎖的知識點(diǎn)總結問(wèn)題上存在疑惑，小編查閱了各式資料，整理出簡(jiǎn)單好用的操作方法，希望對大家解答”MySQL事務(wù)與鎖的知識點(diǎn)總結”的疑惑有所幫助！接下來(lái)，請跟著(zhù)小編一起來(lái)學(xué)習吧！

MySQL事務(wù)與鎖

1.         事務(wù)與鎖

1.1.        事務(wù)

事務(wù)是一組數據操作執行步驟，這些步驟被視為一個(gè)工作單元，用于對多個(gè)語(yǔ)句進(jìn)行分組，可以在多個(gè)客戶(hù)機并發(fā)訪(fǎng)問(wèn)同一個(gè)表中的數據時(shí)使用；所有步驟都成功或都失敗，如果所有步驟正常，則執行，如果步驟出現錯誤或不完整，則取消；事務(wù)遵從 ACID。

通過(guò)事務(wù)，您可以將一個(gè)或多個(gè) SQL 語(yǔ)句作為一個(gè)工作單元來(lái)執行，這樣，所有語(yǔ)句或者都成功，或者都失敗。在與其他任何事務(wù)執行的工作隔離的情況下，可能會(huì )出現這種情況。如果所有語(yǔ)句都成功，則可以提交該事務(wù)，以便在數據庫中永久記錄其效果。如果在事務(wù)期間出現錯誤，則可以回滾以取消它。此前已在該事務(wù)中執行的任何語(yǔ)句將被撤消，從而使數據庫保持開(kāi)始該事務(wù)之前的狀態(tài)。

注：在 MySQL 中，只有那些使用事務(wù)存儲引擎（如 InnoDB）的表才支持事務(wù)。這些語(yǔ)句不會(huì )對非事務(wù)存儲引擎所管理的表產(chǎn)生任何明顯影響。

MySQL事務(wù)遵從ACID：

? Atomic（原子性）：所有語(yǔ)句作為一個(gè)單元全部成功執行或全部取消。

? Consistent（一致性）：如果數據庫在事務(wù)開(kāi)始時(shí)處于一致?tīng)顟B(tài)，則在執行該事務(wù)期間將保留一致?tīng)顟B(tài)。

? Isolated（隔離性）：事務(wù)之間不相互影響。

? Durable（持久性）：事務(wù)成功完成后，所做的所有更改都會(huì )準確地記錄在數據庫中。所做的更改不會(huì )丟失

1.1.1         事務(wù)SQL 控制語(yǔ)句

? START TRANSACTION（或BEGIN）：顯式開(kāi)始一個(gè)新事務(wù)

? SAVEPOINT：分配事務(wù)過(guò)程中的一個(gè)位置，以供將來(lái)引用

? COMMIT：永久記錄當前事務(wù)所做的更改

? ROLLBACK：取消當前事務(wù)所做的更改

? ROLLBACK TO SAVEPOINT：取消在savepoint 之后執行的更改

? RELEASE SAVEPOINT：刪除savepoint 標識符

? SET AUTOCOMMIT：為當前連接禁用或啟用默認autocommit 模式

1.1.2         AUTOCOMMIT 模式

如何設置 AUTOCOMMIT 模式?jīng)Q定了如何以及何時(shí)開(kāi)始新事務(wù)。默認情況下，AUTOCOMMIT 處于全局啟用狀態(tài)，這意味著(zhù)會(huì )強制每個(gè) SQL 語(yǔ)句隱式開(kāi)始一個(gè)新事務(wù)?？梢酝ㄟ^(guò)一個(gè)配置文件全局禁用 AUTOCOMMIT，也可以通過(guò)設置 autocommit 變量為每個(gè)會(huì )話(huà)禁用它。啟用 AUTOCOMMIT 會(huì )限制每個(gè)語(yǔ)句，并進(jìn)而影響其自身事務(wù)中的事務(wù)表。這樣可以有效地防止在一個(gè)事務(wù)中執行多個(gè)語(yǔ)句。這意味著(zhù)，您將無(wú)法通過(guò) COMMIT 或 ROLLBACK 作為一個(gè)單元提交或回滾多個(gè)語(yǔ)句。有時(shí)，會(huì )將這種情況誤認為根本沒(méi)有事務(wù)。但是，情況并非如此。啟用 AUTOCOMMIT 后，每個(gè)語(yǔ)句仍會(huì )以原子方式執行。例如，通過(guò)在插入多個(gè)行時(shí)比較違反約束限制的效果，便可看出啟用 AUTOCOMMIT 和根本不具有事務(wù)之間的差別。在非事務(wù)表（如 MyISAM）中，一旦發(fā)生錯誤，語(yǔ)句就會(huì )終止，已經(jīng)插入的行會(huì )保留在該表中。而對于 InnoDB 表，已經(jīng)插入的所有行都會(huì )從該表中刪除，從而不會(huì )產(chǎn)生任何實(shí)際影響。

AUTOCOMMIT確定開(kāi)始新事務(wù)的方式和時(shí)間；默認情況下， AUTOCOMMIT 模式處于啟用狀態(tài)：作為一個(gè)事務(wù)隱式提交每個(gè)語(yǔ)句；

在my.cnf中將 AUTOCOMMIT 模式設置為 0，或者SET GLOBAL AUTOCOMMIT=0;SET SESSION AUTOCOMMIT=0; SET @@AUTOCOMMIT :=0; 則禁用 AUTOCOMMIT，事務(wù)會(huì )跨越多個(gè)語(yǔ)句，需要使用 COMMIT 或 ROLLBACK 結束事務(wù)；

使用 SELECT 檢查 AUTOCOMMIT 設置：

SELECT @@AUTOCOMMIT;

1.1.3         隱式提交

COMMIT 語(yǔ)句始終會(huì )顯式提交當前事務(wù)。其他事務(wù)控制語(yǔ)句（例如，本幻燈片列出的語(yǔ)句）還具有隱式提交當前事務(wù)的作用。除了這些事務(wù)控制語(yǔ)句之外，其他類(lèi)型的語(yǔ)句可能也具有隱式提交并進(jìn)而終止）當前事務(wù)的作用。這些語(yǔ)句的行為就像在執行實(shí)際語(yǔ)句之前發(fā)出 COMMIT 一樣。此外，這些語(yǔ)句本身并非事務(wù)語(yǔ)句，也就是說(shuō)，如果成功，則無(wú)法回滾。通常，數據定義語(yǔ)句、據訪(fǎng)問(wèn)和用戶(hù)管理語(yǔ)句以及Lock語(yǔ)句具有這種效果。

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

隱式提交會(huì )終止當前事務(wù)。用于隱式提交的 SQL 語(yǔ)句：

l  START TRANSACTION

l  SET AUTOCOMMIT = 1

導致提交的非事務(wù)語(yǔ)句：

l  數據定義語(yǔ)句（ALTER、 CREATE 和 DROP）

l  管理語(yǔ)句（GRANT、 REVOKE 和 SET PASSWORD）

l  Lock語(yǔ)句（LOCK TABLES 和 UNLOCK TABLES）

導致隱式提交的語(yǔ)句示例：

Mysql>TRUNCATE TABLE

Mysql>LOAD DATA INFILE

1.1.4         事務(wù)存儲引擎

使用 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

...

要確保事務(wù)存儲引擎已編譯到 MySQL 服務(wù)器中，并且可以在運行時(shí)使用，可使用 SHOW ENGINES 語(yǔ)句。Support 列中的值為 YES 或 NO，用于指示該引擎是否可以使用。如果該值為DISABLED則表示該引擎存在，但已關(guān)閉。值 DEFAULT 用于指示服務(wù)器在默認情況下使用的存儲引擎。指定為 DEFAULT 的引擎應視為可用。 Transactions、 XA 和Savepoints 列用于指示該存儲引擎是否支持這些功能。

1.2.        事務(wù)隔離級別

1.2.1         隔離級別介紹

如果一個(gè)客戶(hù)機的事務(wù)更改了數據，其他客戶(hù)機的事務(wù)是應發(fā)現這些更改還是應與其隔離，事務(wù)隔離級別可以確定同時(shí)進(jìn)行的事務(wù)在訪(fǎng)問(wèn)相同數據時(shí)彼此交互的方式。使用存儲引擎可實(shí)現隔離級別。隔離級別選項在不同的數據庫服務(wù)器之間是不一樣的，因此， InnoDB 所實(shí)現的級別可能與其他數據庫系統所實(shí)現的級別并不完全對應。InnoDB 可實(shí)現四種隔離級別，用于控制事務(wù)所做的更改在多大程度上可由其他同時(shí)進(jìn)行的事務(wù)注意到。四種隔離級別如下：

l  READ UNCOMMITTED：允許事務(wù)查看其他事務(wù)所進(jìn)行的未提交更改；允許發(fā)生“臟” 讀、不可重復讀和虛讀。

l  READ COMMITTED：允許事務(wù)查看其他事務(wù)所進(jìn)行的已提交更改；允許發(fā)生不可重復讀和虛讀。未提交的更改仍不可見(jiàn)。

l  REPEATABLE READ：確保每個(gè)事務(wù)的 SELECT 輸出一致，InnoDB 的默認級別；無(wú)論其他事務(wù)所做的更改是否已提交，兩次都會(huì )獲得相同的結果。換句話(huà)說(shuō)，也就是不同的事務(wù)會(huì )對相同的數據產(chǎn)生一致的結果。

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

1.2.2         設置隔離級別

系統默認事務(wù)級別為：repeatable-read

方法一、 服務(wù)器啟動(dòng)時(shí)設置級別。

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

–  在配置文件中設置transaction-isolation：

[mysqld]

transaction-isolation = <isolation_level>

在配置文件中或在命令行上將<isolation_level> 值設置為：

l  READ-UNCOMMITTED

l  READ-COMMITTED

l  REPEATABLE-READ

l  SERIALIZABLE

方法二、使用SET TRANSACTION ISOLATION LEVEL 語(yǔ)句為正在運行的服務(wù)器設置。

– 語(yǔ)法示例：

SET GLOBAL TRANSACTION ISOLATION LEVEL <isolation_level>;

SET SESSION TRANSACTION ISOLATION LEVEL <isolation_level>;

SET TRANSACTION ISOLATION LEVEL <isolation_level>;

對于SET TRANSACTION ISOLATION LEVEL 語(yǔ)句，將<isolation_level> 值設置為：

l  READ UNCOMMITTED

l  READ COMMITTED

l  REPEATABLE READ

l  SERIALIZABLE。

此事務(wù)級別可以全局設置，也可以按會(huì )話(huà)設置。如果沒(méi)有顯式指定，則事務(wù)隔離級別將按會(huì )話(huà)進(jìn)行設置。例如，以下語(yǔ)句會(huì )將當前mysql 會(huì )話(huà)的隔離級別設置為READ COMITTED：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

該語(yǔ)句相當于：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

要設置所有后續mysql 連接的默認級別，請使用GLOBAL 關(guān)鍵字，而不是SESSION：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

注：設置的全局默認事務(wù)隔離級別適用于從設置時(shí)起所有新建立的客戶(hù)機連接?，F有連接不受影響。

方法三、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 使用多線(xiàn)程體系結構，多個(gè)客戶(hù)機訪(fǎng)問(wèn)一個(gè)表時(shí)會(huì )出現問(wèn)題，有必要對客戶(hù)機進(jìn)行協(xié)調；Lock是一種防止出現并發(fā)問(wèn)題的機制，由服務(wù)器管理，Lock供一個(gè)客戶(hù)機訪(fǎng)問(wèn)，限制其他客戶(hù)機訪(fǎng)問(wèn)，Lock類(lèi)型：共享鎖、互斥鎖

Lock機制可以防止因多個(gè)客戶(hù)機同時(shí)訪(fǎng)問(wèn)數據而出現的問(wèn)題。該機制會(huì )以某個(gè)客戶(hù)機的身份Lock數據，以限制其他客戶(hù)機訪(fǎng)問(wèn)該數據，直到釋放Lock為止。該Lock允許持有鎖的客戶(hù)機訪(fǎng)問(wèn)數據，而限制與之爭用訪(fǎng)問(wèn)權限的其他客戶(hù)機可以執行的操作。Lock機制的結果是，將對數據的訪(fǎng)問(wèn)序列化，這樣，在多個(gè)客戶(hù)機要執行相互沖突的操作時(shí)，每個(gè)客戶(hù)機都必須輪流等待。并非所有類(lèi)型的并發(fā)訪(fǎng)問(wèn)都會(huì )產(chǎn)生沖突，因此，允許客戶(hù)機訪(fǎng)問(wèn)數據所需的Lock類(lèi)型取決于該客戶(hù)機是希望讀取還是希望寫(xiě)入：

? 如果某個(gè)客戶(hù)機希望讀取數據，則希望讀取相同數據的其他客戶(hù)機不會(huì )產(chǎn)生沖突，它們可以同時(shí)進(jìn)行讀取。但是，如果另一個(gè)客戶(hù)機希望寫(xiě)入（修改）數據，則它必須等待，直到讀取完成為止。

? 如果某個(gè)客戶(hù)機希望寫(xiě)入數據，則所有其他客戶(hù)機都必須等待，直到寫(xiě)入完成，而無(wú)論這些客戶(hù)機是想讀取還是想寫(xiě)入。

讀取器必須阻止寫(xiě)入器，但不能阻止其他讀取器。寫(xiě)入器必須同時(shí)阻止讀取器和寫(xiě)入器。通過(guò)讀取鎖和寫(xiě)入鎖，可以強制實(shí)施這些限制。利用Lock，可以使客戶(hù)機進(jìn)入等待狀態(tài)，直到能夠安全地訪(fǎng)問(wèn)數據為止。借助這種方式，Lock可以禁止并發(fā)進(jìn)行相互沖突的更改并禁止讀取正在更改的數據，從而可以防止數據損壞。

1.3.1         顯式行鎖

InnoDB 支持兩種類(lèi)型的行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 語(yǔ)句的末尾：

? LOCK IN SHARE MODE 子句： 共享鎖，也就是說(shuō)，雖然任何其他事務(wù)都無(wú)法獲得互斥鎖，但其他事務(wù)可以同時(shí)使用共享鎖。由于正常讀取不會(huì )Lock任何內容，因此它們不會(huì )受Lock的影響。

? FOR UPDATE 子句： 使用互斥鎖來(lái)Lock選定的每一行，以防止其他對象獲得這些行上的任何鎖，但允許讀取這些行。

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

1.3.2         死鎖

如果多個(gè)事務(wù)都需要訪(fǎng)問(wèn)數據，而另一個(gè)事務(wù)已經(jīng)以互斥方式Lock該數據，則會(huì )發(fā)生死鎖。在兩個(gè)或更多事務(wù)之間發(fā)生循環(huán)依賴(lài)性時(shí)。例如， T1 等待由 T2 Lock的資源，而 T2 等待由 T3 Lock的資源，同時(shí) T3 又等待由 T1 Lock的資源。InnoDB 會(huì )檢測并中止（回滾）其中一個(gè)事務(wù)，并允許另一個(gè)事務(wù)完成。

死鎖是事務(wù)數據庫中的一個(gè)經(jīng)典問(wèn)題，它們并不具有危害性，除非它們經(jīng)常發(fā)生，從而使您根本無(wú)法運行某些事務(wù)。死鎖發(fā)生的條件如下：

? 事務(wù)獲得多個(gè)表上的Lock，但順序相反。

? 諸如 UPDATE 或 SELECT ... FOR UPDATE 等語(yǔ)句Lock了一系列索引記錄和間隙，其中，每個(gè)事務(wù)因計時(shí)問(wèn)題而僅獲取了部分Lock。

? 存在多個(gè)事務(wù)，其中每個(gè)事務(wù)都在等待另一個(gè)事務(wù)完成，從而構成一個(gè)循環(huán)。例如，T1 正在等待 T2， T2 正在等待 T3， T3 正在等待 T1。

如果 InnoDB 對某個(gè)事務(wù)執行完整回滾，則該事務(wù)所設置的所有Lock都會(huì )被釋放。但是，如果因出現錯誤而僅回滾了一個(gè) SQL 語(yǔ)句，則該語(yǔ)句所設置的某些Lock可能會(huì )保留。發(fā)生此問(wèn)題的原因是， InnoDB 存儲行鎖的格式使它此后無(wú)法識別鎖和語(yǔ)句之間的對應關(guān)系。如果 SELECT 語(yǔ)句在事務(wù)中調用一個(gè)存儲函數，而該函數中的一個(gè)語(yǔ)句出現錯誤，則該語(yǔ)句將回滾。同時(shí)，如果此后執行 ROLLBACK，則整個(gè)事務(wù)將回滾。

有關(guān) InnoDB 死鎖的更多信息，請參閱《MySQL 參考手冊》：

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

事務(wù)示例：死鎖

第一條 DELETE 語(yǔ)句在等待鎖時(shí)掛起。在執行 UPDATE 語(yǔ)句期間，由于兩個(gè)會(huì )話(huà)出現沖突，因此，在會(huì )話(huà) 2 中檢測到死鎖。 UPDATE 將被中止，從而允許會(huì )話(huà) 1 中的 DELETE完成。

1.3.3         隱式鎖

MySQL 服務(wù)器會(huì )根據所發(fā)出的命令以及所使用的存儲引擎來(lái)Lock表（或行）：

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

InnoDB 表會(huì )使用行級別Lock，以使多個(gè)會(huì )話(huà)和應用程序能夠同時(shí)讀取和寫(xiě)入同一個(gè)表，而不會(huì )相互等待，也不會(huì )產(chǎn)生不一致的結果。對于此存儲引擎，請避免使用 LOCK TABLES 語(yǔ)句；它不會(huì )提供任何額外的保護，卻會(huì )減少并發(fā)性。

利用自動(dòng)行級別Lock，可以使這些表適用于存儲最重要數據的最繁忙數據庫，同時(shí)還能簡(jiǎn)化應用邏輯，因為您無(wú)需對表進(jìn)行Lock和解鎖。這樣， InnoDB 存儲引擎就成為MySQL 5.6 中的默認設置