MySQL的innoDB鎖機制以及死鎖的處理方法

160次閱讀

共計 8812 個字符，預計需要花費 23 分鐘才能閱讀完成。

本篇內容主要講解“MySQL 的 innoDB 鎖機制以及死鎖的處理方法”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓丸趣 TV 小編來帶大家學習“MySQL 的 innoDB 鎖機制以及死鎖的處理方法”吧!

MySQL 的 nnoDB 鎖機制

InnoDB 與 MyISAM 的最大不同有兩點：一是支持事務（TRANSACTION）；二是采用了行級鎖。行級鎖與表級鎖本來就有許多不同之處，innodb 正常的 select ID from table where id=1；不會上任何鎖，接下來詳細討論 InnoDB 的鎖問題;

一：InnoDB 行鎖的介紹。

共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖, 也就是我讀取的行，你不能修改；

排他鎖（X)：允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖。也就是我更新的行，不允許其他的事務讀取和更新相同的行；

另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB 還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。

意向共享鎖（IS）：事務打算給數據行加行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的 IS 鎖。

意向排他鎖（IX）：事務打算給數據行加行排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的 IX 鎖。

意向鎖是 InnoDB 自動加的，不需用戶干預。對于 UPDATE、DELETE 和 INSERT 語句，InnoDB 會自動給涉及數據集加排他鎖（X)；對于普通 SELECT 語句，InnoDB 不會加任何鎖；事務可以通過以下語句顯示給記錄集加共享鎖或排他鎖。

共享鎖（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE；

排他鎖（X)：SELECT * FROM table_name WHERE … FOR UPDATE；

InnoDB 行鎖模式兼容性列表：

如果一個事務請求的鎖模式與當前的鎖兼容，InnoDB 就將請求的鎖授予該事務；反之，如果兩者不兼容，該事務就要等待鎖釋放。

二：關于 innodb 鎖機制，實現原理：

InnoDB 行鎖是通過給索引上的索引項加鎖來實現的，這一點 MySQL 與 Oracle 不同，后者是通過在數據塊中對相應數據行加鎖來實現的。InnoDB 這種行鎖實現特點意味著：只有通過索引條件檢索數據，InnoDB 才使用行級鎖，否則，InnoDB 將使用表鎖！索引分為主鍵索引和二級索引兩種，如果一條 sql 語句操作了主鍵索引，MySQL 就會鎖定這條主鍵索引; 如果一條語句操作了二級索引，MySQL 會先鎖定該二級索引，再鎖定相關的主鍵索引。

然后 innodb 行鎖分為三種情形：

1）Record lock：對索引項加鎖，即鎖定一條記錄。

2）Gap lock：對索引項之間的‘間隙’、對第一條記錄前的間隙或最后一條記錄后的間隙加鎖，即鎖定一個范圍的記錄，不包含記錄本身

3）Next-key Lock：鎖定一個范圍的記錄并包含記錄本身（上面兩者的結合）。

注意：InnoDB 默認級別是 repeatable-read 級別，所以下面說的都是在 RR 級別中的。

Next-Key Lock 是行鎖與間隙鎖的組合，這樣，當 InnoDB 掃描索引記錄的時候，會首先對選中的索引記錄加上行鎖（Record Lock），再對索引記錄兩邊的間隙加上間隙鎖（Gap Lock）。如果一個間隙被事務 T1 加了鎖，其它事務是不能在這個間隙插入記錄的

舉例 1：

假設我們有一張表：

+—-+——+

| id | age |

+—-+——+

| 1 | 3 |

| 2 | 6 |

| 3 | 9 |

+—-+——+

表結構如下：

CREATE TABLE `liuhe` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`age` int(11) DEFAULT NULL,

PRIMARY KEY (`id`),

KEY `keyname` (`age`)

) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=gbk ;

這樣我們 age 段的索引就分為

(negative infinity, 3],

(3,6],

(6,9],

(9,positive infinity)；

我們來看一下幾種情況：

1）當事務 A 執行以下語句：

mysql select * from liuhe where age=6 for update ;

不僅使用行鎖鎖住了相應的數據行，同時也在兩邊的區間，（3,6]和（6，9] 都加入了 gap 鎖。

這樣事務 B 就無法在這兩個區間 insert 進新數據，同時也不允許 update liuhe set age=5 where id=1（因為這也類似于在（3,6]范圍新增）, 但是事務 B 可以在兩個區間外的區間插入數據。

實驗如下：

事務 A:

mysql set autocommit=0;

Query OK, 0 rows affected (0.00 sec)

mysql select * from liuhe ;（age 上有索引）

+—-+——+

| id | age |

+—-+——+

| 1 | 3 |

| 2 | 6 |

| 3 | 9 |

+—-+——+

4 rows in set (0.00 sec)

mysql select * from liuhe where age=6 for update ;

