從對(duì)數(shù)據(jù)操作的粒度分法
表級(jí)鎖：表級(jí)鎖是MySQL中鎖定粒度最大的一種鎖，表示對(duì)當(dāng)前操作的整張表加鎖(MyISAM引擎默認(rèn)表級(jí)鎖，也只支持表級(jí)鎖)。比如說更新一張10萬表數(shù)據(jù)中的一條數(shù)據(jù)，在這條update沒提交事務(wù)之前，其它事務(wù)是會(huì)被排斥掉的，粒度很大。
行級(jí)鎖：行級(jí)鎖是Mysql中鎖定粒度最細(xì)的一種鎖，表示只針對(duì)當(dāng)前操作的行進(jìn)行加鎖(基于索引實(shí)現(xiàn)的，所以一旦某個(gè)加鎖操作沒有使用索引，那么該鎖就會(huì)退化為表鎖)
頁級(jí)鎖：頁級(jí)鎖是MySQL中鎖定粒度介于行級(jí)鎖和表級(jí)鎖中間的一種鎖，一次鎖定相鄰的一組記錄

從并發(fā)角度的分發(fā)--實(shí)際上樂觀鎖和悲觀鎖只是一種思想
悲觀鎖：對(duì)數(shù)據(jù)被外界（包括本系統(tǒng)當(dāng)前的其他事務(wù)，以及來自外部系統(tǒng)的事務(wù)處理）修改持保守態(tài)度(悲觀) ，因此，在整個(gè)數(shù)據(jù)處理過程中，將數(shù)據(jù)處于鎖定狀態(tài)。
樂觀鎖：樂觀鎖假設(shè)認(rèn)為數(shù)據(jù)一般情況下不會(huì)造成沖突，所以在數(shù)據(jù)進(jìn)行提交更新的時(shí)候，才會(huì)正式對(duì)數(shù)據(jù)的沖突與否進(jìn)行檢測(cè)，如果發(fā)現(xiàn)沖突了，則讓返回錯(cuò)誤信息再進(jìn)行業(yè)務(wù)重試

其他鎖：
間隙鎖：在條件查詢中，如：where id>100，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖；對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做“間隙（GAP)”，間隙的目的是為了防止幻讀
意向鎖：意向鎖分為 intention shared lock (IS) 和 intention exclusive lock (IX)，意向鎖的目的就是表明有事務(wù)正在或者將要鎖住某個(gè)表中的行

二、行鎖和表鎖的區(qū)別

表級(jí)鎖是MySQL中鎖定粒度最大的一種鎖，表示對(duì)當(dāng)前操作的整張表加鎖，它實(shí)現(xiàn)簡(jiǎn)單。最常使用的MYISAM與INNODB都支持表級(jí)鎖定。
特點(diǎn)：開銷小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)出鎖沖突的概率最高，并發(fā)度最低。

行級(jí)鎖是Mysql中鎖定粒度最細(xì)的一種鎖，表示只針對(duì)當(dāng)前操作的行進(jìn)行加鎖。行級(jí)鎖能大大減少數(shù)據(jù)庫操作的沖突。其加鎖粒度最小，但加鎖的開銷也最大。
特點(diǎn)：開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高
使用：InnoDB行鎖是通過給索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的，只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級(jí)鎖，否則，InnoDB將使用表鎖

下面這個(gè)update語句，b是一般字段不是索引列的話，那么此時(shí)行級(jí)鎖將改為表級(jí)鎖。

update from test set a=100 where b='100';

現(xiàn)在舉個(gè)實(shí)際例子操作一下，看看innnodb是怎么來用行鎖的。

當(dāng)前表中數(shù)據(jù):

首先開啟兩個(gè)session會(huì)話窗口，然后將mysql事務(wù)級(jí)別設(shè)置成不提交級(jí)別:

會(huì)話一窗口:

會(huì)話二窗口:

 其中會(huì)話2的update一直都在Running中，一直到超時(shí)結(jié)束，或者會(huì)話1提交事務(wù)后才會(huì)Running結(jié)束。

可以通過show VARIABLES like "%innodb_lock_wait_timeout%" 查詢當(dāng)前mysql設(shè)置的鎖超時(shí)時(shí)間,默認(rèn)是50秒。 

可以通過set innodb_lock_wait_timeout = 60; 設(shè)置鎖的超時(shí)時(shí)間。

當(dāng)?shù)谝粋€(gè)會(huì)話commit之后，第二個(gè)會(huì)話的update語句才會(huì)執(zhí)行成功。這代表了innodb用了鎖。

那怎么確定是用了行鎖呢？

 總結(jié):會(huì)話一更新id=125的時(shí)候，給這條數(shù)據(jù)add lock了，那么在會(huì)話2中再次更新id=125的時(shí)候，這條數(shù)據(jù)是locked中的。這個(gè)lock加的是id=125這條記錄。此時(shí)除了id=125這條之外的，都是可以成功的，證明這條默認(rèn)加的是行鎖。

三、InnoDB死鎖概念和死鎖案例

定義：當(dāng)兩個(gè)或以上的事務(wù)相互持有和請(qǐng)求鎖，并形成一個(gè)循環(huán)的依賴關(guān)系，就會(huì)產(chǎn)生死鎖。多個(gè)事務(wù)同時(shí)鎖定同一個(gè)資源時(shí)，也會(huì)產(chǎn)生死鎖。在一個(gè)事務(wù)系統(tǒng)中，死鎖是確切存在并且是不能完全避免的。

