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

發布時間：2021-09-01 12:36:05 來源：億速云閱讀：172 作者：chen 欄目：MySQL數據庫

本篇內容主要講解“MySQL的innoDB鎖機制以及死鎖的處理方法”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“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行鎖模式兼容性列表：
MySQL的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鎖機制以及死鎖的處理方法”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

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

猜你喜歡

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

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

猜你喜歡

最新資訊

相關推薦

相關標簽