MySQL之InnoDB中鎖的情況分析

這篇文章主要講解了“MySQL之InnoDB中鎖的情況分析”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“MySQL之InnoDB中鎖的情況分析”吧！

創(chuàng)新互聯(lián)建站主營大廠網(wǎng)站建設(shè)的網(wǎng)絡(luò)公司,主營網(wǎng)站建設(shè)方案,手機APP定制開發(fā),大廠h5微信小程序搭建,大廠網(wǎng)站營銷推廣歡迎大廠等地區(qū)企業(yè)咨詢

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.01 sec)

一，鎖的基本介紹

相對其他數(shù)據(jù)庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的存儲引擎支持不同的鎖機制。比如，MyISAM和MEMORY存儲引擎采用的是表級鎖（table-level locking）；InnoDB存儲引擎既支持行級鎖（row-level locking），也支持表級鎖，但默認(rèn)情況下是采用行級鎖。

表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。

行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。

行級鎖類型：

Record Lock(記錄鎖)：當(dāng)個記錄的鎖（鎖住單條記錄）

記錄鎖只會鎖住索引的記錄，如果InnoDB存儲表在建立的時候沒有任何索引，那么這個鎖會使用隱式的主鍵來進(jìn)行鎖定，如下圖

Gap Lock(間隙鎖)：鎖定一個范圍，不包括記錄本身(只鎖數(shù)據(jù)前面的GAP)

如下圖為的鎖就是GAP鎖，就是不允許其他事務(wù)在索引列8之前的間隙插入新的記錄，也就是(3 , 8)這個區(qū)間。gap鎖 的作用僅僅是為了防止插入幻影記錄的而已

Next-Key Lock(臨鍵鎖)：同時鎖住記錄和記錄前面的GAP，也就是Next-Key Lock = Record Lock + Gap Lock。

二，鎖的分類

共享鎖 Share Locks (簡稱S鎖，屬于行鎖)

排它鎖 Exclusive Locks (簡稱X鎖，屬于行鎖)

意向共享鎖 Intention Share Locks (簡稱IS鎖，屬于表鎖)

意向排它鎖 Intention Exclusive Locks (簡稱IX鎖，屬于表鎖)

自增鎖 AUTO-INC Locks(屬于表鎖)

下面具體介紹下每種類型的鎖，我們先建一張innodb的表，sql如下

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

共享鎖

共享鎖就是多個事務(wù)對于同一個數(shù)據(jù)可以共享一把鎖，都能訪問數(shù)據(jù)庫，但是只能讀不能修改；

事務(wù)A：

select * from tab_with_index lock in share mode;

事務(wù)B：

select * from tab_with_index where id =1; // 可以查詢數(shù)據(jù)

update tab_with_index set name = 'aa' where id = 1 ;

注意：這里的修改語句會堵塞住，直到事務(wù)A提交之后才能操作成功。

排它鎖

排它鎖不能與其他鎖并存，如一個事務(wù)獲取了一個數(shù)據(jù)行的排它鎖，其他事務(wù)就不能在獲取該行的鎖，只有當(dāng)前獲取排它鎖的事務(wù)可以對數(shù)據(jù)進(jìn)行修改。（delete，update，create默認(rèn)是排它鎖）

事務(wù)A：

select * from tab_with_index where id =1 for update;

事務(wù)B：

select * from tab_with_index where id =1; //可以獲取結(jié)果

select * from tab_with_index where id =1 for update; // 堵塞

select * from tab_with_index where id = 1 lock for share mode; // 堵塞

注意：事務(wù)B兩個sql都會堵塞住,也就是獲取不到共享鎖也獲取不到排它鎖，直到事務(wù)A提交之后才能操作成功。

意向共享鎖和意向排它鎖

意向共享鎖：表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入共享鎖，也就是說一個數(shù)據(jù)行在加共享鎖之前必須先獲取該表的IS鎖。

意向排它鎖：表示事務(wù)準(zhǔn)備給數(shù)據(jù)行加入排它鎖，也就是說一個數(shù)據(jù)行加排它鎖之前必須先獲取該表的IX鎖。

IS鎖和IX鎖是表級鎖，他們的提出僅僅為了在之后加表級別的S鎖和X鎖時可以快速判斷表中的記錄是否被上鎖，以避免用遍歷的方式來查看表中有沒有上鎖的記錄，也就是說其實IS鎖和IX鎖是兼容的，IX鎖和IX鎖是兼容的。 《MySQL是怎樣運行的》

自增鎖

針對自增列自增長的一個特殊的表級別鎖。

show variables like 'innodb_autoinc_lock_mode'; 
-- 默認(rèn)值1，代表連續(xù)，事務(wù)未提交則ID永久丟失

三，InnoDB鎖

1、事務(wù)及其ACID屬性

事務(wù)是由一組SQL語句組成的邏輯處理單元，事務(wù)具有4屬性，通常稱為事務(wù)的ACID屬性。

原子性（Actomicity）：事務(wù)是一個原子操作單元，其對數(shù)據(jù)的修改，要么全都執(zhí)行，要么全都不執(zhí)行。 一致性（Consistent）：在事務(wù)開始和完成時，數(shù)據(jù)都必須保持一致狀態(tài)。 隔離性（Isolation）：數(shù)據(jù)庫系統(tǒng)提供一定的隔離機制，保證事務(wù)在不受外部并發(fā)操作影響的“獨立”環(huán)境執(zhí)行。 持久性（Durable）：事務(wù)完成之后，它對于數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。