解決：InnoDB會(huì)自動(dòng)檢測(cè)事務(wù)死鎖，立即回滾其中某個(gè)事務(wù)，并且返回一個(gè)錯(cuò)誤。它根據(jù)某種機(jī)制來選擇那個(gè)最簡(jiǎn)單（代價(jià)最?。┑氖聞?wù)來進(jìn)行回滾

死鎖場(chǎng)景一之select for update:

產(chǎn)生場(chǎng)景：兩個(gè)transaction都有兩個(gè)select for update，transaction a先鎖記錄1，再鎖記錄2；而transaction b先鎖記錄2，再鎖記錄1

寫鎖：for update，讀鎖：for my share mode show engine innodb status

驗(yàn)證下死鎖的場(chǎng)景:

 第一步更新會(huì)話一:

start TRANSACTION;
select * from wnn_test where a=199 for update;

第二步更新會(huì)話二:

start TRANSACTION;
select * from wnn_test where a=101 for update;

第三步更新會(huì)話一:

select * from wnn_test where a=101 for update;

第四步更新會(huì)話二;

select * from wnn_test where a=199 for update;

在更新到第三步和第四步的時(shí)候，已經(jīng)發(fā)生了死鎖。

來看下執(zhí)行的日志:

show engine innodb status;最后一個(gè)鎖的時(shí)間，鎖的表，引起鎖的語句。其中session1被鎖 14秒(ACTIVE 14)，session 2被鎖了10秒(Active 10)

死鎖場(chǎng)景二之兩個(gè)update

產(chǎn)生場(chǎng)景：兩個(gè)transaction都有兩個(gè)update，transaction a先更新記錄1，再更新記錄2；而transaction b先更新記錄2，再更新記錄1

 產(chǎn)生日志：

 注意：仔細(xì)查看上面2個(gè)例子可以發(fā)現(xiàn)一個(gè)現(xiàn)象，當(dāng)2條資源鎖住后，再執(zhí)行第三個(gè)會(huì)執(zhí)行成功，但是第四個(gè)會(huì)提示死鎖。在mysql5.7中，執(zhí)行第三個(gè)的時(shí)候就會(huì)一直在Running狀態(tài)了，本博文使用的是mysql8.0 ，其中 有這個(gè)參數(shù) innodb_deadlock_detect 可以用于控制 InnoDB 是否執(zhí)行死鎖檢測(cè)，當(dāng)啟用了死鎖檢測(cè)時(shí)（默認(rèn)設(shè)置），InnoDB 自動(dòng)執(zhí)行事務(wù)的死鎖檢測(cè)，并且回滾一個(gè)或多個(gè)事務(wù)以解決死鎖。InnoDB 嘗試回滾更小的事務(wù)，事務(wù)的大小由它所插入、更新或者刪除的數(shù)據(jù)行數(shù)決定。

 那么這個(gè)innodb_deadlock_detect參數(shù)，到底要不要啟用呢？

對(duì)于高并發(fā)的系統(tǒng)，當(dāng)大量線程等待同一個(gè)鎖時(shí)，死鎖檢測(cè)可能會(huì)導(dǎo)致性能的下降。此時(shí)，如果禁用死鎖檢測(cè)，而改為依靠參數(shù) innodb_lock_wait_timeout 執(zhí)行發(fā)生死鎖時(shí)的事務(wù)回滾可能會(huì)更加高效。
通常來說，應(yīng)該啟用死鎖檢測(cè)，并且在應(yīng)用程序中盡量避免產(chǎn)生死鎖，同時(shí)對(duì)死鎖進(jìn)行相應(yīng)的處理，例如重新開始事務(wù)。

只有在確認(rèn)死鎖檢測(cè)影響了系統(tǒng)的性能，并且禁用死鎖檢測(cè)不會(huì)帶來負(fù)面影響時(shí)，可以嘗試關(guān)閉 innodb_deadlock_detect 選項(xiàng)。另外，如果禁用了 InnoDB 死鎖檢測(cè)，需要調(diào)整參數(shù) innodb_lock_wait_timeout 的值，以滿足實(shí)際的需求。

 四、程序開發(fā)過程中應(yīng)該如何注意避免死鎖

 鎖的本質(zhì)是資源相互競(jìng)爭(zhēng)，相互等待，往往是兩個(gè)(或以上)的Session加鎖的順序不一致

如何有效避免：

在程序中，操作多張表時(shí)，盡量以相同的順序來訪問（避免形成等待環(huán)路）

批量操作單張表數(shù)據(jù)的時(shí)候，先對(duì)數(shù)據(jù)進(jìn)行排序（避免形成等待環(huán)路） A線程 id：1 ,10 ,20按順序加鎖     B線程id:20,10,1   這種的話就容易鎖。

如果可以，大事務(wù)化成小事務(wù)，甚至不開啟事務(wù) select for update==>insert==>update = insert into update on duplicate key

盡量使用索引訪問數(shù)據(jù)，避免沒有 where 條件的操作，避免鎖表 有走索引是記錄行鎖，沒走索引是表鎖

使用等值查詢而不是范圍查詢查詢數(shù)據(jù)，命中記錄，避免間隙鎖對(duì)并發(fā)的影響 1，10，20 等值where id in (1,10,20) 范圍查詢 id>1 and id<20

避免在同一時(shí)間點(diǎn)運(yùn)行多個(gè)對(duì)同一表進(jìn)行讀寫的腳本，特別注意加鎖且操作數(shù)據(jù)量比較大的語句；我們經(jīng)常會(huì)有一些定時(shí)腳本，避免它們?cè)谕粫r(shí)間點(diǎn)運(yùn)行

 到此這篇關(guān)于Mysql鎖機(jī)制之行鎖、表鎖、死鎖的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)Mysql 行鎖、表鎖、死鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！