[[238415]]

MySQL/InnoDB的加鎖，一直是一個面試中常問的話題。例如，數(shù)據(jù)庫如果有高并發(fā)請求，如何保證數(shù)據(jù)完整性？產(chǎn)生死鎖問題如何排查并解決？我在工作過程中，也會經(jīng)常用到，樂觀鎖，排它鎖，等。于是今天就對這幾個概念進行學(xué)習(xí)，屢屢思路，記錄一下。

注：MySQL是一個支持插件式存儲引擎的數(shù)據(jù)庫系統(tǒng)。本文下面的所有介紹，都是基于InnoDB存儲引擎，其他引擎的表現(xiàn)，會有較大的區(qū)別。

存儲引擎查看

MySQL給開發(fā)者提供了查詢存儲引擎的功能，我這里使用的是MySQL5.6.4，可以使用：

SHOW ENGINES

樂觀鎖

用數(shù)據(jù)版本（Version）記錄機制實現(xiàn)，這是樂觀鎖最常用的一種實現(xiàn)方式。何謂數(shù)據(jù)版本？即為數(shù)據(jù)增加一個版本標(biāo)識，一般是通過為數(shù)據(jù)庫表增加一個數(shù)字類型的 “version” 字段來實現(xiàn)。當(dāng)讀取數(shù)據(jù)時，將version字段的值一同讀出，數(shù)據(jù)每更新一次，對此version值加1。當(dāng)我們提交更新的時候，判斷數(shù)據(jù)庫表對應(yīng)記錄的當(dāng)前版本信息與***次取出來的version值進行比對，如果數(shù)據(jù)庫表當(dāng)前版本號與***次取出來的version值相等，則予以更新，否則認為是過期數(shù)據(jù)。

舉例

1、數(shù)據(jù)庫表設(shè)計

三個字段，分別是 id,value、version 

select id,value,version from TABLE where id=#{id} 

2、每次更新表中的value字段時，為了防止發(fā)生沖突，需要這樣操作 

update TABLE  
set value=2,version=version+1  
where id=#{id} and version=#{version};  

悲觀鎖

與樂觀鎖相對應(yīng)的就是悲觀鎖了。悲觀鎖就是在操作數(shù)據(jù)時，認為此操作會出現(xiàn)數(shù)據(jù)沖突，所以在進行每次操作時都要通過獲取鎖才能進行對相同數(shù)據(jù)的操作，這點跟java中的synchronized很相似，所以悲觀鎖需要耗費較多的時間。另外與樂觀鎖相對應(yīng)的，悲觀鎖是由數(shù)據(jù)庫自己實現(xiàn)了的，要用的時候，我們直接調(diào)用數(shù)據(jù)庫的相關(guān)語句就可以了。

說到這里，由悲觀鎖涉及到的另外兩個鎖概念就出來了，它們就是共享鎖與排它鎖。 共享鎖和排它鎖是悲觀鎖的不同的實現(xiàn) ，它倆都屬于悲觀鎖的范疇。

使用，排它鎖 舉例

要使用悲觀鎖，我們必須關(guān)閉mysql數(shù)據(jù)庫的自動提交屬性，因為MySQL默認使用autocommit模式，也就是說，當(dāng)你執(zhí)行一個更新操作后，MySQL會立刻將結(jié)果進行提交。

我們可以使用命令設(shè)置MySQL為非autocommit模式： 

set autocommit=0;  
# 設(shè)置完autocommit后，我們就可以執(zhí)行我們的正常業(yè)務(wù)了。具體如下：  
# 1. 開始事務(wù)  
begin;/begin work;/start transaction; (三者選一就可以)  
# 2. 查詢表信息  
select status from TABLE where id=1 for update;  
# 3. 插入一條數(shù)據(jù)  
insert into TABLE (id,value) values (2,2);  
# 4. 修改數(shù)據(jù)為  
update TABLE set value=2 where id=1;  
# 5. 提交事務(wù)  
commit;/commit work;  

共享鎖

共享鎖又稱 讀鎖 read lock ，是讀取操作創(chuàng)建的鎖。其他用戶可以并發(fā)讀取數(shù)據(jù)，但任何事務(wù)都不能對數(shù)據(jù)進行修改（獲取數(shù)據(jù)上的排他鎖），直到已釋放所有共享鎖。

如果事務(wù)T對數(shù)據(jù)A加上共享鎖后，則其他事務(wù)只能對A再加共享鎖，不能加排他鎖。獲得共享鎖的事務(wù)只能讀數(shù)據(jù)，不能修改數(shù)據(jù)

打開***個查詢窗口 

begin;/begin work;/start transaction; (三者選一就可以)  
SELECT * from TABLE where id = 1 lock in share mode;  

然后在另一個查詢窗口中，對id為1的數(shù)據(jù)進行更新 

update TABLE set name="www.souyunku.com" where id =1; 

此時，操作界面進入了卡頓狀態(tài)，過了超時間，提示錯誤信息

如果在超時前，執(zhí)行 commit ，此更新語句就會成功。 

[SQL]update test_one set name="www.souyunku.com" where id =1;  
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction  

加上共享鎖后，也提示錯誤信息 

