mysql 共用鎖定-排它鎖

標籤：

轉  InnoDB 行級鎖

 

InnoDB 行級鎖分類： 資料庫2013-03-13 16:40 1745人閱讀 評論(0) 收藏 舉報

nnoDB的行鎖模式及加鎖方法

InnoDB實現了以下兩種類型的行鎖。 

? 共用鎖定（S）：允許一個事務去讀一行，阻止其他事務獲得相同資料集的獨佔鎖定。

? 獨佔鎖定（X)：允許獲得獨佔鎖定的事務更新資料，阻止其他事務取得相同資料集的共用讀鎖和排他寫鎖。

另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意圖鎖定（Intention Locks），這兩種意圖鎖定都是表鎖。

? 意圖共用鎖（IS）：事務打算給資料行加行共用鎖定，事務在給一個資料行加共用鎖定前必須先取得該表的IS鎖。

? 意向獨佔鎖定（IX）：事務打算給資料行加行獨佔鎖定，事務在給一個資料行加獨佔鎖定前必須先取得該表的IX鎖。

上述鎖模式的相容情況具體如表20-6所示。

表20-6     InnoDB行鎖模式相容性列表

請求鎖模式      是否相容   當前鎖模式	X	IX	S	IS
X	衝突	衝突	衝突	衝突
IX	衝突	相容	衝突	相容
S	衝突	衝突	相容	相容
IS	衝突	相容	相容	相容

如果一個事務請求的鎖模式與當前的鎖相容，InnoDB就將請求的鎖授予該事務；反之，如果兩者不相容，該事務就要等待鎖釋放。

意圖鎖定是InnoDB自動加的，不需使用者幹預。對於UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及資料集加獨佔鎖定（X)；對於普通SELECT語句，InnoDB不會加任何鎖；事務可以通過以下語句顯示給記錄集加共用鎖定或獨佔鎖定。

·共用鎖定（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

·獨佔鎖定（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... IN SHARE MODE獲得共用鎖定，主要用在需要資料依存關係時來確認某行記錄是否存在，並確保沒有人對這個記錄進行UPDATE或者DELETE操作。但是如果當前事務也需要對該記錄進行更新操作，則很有可能造成死結，對於鎖定行記錄後需要進行更新操作的應用，應該使用SELECT... FOR UPDATE方式獲得獨佔鎖定。

在如表20-7所示的例子中，使用了SELECT ... IN SHARE MODE加鎖後再更新記錄，看看會出現什麼情況，其中actor表的actor_id欄位為主鍵。

表20-7           InnoDB儲存引擎的共用鎖定例子

session_1	session_2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)   mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | LISA       | MONROE   | +----------+------------+-----------+ 1 row in set (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)   mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | LISA       | MONROE   | +----------+------------+-----------+ 1 row in set (0.00 sec)
當前session對actor_id=178的記錄加share mode 的共用鎖定： mysql> select actor_id,first_name,last_name from actor where actor_id = 178 lock in share mode; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | LISA       | MONROE   | +----------+------------+-----------+ 1 row in set (0.01 sec)
	其他session仍然可以查詢記錄，並也可以對該記錄加share mode的共用鎖定： mysql> select actor_id,first_name,last_name from actor where actor_id = 178 lock in share mode; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | LISA       | MONROE   | +----------+------------+-----------+ 1 row in set (0.01 sec)
當前session對鎖定的記錄進行更新操作，等待鎖： mysql> update actor set last_name = ‘MONROE T‘ where actor_id = 178; 等待
	其他session也對該記錄進行更新操作，則會導致死結退出： mysql> update actor set last_name = ‘MONROE T‘ where actor_id = 178; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
獲得鎖後，可以成功更新： mysql> update actor set last_name = ‘MONROE T‘ where actor_id = 178; Query OK, 1 row affected (17.67 sec) Rows matched: 1  Changed: 1 Warnings: 0

當使用SELECT...FOR UPDATE加鎖後再更新記錄，出現如表20-8所示的情況。

表20-8             InnoDB儲存引擎的獨佔鎖定例子

session_1	session_2
mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)   mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | LISA       | MONROE   | +----------+------------+-----------+ 1 row in set (0.00 sec)	mysql> set autocommit = 0; Query OK, 0 rows affected (0.00 sec)   mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | LISA       | MONROE   | +----------+------------+-----------+ 1 row in set (0.00 sec)
當前session對actor_id=178的記錄加for update的共用鎖定： mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | LISA       | MONROE   | +----------+------------+-----------+ 1 row in set (0.00 sec)
	其他session可以查詢該記錄，但是不能對該記錄加共用鎖定，會等待獲得鎖： mysql> select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | LISA       | MONROE   | +----------+------------+-----------+ 1 row in set (0.00 sec)   mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update; 等待
當前session可以對鎖定的記錄進行更新操作，更新後釋放鎖： mysql> update actor set last_name = ‘MONROE T‘ where actor_id = 178; 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)
	其他session獲得鎖，得到其他session提交的記錄： mysql> select actor_id,first_name,last_name from actor where actor_id = 178 for update; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178      | LISA       | MONROE T | +----------+------------+-----------+ 1 row in set (9.59 sec)

mysql 共用鎖定-排它鎖