mysql innodb 锁_MySQL/InnoDB锁机制
顯式加鎖
select ... lock in share mode：加 S 鎖
select ... for update：加 X 鎖
MySQL快照讀和當前讀
在一個支持MVCC并發控制的系統中，哪些讀操作是快照讀？哪些操作又是當前讀呢？以MySQL InnoDB為例：
快照讀：簡單的select操作，屬于快照讀，不加鎖。(當然，也有例外，下面會分析)
select * from table where ?;
當前讀：特殊的讀操作，插入/更新/刪除操作，屬于當前讀，需要加鎖。
select * from table where ? lock in share mode;
select * from table where ? for update;
insert into table values (…);
update table set ? where ?;
delete from table where ?;
所有以上的語句，都屬于當前讀，讀取記錄的最新版本。并且，讀取之后，還需要保證其他并發事務不能修改當前記錄，對讀取記錄加鎖。其中，除了第一條語句，對讀取記錄加S鎖 (共享鎖)外，其他的操作，都加的是X鎖 (排它鎖)。
為什么將 插入/更新/刪除 操作，都歸為當前讀？可以看看下面這個 更新 操作，在數據庫中的執行流程：
從圖中，可以看到，一個Update操作的具體流程。當Update SQL被發給MySQL后，MySQL Server會根據where條件，讀取第一條滿足條件的記錄，然后InnoDB引擎會將第一條記錄返回，并加鎖 (current read)。待MySQL Server收到這條加鎖的記錄之后，會再發起一個Update請求，更新這條記錄。一條記錄操作完成，再讀取下一條記錄，直至沒有滿足條件的記錄為止。因此，Update操作內部，就包含了一個當前讀。同理，Delete操作也一樣。Insert操作會稍微有些不同，簡單來說，就是Insert操作可能會觸發Unique Key的沖突檢查，也會進行一個當前讀。
注：根據上圖的交互，針對一條當前讀的SQL語句，InnoDB與MySQL Server的交互，是一條一條進行的，因此，加鎖也是一條一條進行的。先對一條滿足條件的記錄加鎖，返回給MySQL Server，做一些DML操作；然后在讀取下一條加鎖，直至讀取完畢。
select ... lock in share mode
官方文檔解釋
SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify them. The rows read are the latest available, so if they belong to another transaction that has not yet committed, the read blocks until that transaction ends.
在讀取的行上設置一個共享模式的鎖。這個共享鎖允許其它session讀取數據但不允許修改它。 行讀取的是最新的數據，如果他被其它事務使用中而沒有提交，讀取鎖將被阻塞直到那個事務結束。
lock in share mode示例一
session1
mysql>?begin;
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?*?from?t1?where?b?=?4000?lock?in?share?mode;
+----+------+
|?a??|?b????|
+----+------+
|?53?|?4000?|
|?54?|?4000?|
+----+------+
2?rows?in?set?(0.00?sec)
session2
mysql>?begin;
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?*?from?t1?where?b=?4000;
+----+------+
|?a??|?b????|
+----+------+
|?53?|?4000?|
|?54?|?4000?|
+----+------+
2?rows?in?set?(0.00?sec)
mysql>?update?t1?set?b?=?2999?where?a?=?53;
因為session1給查詢結果加了讀鎖，當session2更新a=53這條數據時，session1里加的讀鎖拒絕修改，所以session2事務在這里阻塞，即發生死鎖，當超過一定時間后，該事務執行失敗，這是因為mysql的死鎖檢測起作用。
session1
mysql>?commit;
Query?OK,?0?rows?affected?(0.00?sec)
session1執行commit，提交事務，解除了對查詢結果的讀鎖，也就是共享鎖，這時，session2的update語句執行。
session2
Query?OK,?1?row?affected?(20.83?sec)
Rows?matched:?1??Changed:?1??Warnings:?0
mysql>?select?*?from?t1?where?a?=?53;
+----+------+
|?a??|?b????|
+----+------+
|?53?|?2999?|
+----+------+
1?row?in?set?(0.00?sec)
mysql>?commit;
Query?OK,?0?rows?affected?(0.04?sec)
session1
mysql>?begin;
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?*?from?t1?where?a?=?53;
+----+------+
|?a??|?b????|
+----+------+
|?53?|?2999?|
+----+------+
1?row?in?set?(0.00?sec)
lock in share mode示例二
session1
mysql>?begin;
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?*?from?t1?where?b?=?4000?lock?in?share?mode;
+----+------+
|?a??|?b????|
+----+------+
|?54?|?4000?|
+----+------+
1?row?in?set?(0.00?sec)
session2
mysql>?begin;
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?*?from?t1?where?b=?4000?lock?in?share?mode;
+----+------+
|?a??|?b????|
+----+------+
|?54?|?4000?|
+----+------+
1?row?in?set?(0.00?sec)
可以看出，當session1加了共享鎖后，其他事務還可以給其加共享鎖。。。。
select ... for update
官方解釋
SELECT ... FOR UPDATE sets an exclusive lock on the rows read. An exclusive lock prevents other sessions from accessing the rows for reading or writing.
在讀取行上設置一個排他鎖。組織其他session讀取或者寫入行數據
select ... for update示例一
session1
mysql>?begin;
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?*?from?t1?where?b?
+----+------+
|?a??|?b????|
+----+------+
|?51?|?2000?|
|?52?|?2999?|
|?53?|?2999?|
|?57?|?2000?|
+----+------+
4?rows?in?set?(0.06?sec)
mysql>
session2
mysql>?begin;
Query?OK,?0?rows?affected?(0.00?sec)
mysql>?select?*?from?t1?where?b?
+----+------+
|?a??|?b????|
+----+------+
|?51?|?2000?|
|?52?|?2999?|
|?53?|?2999?|
|?57?|?2000?|
+----+------+
4?rows?in?set?(0.00?sec)
mysql>?select?*?from?t1?where?b?
ERROR?1205?(HY000):?Lock?wait?timeout?exceeded;?try?restarting?transaction
mysql>
在該事務中在加排它鎖，過一段時間后，報錯。但能讀取數據。
最近寫的文章的參考，希望對你有幫助。
========END========
總結
以上是生活随笔為你收集整理的mysql innodb 锁_MySQL/InnoDB锁机制的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： mysql 6.5安装_RedHat6.
- 下一篇： mysql 字段属性命令_mysql