update test_one set name="www.souyunku.com" where id =1 lock in share mode;  
[SQL]update test_one set name="www.souyunku.com" where id =1 lock in share mode;  
[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1  

在查詢語句后面增加 LOCK IN SHARE MODE ，Mysql會對查詢結(jié)果中的每行都加共享鎖，當(dāng)沒有其他線程對查詢結(jié)果集中的任何一行使用排他鎖時，可以成功申請共享鎖，否則會被阻塞。其他線程也可以讀取使用了共享鎖的表，而且這些線程讀取的是同一個版本的數(shù)據(jù)。

加上共享鎖后，對于 update,insert,delete 語句會自動加排它鎖。

排它鎖

排他鎖 exclusive lock（也叫writer lock）又稱 寫鎖 。

排它鎖是悲觀鎖的一種實現(xiàn)，在上面悲觀鎖也介紹過。

若事務(wù) 1 對數(shù)據(jù)對象A加上X鎖，事務(wù) 1 可以讀A也可以修改A，其他事務(wù)不能再對A加任何鎖，直到事物 1 釋放A上的鎖。這保證了其他事務(wù)在事物 1 釋放A上的鎖之前不能再讀取和修改A。排它鎖會阻塞所有的排它鎖和共享鎖

讀取為什么要加讀鎖呢：防止數(shù)據(jù)在被讀取的時候被別的線程加上寫鎖，

使用方式：在需要執(zhí)行的語句后面加上 for update 就可以了

行鎖

行鎖又分 共享鎖 和 排他鎖 ,由字面意思理解，就是給某一行加上鎖，也就是一條記錄加上鎖。

注意：行級鎖都是基于索引的，如果一條SQL語句用不到索引是不會使用行級鎖的，會使用表級鎖。

共享鎖：

名詞解釋：共享鎖又叫做讀鎖，所有的事務(wù)只能對其進行讀操作不能寫操作，加上共享鎖后在事務(wù)結(jié)束之前其他事務(wù)只能再加共享鎖，除此之外其他任何類型的鎖都不能再加了。 

SELECT * from TABLE where id = "1" lock in share mode; 

結(jié)果集的數(shù)據(jù)都會加共享鎖

排他鎖：

名詞解釋：若某個事物對某一行加上了排他鎖，只能這個事務(wù)對其進行讀寫，在此事務(wù)結(jié)束之前，其他事務(wù)不能對其進行加任何鎖，其他進程可以讀取,不能進行寫操作，需等待其釋放。 

select status from TABLE where id=1 for update; 

可以參考之前演示的共享鎖,排它鎖語句

由于對于表中,id字段為主鍵，就也相當(dāng)于索引。執(zhí)行加鎖時，會將id這個索引為1的記錄加上鎖，那么這個鎖就是行鎖。

表鎖

如何加表鎖

innodb 的行鎖是在有索引的情況下,沒有索引的表是鎖定全表的.

Innodb中的行鎖與表鎖

前面提到過，在Innodb引擎中既支持行鎖也支持表鎖，那么什么時候會鎖住整張表，什么時候或只鎖住一行呢？ 只有通過索引條件檢索數(shù)據(jù)，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！

在實際應(yīng)用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導(dǎo)致大量的鎖沖突，從而影響并發(fā)性能。

行級鎖都是基于索引的，如果一條SQL語句用不到索引是不會使用行級鎖的，會使用表級鎖。行級鎖的缺點是：由于需要請求大量的鎖資源，所以速度慢，內(nèi)存消耗大。

死鎖

死鎖（Deadlock） 所謂死鎖：是指兩個或兩個以上的進程在執(zhí)行過程中，因爭奪資源而造成的一種互相等待的現(xiàn)象，若無外力作用，它們都將無法推進下去。此時稱系統(tǒng)處于死鎖狀態(tài)或系統(tǒng)產(chǎn)生了死鎖，這些永遠在互相等待的進程稱為死鎖進程。由于資源占用是互斥的，當(dāng)某個進程提出申請資源后，使得有關(guān)進程在無外力協(xié)助下，永遠分配不到必需的資源而無法繼續(xù)運行，這就產(chǎn)生了一種特殊現(xiàn)象死鎖。

解除正在死鎖的狀態(tài)有兩種方法：

***種：

1.查詢是否鎖表 

show OPEN TABLES where In_use > 0; 

2.查詢進程（如果您有SUPER權(quán)限，您可以看到所有線程。否則，您只能看到您自己的線程） 

show processlist 

3.殺死進程id（就是上面命令的id列）

kill id

第二種：

1：查看當(dāng)前的事務(wù) 

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX; 

2：查看當(dāng)前鎖定的事務(wù) 

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

3：查看當(dāng)前等鎖的事務(wù) 

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

殺死進程

kill 線程ID

如果系統(tǒng)資源充足，進程的資源請求都能夠得到滿足，死鎖出現(xiàn)的可能性就很低，否則就會因爭奪有限的資源而陷入死鎖。其次，進程運行推進順序與速度不同，也可能產(chǎn)生死鎖。 產(chǎn)生死鎖的四個必要條件：

（1） 互斥條件：一個資源每次只能被一個進程使用。 （2） 請求與保持條件：一個進程因請求資源而阻塞時，對已獲得的資源保持不放。 （3） 不剝奪條件:進程已獲得的資源，在末使用完之前，不能強行剝奪。 （4） 循環(huán)等待條件:若干進程之間形成一種頭尾相接的循環(huán)等待資源關(guān)系。

雖然不能完全避免死鎖，但可以使死鎖的數(shù)量減至最少。將死鎖減至最少可以增加事務(wù)的吞吐量并減少系統(tǒng)開銷，因為只有很少的事務(wù)回滾，而回滾會取消事務(wù)執(zhí)行的所有工作。由于死鎖時回滾而由應(yīng)用程序重新提交。

下列方法有助于***限度地降低死鎖：

（1）按同一順序訪問對象。 （2）避免事務(wù)中的用戶交互。 （3）保持事務(wù)簡短并在一個批處理中。 （4）使用低隔離級別。 （5）使用綁定連接。

參考 :

• https://blog.csdn.net/puhaiyang/article/details/72284702
• https://www.jb51.net/article/78088.htm

原文鏈接：https://www.souyunku.com/2018/07/30/mysql 如果侵犯您的權(quán)益請告知刪除