作者：志波同學
來源：https://mp.weixin.qq.com/s/8uH-7TD9ZRG4NCya80mOHg 

Mysql InnoDB 排他鎖

用法：select … for update;

例如：select * from goods where id = 1 for update;

排他鎖的申請前提：沒有線程對該結果集中的任何行數據使用排他鎖或共享鎖，否則申請會阻塞。

for update僅適用於InnoDB，且必須在事務塊(BEGIN/COMMIT)中才能生效。在進行事務操作時，通過“for update”語句，MySQL會對查詢結果集中每行數據都添加排他鎖，其他線程對該記錄的更新與刪除操作都會阻塞。排他鎖包含行鎖、表鎖。

場景分析

假設有一張商品表 goods，它包含 id，商品名稱，庫存量三個字段，表結構如下：

一、數據一致性

假設有A、B兩個用戶同時各購買一件 id=1 的商品，用戶A獲取到的庫存量為 1000，用戶B獲取到的庫存量也為 1000，用戶A完成購買後修改該商品的庫存量為 999，用戶B完成購買後修改該商品的庫存量為 999，此時庫存量數據產生了不一致。

有兩種解決方案：

悲觀鎖方案：每次獲取商品時，對該商品加排他鎖。也就是在用戶A獲取獲取 id=1 的商品信息時對該行記錄加鎖，期間其他用戶阻塞等待訪問該記錄。悲觀鎖適合寫入頻繁的場景。

begin;
select * from goods where id = 1 for update;
update goods set stock = stock - 1 where id = 1;
commit;

樂觀鎖方案：每次獲取商品時，不對該商品加鎖。在更新數據的時候需要比較程序中的庫存量與數據庫中的庫存量是否相等，如果相等則進行更新，反之程序重新獲取庫存量，再次進行比較，直到兩個庫存量的數值相等才進行數據更新。樂觀鎖適合讀取頻繁的場景。

#不加鎖獲取 id=1 的商品對象 

select * from goods where id = 1
begin;
#更新 stock 值，這裡需要注意 where 條件 “stock = cur_stock”，只有程序中獲取到的庫存量與數據庫中的庫存量相等才執行更新
update goods set stock = stock - 1 where id = 1 and stock = cur_stock;
commit;

如果我們需要設計一個商城系統，該選擇以上的哪種方案呢？

查詢商品的頻率比下單支付的頻次高，基於以上我可能會優先考慮第二種方案（當然還有其他的方案，這裡只考慮以上兩種方案）。

二、行鎖與表鎖

1、只根據主鍵進行查詢，並且查詢到數據，主鍵字段產生行鎖。

begin;
select * from goods where id = 1 for update;
commit;

2、只根據主鍵進行查詢，沒有查詢到數據，不產生鎖。

begin;
select * from goods where id = 1 for update;
commit;

3、根據主鍵、非主鍵含索引（name）進行查詢，並且查詢到數據，主鍵字段產生行鎖，name字段產生行鎖。

begin;
select * from goods where id = 1 and name='prod11' for update;
commit;

4、根據主鍵、非主鍵含索引（name）進行查詢，沒有查詢到數據，不產生鎖。

begin;
select * from goods where id = 1 and name='prod12' for update;
commit;

5、根據主鍵、非主鍵不含索引（name）進行查詢，並且查詢到數據，如果其他線程按主鍵字段進行再次查詢，則主鍵字段產生行鎖，如果其他線程按非主鍵不含索引字段進行查詢，則非主鍵不含索引字段產生表鎖，如果其他線程按非主鍵含索引字段進行查詢，則非主鍵含索引字段產生行鎖，如果索引值是枚舉類型，mysql也會進行表鎖，這段話有點拗口，大家仔細理解一下。

begin;
select * from goods where id = 1 and name='prod11' for update;
commit;

6、根據主鍵、非主鍵不含索引（name）進行查詢，沒有查詢到數據，不產生鎖。

begin;
select * from goods where id = 1 and name='prod12' for update;
commit;

7、根據非主鍵含索引（name）進行查詢，並且查詢到數據，name字段產生行鎖。

begin;
select * from goods where name='prod11' for update;
commit;

8、根據非主鍵含索引（name）進行查詢，沒有查詢到數據，不產生鎖。

begin;
select * from goods where name='prod11' for update;
commit;

9、根據非主鍵不含索引（name）進行查詢，並且查詢到數據，name字段產生表鎖。

begin;
select * from goods where name='prod11' for update;
commit;

10、根據非主鍵不含索引（name）進行查詢，沒有查詢到數據，name字段產生表鎖。

begin;
select * from goods where name='prod11' for update;
commit;

11、只根據主鍵進行查詢，查詢條件為不等於，並且查詢到數據，主鍵字段產生表鎖。

begin;
select * from goods where id <> 1 for update;
commit;

12、只根據主鍵進行查詢，查詢條件為不等於，沒有查詢到數據，主鍵字段產生表鎖。

begin;
select * from goods where id <> 1 for update;
commit;

13、只根據主鍵進行查詢，查詢條件為 like，並且查詢到數據，主鍵字段產生表鎖。

begin;
select * from goods where id like '1' for update;
commit;

14、只根據主鍵進行查詢，查詢條件為 like，沒有查詢到數據，主鍵字段產生表鎖。

begin;
select * from goods where id like '1' for update;
commit;

測試環境

數據庫版本：5.1.48-community

數據庫引擎：InnoDB Supports transactions, row-level locking, and foreign keys

數據庫隔離策略：REPEATABLE-READ（系統、會話）

總結

1、InnoDB行鎖是通過給索引上的索引項加鎖來實現的，只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖。

2、由於MySQL的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖衝突的。應用設計的時候要注意這一點。

3、當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，另外，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對數據加鎖。

4、即便在條件中使用了索引字段，但是否使用索引來檢索數據是由MySQL通過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB將使用表鎖，而不是行鎖。因此，在分析鎖衝突時，別忘了檢查SQL的執行計劃，以確認是否真正使用了索引。

5、檢索值的數據類型與索引字段不同，雖然MySQL能夠進行數據類型轉換，但卻不會使用索引，從而導致InnoDB使用表鎖。通過用explain檢查兩條SQL的執行計劃，我們可以清楚地看到了這一點。

http://www.2cto.com/database/201208/145888.html

http://www.jb51.net/article/50047.htm

http://blog.csdn.net/kingo0/article/details/43194681

http://www.cnblogs.com/Lawson/archive/2015/11/30/5008741.html

http://www.cnblogs.com/Bob-FD/p/3352216.html

http://www.linuxidc.com/Linux/2014-01/95376.htm