資料庫高並發請求，如何保證資料完整性？詳解MySQL/InnoDB的加鎖

本文是對MySQL/InnoDB中,樂觀鎖、悲觀鎖、共用鎖定、排它鎖、行鎖、表鎖、死結概念的理解，這些在面試中也經常遇到，如資料庫高並發請求，如何保證資料完整性？今天我查閱資料進行了MySQL/InnoDB中加鎖知識點的匯總，這樣也會大家就不會感覺很繁瑣和雜亂了，覺得有用的話就繼續分享吧。apache php mysql

註：MySQL是一個支援外掛程式式儲存引擎的資料庫系統。本文下面的所有介紹，都是基於InnoDB儲存引擎，其他引擎的表現，會有較大的區別。

儲存引擎查看

MySQL給開發人員提供了查詢儲存引擎的功能，我這裡使用的是MySQL5.6.4，可以使用：

SHOW ENGINES

begin！

樂觀鎖

用資料版本（Version）記錄機制實現，這是樂觀鎖最常用的一種實現方式。何謂資料版本？即為資料增加一個版本標識，一般是通過為資料庫表增加一個數字類型的 “version” 欄位來實現。當讀取資料時，將version欄位的值一同讀出，資料每更新一次，對此version值加1。當我們提交更新的時候，判斷資料庫表對應記錄的目前的版本資訊與第一次取出來的version值進行比對，如果資料庫表目前的版本號與第一次取出來的version值相等，則予以更新，否則認為是到期資料。

舉例

1、資料庫表設計

三個欄位，分別是id,value、version

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

2、每次更新表中的value欄位時，為了防止發生衝突，需要這樣操作

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

悲觀鎖

與樂觀鎖相對應的就是悲觀鎖了。悲觀鎖就是在操作資料時，認為此操作會出現資料衝突，所以在進行每次操作時都要通過擷取鎖才能進行對相同資料的操作，這點跟java中的synchronized很相似，所以悲觀鎖需要耗費較多的時間。另外與樂觀鎖相對應的，悲觀鎖是由資料庫自己實現了的，要用的時候，我們直接調用資料庫的相關語句就可以了。

說到這裡，由悲觀鎖涉及到的另外兩個鎖概念就出來了，它們就是共用鎖定與排它鎖。共用鎖定和排它鎖是悲觀鎖的不同的實現，它倆都屬於悲觀鎖的範疇。

使用，排它鎖 舉例

要使用悲觀鎖，我們必須關閉mysql資料庫的自動認可屬性，因為MySQL預設使用autocommit模式，也就是說，當你執行一個更新操作後，MySQL會立刻將結果進行提交。

我們可以使用命令設定MySQL為非autocommit模式：

set autocommit=0;# 設定完autocommit後，我們就可以執行我們的正常業務了。具體如下：# 1. 開始事務begin;/begin work;/start transaction; (三者選一就可以)# 2. 查詢表資訊select status from TABLE where id=1 for update;# 3. 插入一條資料insert into TABLE (id,value) values (2,2);# 4. 修改資料為update TABLE set value=2 where id=1;# 5. 提交事務commit;/commit work;

共用鎖定

共用鎖定又稱讀鎖 read lock，是讀取操作建立的鎖。其他使用者可以並發讀取資料，但任何事務都不能對資料進行修改（擷取資料上的獨佔鎖定），直到已釋放所有共用鎖定。

如果事務T對資料A加上共用鎖定後，則其他事務只能對A再加共用鎖定，不能加獨佔鎖定。獲得共用鎖定的事務只能讀資料，不能修改資料

開啟第一個查詢時段

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

然後在另一個查詢時段中，對id為1的資料進行更新

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

此時，操作介面進入了卡頓狀態，過了逾時間，提示錯誤資訊

如果在逾時前，執行 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會對查詢結果中的每行都加共用鎖定，當沒有其他線程對查詢結果集中的任何一行使用獨佔鎖定時，可以成功申請共用鎖定，否則會被阻塞。其他線程也可以讀取使用了共用鎖定的表，而且這些線程讀取的是同一個版本的資料。

加上共用鎖定後，對於update,insert,delete語句會自動加排它鎖。

排它鎖

獨佔鎖定 exclusive lock（也叫writer lock）又稱寫鎖。

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

若事務 1 對資料對象A加上X鎖，事務 1 可以讀A也可以修改A，其他事務不能再對A加任何鎖，直到事物 1 釋放A上的鎖。這保證了其他事務在事物 1 釋放A上的鎖之前不能再讀取和修改A。排它鎖會阻塞所有的排它鎖和共用鎖定

讀取為什麼要加讀鎖呢：防止資料在被讀取的時候被別的線程加上寫鎖，

使用方式：在需要執行的語句後面加上for update就可以了

行鎖

行鎖又分共用鎖定和獨佔鎖定,由字面意思理解，就是給某一行加上鎖，也就是一條記錄加上鎖。

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

共用鎖定：

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

SELECT * from TABLE where id = "1"  lock in share mode;  結果集的資料都會加共用鎖定

獨佔鎖定：

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

select status from TABLE where id=1 for update;

可以參考之前示範的共用鎖定,排它鎖語句

由於對於表中,id欄位為主鍵，就也相當於索引。執行加鎖時，會將id這個索引為1的記錄加上鎖，那麼這個鎖就是行鎖。

表鎖

如何加表鎖

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

Innodb中的行鎖與表鎖

前面提到過，在Innodb引擎中既支援行鎖也支援表鎖，那麼什麼時候會鎖住整張表，什麼時候或只鎖住一行呢？
只有通過索引條件檢索資料，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！

在實際應用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導致大量的鎖衝突，從而影響並發效能。

行級鎖都是基於索引的，如果一條SQL語句用不到索引是不會使用行級鎖的，會使用表級鎖。行級鎖的缺點是：由於需要請求大量的鎖資源，所以速度慢，記憶體消耗大。

死結

死結（Deadlock）
所謂死結：是指兩個或兩個以上的進程在執行過程中，因爭奪資源而造成的一種互相等待的現象，若無外力作用，它們都將無法推進下去。此時稱系統處於死結狀態或系統產生了死結，這些永遠在互相等待的進程稱為死結進程。由於資源佔用是互斥的，當某個進程提出申請資源後，使得有關進程在無外力協助下，永遠分配不到必需的資源而無法繼續運行，這就產生了一種特殊現象死結。

解除正在死結的狀態有兩種方法：

第一種：

1.查詢是否鎖表

show OPEN TABLES where In_use > 0;

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

show processlist

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

kill id

第二種：

1：查看當前的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2：查看當前鎖定的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：查看當前等鎖的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

殺死進程

kill 線程ID

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

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

雖然不能完全避免死結，但可以使死結的數量減至最少。將死結減至最少可以增加事務的輸送量並減少系統開銷，因為只有很少的交易回復，而復原會取消事務執行的所有工作。由於死結時復原而由應用程式重新提交。

下列方法有助於最大限度地降低死結：

（1）按同一順序訪問對象。
（2）避免事務中的使用者互動。
（3）保持事務簡短並在一個批處理中。
（4）使用低隔離等級。
（5）使用綁定串連。

end！