+—-+——+

| id | age |

+—-+——+

| 2 | 6 |

+—-+——+

1 row in set (0.00 sec)

事務 B，嘗試 insert age= 5 的數據，確實有鎖等待，說明確實（3,6]上區間鎖，防止在這個區間插入；

mysql insert into liuhe (id,age) values (5,5);

查看事務狀態，發現確實是等待；

mysql select * from INNODB_TRX\G;

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

trx_id: 27162

trx_state:LOCK WAIT

trx_started: 2018-04-06 00:03:39

trx_requested_lock_id: 27162:529:4:3

trx_wait_started: 2018-04-06 00:03:39

trx_weight: 3

trx_mysql_thread_id: 46

trx_query: insert into liuhe (id,age) values (5,5)

trx_operation_state: inserting

trx_tables_in_use: 1

trx_tables_locked: 1

trx_lock_structs: 2

trx_lock_memory_bytes: 360

trx_rows_locked: 1

trx_rows_modified: 1

trx_concurrency_tickets: 0

trx_isolation_level: REPEATABLE READ

trx_unique_checks: 1

trx_foreign_key_checks: 1

trx_last_foreign_key_error: NULL

trx_adaptive_hash_latched: 0

trx_adaptive_hash_timeout: 10000

trx_is_read_only: 0

trx_autocommit_non_locking: 0

如上說明：（3,6]和（6，9] 都加入了 gap 鎖。這樣事務 B 就無法在這兩個區間 insert 進新數據, 但是事務 B 可以在兩個區間外的區間插入數據

2）當事務 A 執行如下語句：

select * from fenye where age=7 for update ;

那么就會給 (6,9] 這個區間加鎖，別的事務無法在此區間插入或更新數據。

3）當事務 A 執行:

select * from fenye where age=100 for update ;

那么加鎖區間就是(9,positive infinity)，別的事務無法在此區間插入新數據同時也不允許更新已有的數據到這個區間，也就是 update liuhe set age=19 where id= 1 是不允許的（因為這也類似于新增）。

整個舉例 1 說明：

行鎖防止別的事務修改或刪除，GAP 鎖防止別的事務新增(防止新增包括 insert 和 update 已有數據到這個范圍中），行鎖和 GAP 鎖結合形成的的 Next-Key 鎖共同解決了 RR 級別在寫數據時的部分幻讀問題，一定注意只是部分幻讀問題；

舉例 2：

假如 emp 表中只有 101 條記錄，其 empid 的值分別是 1,2,…,100,101，下面的 SQL：

Select * from emp where empid 100 for update;

是一個范圍條件的檢索，InnoDB 不僅會對符合條件的 empid 值為 101 的記錄加鎖，也會對 empid 大于 101（這些記錄并不存在）的“間隙”加鎖，這樣其他事務就不能在 empid 100 范圍 insert 數據了。

InnoDB 使用間隙鎖的目的，一方面是為了防止幻讀，以滿足相關隔離級別的要求，對于上面的例子，要是不使用間隙鎖，如果其他事務插入了 empid 大于 100 的任何記錄，那么本事務如果再次執行上述語句，就會發生幻讀

舉例 3

假如 emp 表中只有 101 條記錄，其 empid 的值分別是 1,5,7,9,10,19，那么下面的 sql:

select * from emp where empid 2 and empid 16 for update ;

那么 InnoDB 不僅會對符合條件的 empid 值為 5,7,9,10 的記錄加鎖，也會對（2，16）這個區間加“間隙”加鎖，這樣其他事務就不能在（2，16)范圍 insert 數據了, 并且也不允許更新已有的數據到這個區間；

三：關于 innodb 鎖機制需要注意的是：

1）InnoDB 行鎖是通過給索引項加鎖實現的，如果沒有索引，InnoDB 會通過隱藏的聚簇索引來對記錄加鎖。也就是說：如果不通過索引條件檢索數據，那么 InnoDB 將對表中所有數據加鎖，實際效果跟表鎖一樣。

2）由于 MySQL 的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖沖突的。說白了就是，where id=1 for update 會鎖定所有 id= 1 的數據行，如果是 where id=1 and name= liuwenhe for update, 這樣會把所有 id= 1 以及所有 name= liuwenhe 的行都上排它鎖；

3）當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB 都會使用行鎖來對數據加鎖。

4）即便在條件中使用了索引字段，但是否使用索引來檢索數據是由 MySQL 優化器通過判斷不同執行計劃的代價來決定的，如果 MySQL 認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，或者飲食轉換，或者 like 百分號在前等等，這種情況下 InnoDB 將使用表鎖，而不是行鎖。因此，在分析鎖沖突時，別忘了檢查 SQL 的執行計劃，以確認是否真正使用了索引。

四：查看 innodb 的相關鎖；

1）查詢相關的鎖：