2、并發(fā)事務(wù)帶來的問題

相對于串行處理來說，并發(fā)事務(wù)處理能大大增加數(shù)據(jù)庫資源的利用率，提高數(shù)據(jù)庫系統(tǒng)的事務(wù)吞吐量，從而可以支持更多用戶的并發(fā)操作，但與此同時，會帶來一下問題：

臟讀： 一個事務(wù)正在對一條記錄做修改，在這個事務(wù)并提交前，這條記錄的數(shù)據(jù)就處于不一致狀態(tài)；這時，另一個事務(wù)也來讀取同一條記錄，如果不加控制，第二個事務(wù)讀取了這些“臟”的數(shù)據(jù)，并據(jù)此做進(jìn)一步的處理，就會產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系。這種現(xiàn)象被形象地叫做“臟讀”

不可重復(fù)讀：一個事務(wù)在讀取某些數(shù)據(jù)已經(jīng)發(fā)生了改變、或某些記錄已經(jīng)被刪除了！這種現(xiàn)象叫做“不可重復(fù)讀”。

幻讀： 一個事務(wù)按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)，這種現(xiàn)象就稱為“幻讀”

上述出現(xiàn)的問題都是數(shù)據(jù)庫讀一致性的問題，可以通過事務(wù)的隔離機制來進(jìn)行保證。

數(shù)據(jù)庫的事務(wù)隔離越嚴(yán)格，并發(fā)副作用就越小，但付出的代價也就越大，因為事務(wù)隔離本質(zhì)上就是使事務(wù)在一定程度上串行化，需要根據(jù)具體的業(yè)務(wù)需求來決定使用哪種隔離級別

	臟讀	不可重復(fù)讀	幻讀
read uncommitted	√	√	√
read committed		√	√
repeatable read			√
serializable

可以通過檢查InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭奪情況：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_lock_time_max      | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
--如果發(fā)現(xiàn)鎖爭用比較嚴(yán)重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高

3、InnoDB的行鎖模式及加鎖方法

共享鎖（S）：又稱讀鎖(lock in share mode)。允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。若事務(wù)T對數(shù)據(jù)對象A加上S鎖，則事務(wù)T可以讀A但不能修改A，其他事務(wù)只能再對A加S鎖，而不能加X鎖，直到T釋放A上的S鎖。這保證了其他事務(wù)可以讀A，但在T釋放A上的S鎖之前不能對A做任何修改。  排他鎖（X）：又稱寫鎖(for update)。允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同的數(shù)據(jù)集共享讀鎖和排他寫鎖。若事務(wù)T對數(shù)據(jù)對象A加上X鎖，事務(wù)T可以讀A也可以修改A，其他事務(wù)不能再對A加任何鎖，直到T釋放A上的鎖。

mysql InnoDB引擎默認(rèn)的修改數(shù)據(jù)語句：update,delete,insert都會自動給涉及到的數(shù)據(jù)加上排他鎖，select語句默認(rèn)不會加任何鎖類型，如果加排他鎖可以使用select …for update語句，加共享鎖可以使用select … lock in share mode語句。所以加過排他鎖的數(shù)據(jù)行在其他事務(wù)中是不能修改數(shù)據(jù)的，也不能通過for update和lock in share mode鎖的方式查詢數(shù)據(jù)，但可以直接通過select …from…查詢數(shù)據(jù)，因為普通查詢沒有任何鎖機制。

4、InnoDB行鎖實現(xiàn)方式

InnoDB行鎖是通過給索引上的索引項加鎖來實現(xiàn)的，這一點MySQL與Oracle不同，后者是通過在數(shù)據(jù)塊中對相應(yīng)數(shù)據(jù)行加鎖來實現(xiàn)的。InnoDB這種行鎖實現(xiàn)特點意味著：只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！

1、在不通過索引條件查詢的時候，innodb使用的是表鎖而不是行鎖

create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_no_index where id = 1;	set autocommit=0 select * from tab_no_index where id =2
select * from tab_no_index where id = 1 for update
	select * from tab_no_index where id = 2 for update;

session1只給一行加了排他鎖，但是session2在請求其他行的排他鎖的時候，會出現(xiàn)鎖等待。原因是在沒有索引的情況下，innodb只能使用表鎖。

2、創(chuàng)建帶索引的表進(jìn)行條件查詢，innodb使用的是行鎖

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_with_indexwhere id = 1;	set autocommit=0 select * from tab_with_indexwhere id =2
select * from tab_with_indexwhere id = 1 for update
	select * from tab_with_indexwhere id = 2 for update;

3、由于mysql的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是依然無法訪問到具體的數(shù)據(jù)（這里是表鎖）

alter table tab_with_index drop index id;
insert into tab_with_index  values(1,'4');

session1	session2
set autocommit=0	set autocommit=0
select * from tab_with_index where id = 1 and name='1' for update
	select * from tab_with_index where id = 1 and name='4' for update 雖然session2訪問的是和session1不同的記錄，但是鎖的是具體表，所以需要等待鎖

感謝各位的閱讀，以上就是“MySQL之InnoDB中鎖的情況分析”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對MySQL之InnoDB中鎖的情況分析這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！

標(biāo)題名稱：MySQL之InnoDB中鎖的情況分析
當(dāng)前網(wǎng)址：http://www.muchs.cn/article6/pdhhog.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供面包屑導(dǎo)航、小程序開發(fā)、標(biāo)簽優(yōu)化、網(wǎng)站排名、網(wǎng)站制作、網(wǎng)站收錄

廣告

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