MySQL的innoDB鎖機(jī)制以及死鎖的處理方法

本篇內(nèi)容主要講解“MySQL的innoDB鎖機(jī)制以及死鎖的處理方法”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“MySQL的innoDB鎖機(jī)制以及死鎖的處理方法”吧!

我們注重客戶提出的每個(gè)要求，我們充分考慮每一個(gè)細(xì)節(jié)，我們積極的做好成都網(wǎng)站設(shè)計(jì)、做網(wǎng)站服務(wù)，我們努力開拓更好的視野，通過(guò)不懈的努力，創(chuàng)新互聯(lián)公司贏得了業(yè)內(nèi)的良好聲譽(yù)，這一切，也不斷的激勵(lì)著我們更好的服務(wù)客戶。主要業(yè)務(wù)：網(wǎng)站建設(shè),網(wǎng)站制作,網(wǎng)站設(shè)計(jì),微信小程序定制開發(fā),網(wǎng)站開發(fā),技術(shù)開發(fā)實(shí)力，DIV+CSS，PHP及ASP，ASP.Net，SQL數(shù)據(jù)庫(kù)的技術(shù)開發(fā)工程師。

MySQL的nnoDB鎖機(jī)制

InnoDB與MyISAM的最大不同有兩點(diǎn)：一是支持事務(wù)（TRANSACTION）；二是采用了行級(jí)鎖。行級(jí)鎖與表級(jí)鎖本來(lái)就有許多不同之處，innodb正常的select ID from table where id=1；不會(huì)上任何鎖，接下來(lái)詳細(xì)討論InnoDB的鎖問(wèn)題;

一：InnoDB行鎖的介紹。

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

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

另外，為了允許行鎖和表鎖共存，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB還有兩種內(nèi)部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。

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

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

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

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

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

InnoDB行鎖模式兼容性列表：
MySQL的innoDB鎖機(jī)制以及死鎖的處理方法

如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前的鎖兼容，InnoDB就將請(qǐng)求的鎖授予該事務(wù)；反之，如果兩者不兼容，該事務(wù)就要等待鎖釋放。

二：關(guān)于innodb鎖機(jī)制，實(shí)現(xiàn)原理：

InnoDB行鎖是通過(guò)給索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的，這一點(diǎn)MySQL與Oracle不同，后者是通過(guò)在數(shù)據(jù)塊中對(duì)相應(yīng)數(shù)據(jù)行加鎖來(lái)實(shí)現(xiàn)的。InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著：只有通過(guò)索引條件檢索數(shù)據(jù)，InnoDB才使用行級(jí)鎖，否則，InnoDB將使用表鎖！索引分為主鍵索引和二級(jí)索引兩種，如果一條sql語(yǔ)句操作了主鍵索引，MySQL就會(huì)鎖定這條主鍵索引;如果一條語(yǔ)句操作了二級(jí)索引，MySQL會(huì)先鎖定該二級(jí)索引，再鎖定相關(guān)的主鍵索引。

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

1）Record lock ：對(duì)索引項(xiàng)加鎖，即鎖定一條記錄。

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

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

注意：InnoDB默認(rèn)級(jí)別是repeatable-read級(jí)別，所以下面說(shuō)的都是在RR級(jí)別中的。

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

舉例1：

假設(shè)我們有一張表：

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

| id | age |

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

| 1 | 3 |

| 2 | 6 |

| 3 | 9 |

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

表結(jié)構(gòu)如下：

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)；

我們來(lái)看一下幾種情況：

1）當(dāng)事務(wù)A執(zhí)行以下語(yǔ)句：

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

不僅使用行鎖鎖住了相應(yīng)的數(shù)據(jù)行，同時(shí)也在兩邊的區(qū)間，（3,6]和（6，9] 都加入了gap鎖。

