程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> 【mysql】關於悲觀鎖，mysql

【mysql】關於悲觀鎖，mysql

編輯：MySQL綜合教程

【mysql】關於悲觀鎖，mysql

關於mysql中的鎖

在並發環境下，有可能會出現髒讀（Dirty Read）、不可重復讀（Unrepeatable Read）、幻讀（Phantom Read）、更新丟失（Lost update）等情況，所以mysql引入了很多鎖的概念

MySQL InnoDB對數據行的鎖定類型一共有四種：共享鎖（讀鎖，S鎖）、排他鎖（寫鎖，X鎖）、意向共享鎖（IS鎖）和意向排他鎖（IX鎖），支持三種行鎖定方式：

行鎖（Record Lock）：鎖直接加在索引記錄上面。
間隙鎖（Gap Lock）：鎖加在不存在的空閒空間，可以是兩個索引記錄之間，也可能是第一個索引記錄之前或最後一個索引之後的空間。
Next-Key Lock：行鎖與間隙鎖組合起來用就叫做Next-Key Lock。

默認情況下，InnoDB工作在可重復讀隔離級別下，並且以Next-Key Lock的方式對數據行進行加鎖，這樣可以有效防止幻讀的發生。Next-Key Lock是行鎖與間隙鎖的組合，這樣，當InnoDB掃描索引記錄的時候，會首先對選中的索引記錄加上行鎖（Record Lock），再對索引記錄兩邊的間隙加上間隙鎖（Gap Lock）。如果一個間隙被事務T1加了鎖，其它事務是不能在這個間隙插入記錄的。

在可重復讀級別下，InnoDB以Next-Key Lock的方式對索引加鎖；在讀已提交級別下，InnoDB以Index-Record Lock的方式對索引加鎖。
被加鎖的索引如果不是聚族索引，那被鎖的索引所指向的聚族索引以及其它指向相同聚族索引的索引也會被加鎖。
SELECT * FROM ... LOCK IN SHARE MODE對索引加共享鎖；SELECT * FROM ... FOR UPDATE對索引加排他鎖。
SELECT * FROM ... 是非阻塞式讀，（除Serializable級別）不會對索引加鎖。在讀已提交級別下，總是查詢記錄的最新、有效的版本；在可重復讀級別下，會記住第一次查詢時的版本，之後的查詢會基於該版本。例外的情況是在串行化級別，這時會以Next-Key Lock的方式對索引加共享鎖。
UPDATE ... WHERE 與DELETE ... WHERE對索引加排他鎖。
INSERT INTO ... 以Index-Record Lock的方式對索引加排他鎖

什麼是悲觀鎖

悲觀鎖是指對數據被外界（包括本系統當前的其他事務，以及來自外部系統的事務處理）修改持保守態度，因此，在整個數據處理過程中，將數據處於鎖定狀態，在悲觀鎖的情況下，為了保證事務的隔離性，就需要一致性鎖定讀。讀取數據時給加鎖，其它事務無法修改這些數據。修改刪除數據時也要加鎖，其它事務無法讀取這些數據。

悲觀鎖（pessimistic locking）體現了一種謹慎的處事態度。其流程如下：

在對任意記錄進行修改前，先嘗試為該記錄加上排他鎖（exclusive locking）
如果加鎖失敗，說明該記錄正在被修改，那麼當前查詢可能要等待或者拋出異常
如果成功加鎖，那麼就可以對記錄做修改，事務完成後就會解鎖了
其間如果有其他對該記錄做修改或加排他鎖的操作，都會等待我們解鎖或直接拋出異常

悲觀鎖確實很嚴謹，有效保證了數據的一致性，在C/S應用上有諸多成熟方案。但是他的缺點與優點一樣的明顯

悲觀鎖適用於可靠的持續性連接，諸如C/S應用。對於Web應用的HTTP連接，先天不適用
鎖的使用意味著性能的損耗，在高並發、鎖定持續時間長的情況下，尤其嚴重。 Web應用的性能瓶頸多在數據庫處，使用悲觀鎖，進一步收緊了瓶頸
非正常中止情況下的解鎖機制，設計和實現起來很麻煩，成本還很高
不夠嚴謹的設計下，可能產生莫名其妙的，不易被發現的，的死鎖問題

自動提交

MySQL 采用 autocommit 模式運行。這意味著，當執行一個用於更新（修改）表的語句之後，MySQL立刻把更新到buffer中，同時記錄鎖也會被釋放。因此如果事務要執行多條更新（修改）語句，那麼從第2條更新語句開始就是在無鎖條件下執行了，這樣會導致事務失效，破壞數據一致性

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

關閉自動提交

set autocommit=0;  

or

[mysqld]  
init_connect='SET autocommit=0' ;

避免此問題的方法就是關閉 autocommit，然後通過執行 commit 語句來提交事務

$db->begin();
$db->query("SET autocommit=0");
...
...
$db->commit();

注意：

1、不能將"關閉autocommit"作為缺省設置，否則在 innodb 表上執行的查詢操作也將因為沒有執行 commit 或者 rollback 而一直鎖表！因此只能在需要時局部關閉 autocommit，並在操作完成後開啟 autocommit

2、連接mysql用戶的權限不能大於啟動mysql的用戶的權限，不然init_connect='SET autocommit=0'根本不會啟作用，也不會報任何錯誤

If a user has SUPER privilege, init_connect will not execute，(otherwise if init_connect will a wrong query no one can connect to server).
Note, if init_connect is a wrong query, the connection is closing without any errors and next command will clause 'lost connection' error.

使用舉例

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

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.01 sec)

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

Connect a:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select age from users where id =1 for update;
+-----+
| age |
+-----+
|  24 |
+-----+
1 row in set (0.00 sec)

mysql> update users set age = 25 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

Connect b: 在a長時間未提交的時候

mysql> select * from users where id =1 for update;
ERROR 1205 : Lock wait timeout exceeded; try restarting transaction
mysql> update users set age = 22 where id =1;
ERROR 1205 : Lock wait timeout exceeded; try restarting transaction

connect b 只有在 connect a 提交之後才會執行，否則會一直等待

在事務中，只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 會等待其它事務結束後才執行，一般SELECT ... 則不受此影響

MySQL select…for update 的 Row Lock 與 Table Lock　

上面我們提到，使用select…for update會把數據給鎖住，不過我們需要注意一些鎖的級別，MySQL InnoDB默認Row-Level Lock，所以只有「明確」地指定主鍵/索引，MySQL 才會執行Row lock (只鎖住被選取的數據) ，否則MySQL 將會執行Table Lock (將整個數據表單給鎖住)

如果一個條件無法通過索引快速過濾，存儲引擎層面就會將所有記錄加鎖後返回，再由MySQL Server層進行過濾，但在實際使用過程當中，MySQL做了一些改進，在MySQL Server過濾條件，發現不滿足後，會調用unlock_row方法，把不滿足條件的記錄釋放鎖 (違背了二段鎖協議的約束)。這樣做，保證了最後只會持有滿足條件記錄上的鎖，但是每條記錄的加鎖操作還是不能省略的。可見即使是MySQL，為了效率也是會違反規范的

這種情況同樣適用於MySQL的默認隔離級別RR。所以對一個數據量很大的表做批量修改的時候，如果無法使用相應的索引，MySQL Server過濾數據的的時候特別慢，就會出現雖然沒有修改某些行的數據，但是它們還是被鎖住了的現象