information_schema 庫中增加了三個關于鎖的表：

innodb_trx ## 當前運行的所有事務，還有具體的語句，

innodb_locks ## 當前出現的鎖，只有

innodb_lock_waits ## 鎖等待的對應關系

看一下表結構：

root@127.0.0.1 : information_schema 13:28:38 desc innodb_locks;

+————-+———————+——+—–+———+——-+

+————-+———————+——+—–+———+——-+

| lock_id | varchar(81) | NO | | | |# 鎖 ID

| lock_mode | varchar(32) | NO | | | |# 鎖模式

| lock_type | varchar(32) | NO | | | |# 鎖類型

+————-+———————+——+—–+———+——-+

10 rows in set (0.00 sec)

root@127.0.0.1 : information_schema 13:28:56 desc innodb_lock_waits;

+——————-+————-+——+—–+———+——-+

+——————-+————-+——+—–+———+——-+

+——————-+————-+——+—–+———+——-+

4 rows in set (0.00 sec)

root@127.0.0.1 : information_schema 13:29:05 desc innodb_trx ;

+—————————-+———————+——+—–+———————+——-+

+—————————-+———————+——+—–+———————+——-+

| trx_weight | bigint(21) unsigned | NO | | 0 | |#

| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#

| trx_unique_checks | int(1) | NO | | 0 | |# 是否唯一性檢查

| trx_foreign_key_checks | int(1) | NO | | 0 | |# 是否外鍵檢查

| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#

| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#

+—————————-+———————+——+—–+———————+——-+

22 rows in set (0.01 sec)

mysql show processlist; ## 可以看出來，

或者

mysql show engine innodb status\G ## 也可以要看出相關死鎖的問題

或者：

mysql select ID,STATE from information_schema.processlist where user= system user

mysql select concat(KILL ,id,) from information_schema.processlist where user= system user

+————————+

| concat(KILL ,id,) |

+————————+

| KILL 3101; |

| KILL 2946; |

+————————+

2 rows in set (0.00 sec)

批量 kill 多個進程。

mysql select concat(KILL ,id,) from information_schema.processlist where user= root into outfile /tmp/a.txt

Query OK, 2 rows affected (0.00 sec)

五：關于死鎖：

MyISAM 表鎖是 deadlock free 的，這是因為 MyISAM 總是一次獲得所需的全部鎖，要么全部滿足，要么等待，因此不會出現死鎖。但在 InnoDB 中，除單個 SQL 組成的事務外，鎖是逐步獲得的，這就決定了在 InnoDB 中發生死鎖是可能的。

發生死鎖后，InnoDB 一般都能自動檢測到，并使一個事務釋放鎖并回退，另一個事務獲得鎖，繼續完成事務。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB 并不能完全自動檢測到死鎖，這需要通過設置鎖等待超時參數 innodb_lock_wait_timeout 來解決。需要說明的是，這個參數并不是只用來解決死鎖問題，在并發訪問比較高的情況下，如果大量事務因無法立即獲得所需的鎖而掛起，會占用大量計算機資源，造成嚴重性能問題，甚至拖跨數據庫。我們通過設置合適的鎖等待超時閾值，可以避免這種情況發生。

通常來說，死鎖都是應用設計的問題，通過調整業務流程、數據庫對象設計、事務大小，以及訪問數據庫的 SQL 語句，絕大部分死鎖都可以避免。

下面就通過實例來介紹幾種避免死鎖的常用方法。

（1）在應用中，如果不同的程序會并發存取多個表，應盡量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會。

（2）在程序以批量方式處理數據的時候，如果事先對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現死鎖的可能。

（3）在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應先申請共享鎖，更新時再申請排他鎖，因為當用戶申請排他鎖時，其他事務可能又已經獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。

如果出現死鎖，可以用 mysql show engine innodb status\G 命令來確定最后一個死鎖產生的原因。返回結果中包括死鎖相關事務的詳細信息，如引發死鎖的 SQL 語句，事務已經獲得的鎖，正在等待什么鎖，以及被回滾的事務等。據此可以分析死鎖產生的原因和改進措施。

總結：MySQL innodb 引擎的鎖機制比 myisam 引擎機制復雜，但是 innodb 引擎支持更細粒度的鎖機制，當然也會帶來更多維護的代價；然后 innodb 的行級別是借助對索引項加鎖實現的，值得注意的事如果表沒有索引，那么就會上表級別的鎖，同時借助行級鎖中 gap 鎖來解決部分幻讀的問題。只要知道 MySQL innodb 中的鎖的機制原理，那么再解決死鎖或者避免死鎖就會很容易！

到此，相信大家對“MySQL 的 innoDB 鎖機制以及死鎖的處理方法”有了更深的了解，不妨來實際操作一番吧！這里是丸趣 TV 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！

正文完