這樣事務(wù)B就無(wú)法在這兩個(gè)區(qū)間insert進(jìn)新數(shù)據(jù)，同時(shí)也不允許 update liuhe set age=5 where id=1（因?yàn)檫@也類似于在（3,6]范圍新增）,但是事務(wù)B可以在兩個(gè)區(qū)間外的區(qū)間插入數(shù)據(jù)。

實(shí)驗(yàn)如下：

事務(wù)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)

事務(wù)B，嘗試insert age=5的數(shù)據(jù)，確實(shí)有鎖等待，說(shuō)明確實(shí)（3,6]上區(qū)間鎖，防止在這個(gè)區(qū)間插入；

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

查看事務(wù)狀態(tài)，發(fā)現(xiàn)確實(shí)是等待；

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

如上說(shuō)明：（3,6]和（6，9] 都加入了gap鎖。這樣事務(wù)B就無(wú)法在這兩個(gè)區(qū)間insert進(jìn)新數(shù)據(jù),但是事務(wù)B可以在兩個(gè)區(qū)間外的區(qū)間插入數(shù)據(jù)

2）當(dāng)事務(wù)A執(zhí)行如下語(yǔ)句：

select * from fenye where age=7 for update ;

那么就會(huì)給(6,9]這個(gè)區(qū)間加鎖，別的事務(wù)無(wú)法在此區(qū)間插入或更新數(shù)據(jù)。

3）當(dāng)事務(wù)A執(zhí)行:

select * from fenye where age=100 for update ;

那么加鎖區(qū)間就是(9,positive infinity)，別的事務(wù)無(wú)法在此區(qū)間插入新數(shù)據(jù)同時(shí)也不允許更新已有的數(shù)據(jù)到這個(gè)區(qū)間，也就是 update liuhe set age=19 where id=1是不允許的（因?yàn)檫@也類似于新增）。

整個(gè)舉例1說(shuō)明：

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

舉例2：

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

Select * from emp where empid > 100 for update;

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

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

舉例3

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

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

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

三：關(guān)于innodb鎖機(jī)制需要注意的是：

1）InnoDB行鎖是通過(guò)給索引項(xiàng)加鎖實(shí)現(xiàn)的，如果沒有索引，InnoDB會(huì)通過(guò)隱藏的聚簇索引來(lái)對(duì)記錄加鎖。也就是說(shuō)：如果不通過(guò)索引條件檢索數(shù)據(jù)，那么InnoDB將對(duì)表中所有數(shù)據(jù)加鎖，實(shí)際效果跟表鎖一樣。

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

3）當(dāng)表有多個(gè)索引的時(shí)候，不同的事務(wù)可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會(huì)使用行鎖來(lái)對(duì)數(shù)據(jù)加鎖。

4）即便在條件中使用了索引字段，但是否使用索引來(lái)檢索數(shù)據(jù)是由MySQL優(yōu)化器通過(guò)判斷不同執(zhí)行計(jì)劃的代價(jià)來(lái)決定的，如果MySQL認(rèn)為全表掃描效率更高，比如對(duì)一些很小的表，它就不會(huì)使用索引，或者飲食轉(zhuǎn)換，或者like百分號(hào)在前等等，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖沖突時(shí)，別忘了檢查SQL的執(zhí)行計(jì)劃，以確認(rèn)是否真正使用了索引。

四：查看innodb的相關(guān)鎖；

1）查詢相關(guān)的鎖：

information_schema 庫(kù)中增加了三個(gè)關(guān)于鎖的表：

innodb_trx ## 當(dāng)前運(yùn)行的所有事務(wù) ，還有具體的語(yǔ)句，

innodb_locks ## 當(dāng)前出現(xiàn)的鎖，只有

innodb_lock_waits ## 鎖等待的對(duì)應(yīng)關(guān)系

看一下表結(jié)構(gòu)：

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; ##可以看出來(lái)，

或者

mysql> show engine innodb status\G ##也可以要看出相關(guān)死鎖的問(wèn)題

或者：

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多個(gè)進(jìn)程。

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)

五：關(guān)于死鎖：

MyISAM表鎖是deadlock free的，這是因?yàn)镸yISAM總是一次獲得所需的全部鎖，要么全部滿足，要么等待，因此不會(huì)出現(xiàn)死鎖。但在InnoDB中，除單個(gè)SQL組成的事務(wù)外，鎖是逐步獲得的，這就決定了在InnoDB中發(fā)生死鎖是可能的。

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

通常來(lái)說(shuō)，死鎖都是應(yīng)用設(shè)計(jì)的問(wèn)題，通過(guò)調(diào)整業(yè)務(wù)流程、數(shù)據(jù)庫(kù)對(duì)象設(shè)計(jì)、事務(wù)大小，以及訪問(wèn)數(shù)據(jù)庫(kù)的SQL語(yǔ)句，絕大部分死鎖都可以避免。

下面就通過(guò)實(shí)例來(lái)介紹幾種避免死鎖的常用方法。

（1）在應(yīng)用中，如果不同的程序會(huì)并發(fā)存取多個(gè)表，應(yīng)盡量約定以相同的順序來(lái)訪問(wèn)表，這樣可以大大降低產(chǎn)生死鎖的機(jī)會(huì)。

（2）在程序以批量方式處理數(shù)據(jù)的時(shí)候，如果事先對(duì)數(shù)據(jù)排序，保證每個(gè)線程按固定的順序來(lái)處理記錄，也可以大大降低出現(xiàn)死鎖的可能。

（3）在事務(wù)中，如果要更新記錄，應(yīng)該直接申請(qǐng)足夠級(jí)別的鎖，即排他鎖，而不應(yīng)先申請(qǐng)共享鎖，更新時(shí)再申請(qǐng)排他鎖，因?yàn)楫?dāng)用戶申請(qǐng)排他鎖時(shí)，其他事務(wù)可能又已經(jīng)獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。

如果出現(xiàn)死鎖，可以用mysql> show engine innodb status\G命令來(lái)確定最后一個(gè)死鎖產(chǎn)生的原因。返回結(jié)果中包括死鎖相關(guān)事務(wù)的詳細(xì)信息，如引發(fā)死鎖的SQL語(yǔ)句，事務(wù)已經(jīng)獲得的鎖，正在等待什么鎖，以及被回滾的事務(wù)等。據(jù)此可以分析死鎖產(chǎn)生的原因和改進(jìn)措施。

總結(jié)：MySQL innodb引擎的鎖機(jī)制比myisam引擎機(jī)制復(fù)雜，但是innodb引擎支持更細(xì)粒度的鎖機(jī)制，當(dāng)然也會(huì)帶來(lái)更多維護(hù)的代價(jià)；然后innodb的行級(jí)別是借助對(duì)索引項(xiàng)加鎖實(shí)現(xiàn)的，值得注意的事如果表沒有索引，那么就會(huì)上表級(jí)別的鎖，同時(shí)借助行級(jí)鎖中g(shù)ap鎖來(lái)解決部分幻讀的問(wèn)題。只要知道MySQL innodb中的鎖的機(jī)制原理，那么再解決死鎖或者避免死鎖就會(huì)很容易！

到此，相信大家對(duì)“MySQL的innoDB鎖機(jī)制以及死鎖的處理方法”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！

網(wǎng)站題目：MySQL的innoDB鎖機(jī)制以及死鎖的處理方法
文章位置：http://www.chinadenli.net/article12/iepdgc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供App設(shè)計(jì)、網(wǎng)站設(shè)計(jì)、移動(dòng)網(wǎng)站建設(shè)、網(wǎng)站排名、商城網(wǎng)站、ChatGPT

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

欧美一区二区三区老妇人-欧美做爰猛烈大尺度电-99久久夜色精品国产亚洲a-亚洲福利视频一区二区

MySQL的innoDB鎖機(jī)制以及死鎖的處理方法