深入理解SELECT ... LOCK IN SHARE MODE和SELECT ... FOR UPDATE

概念和區別

SELECT ... LOCK IN SHARE MODE走的是IS鎖(意向共享鎖)，即在符合條件的rows上都加了共享鎖，這樣的話，其他session可以讀取這些記錄，也可以繼續新增IS鎖，但是無法修改這些記錄直到你這個加鎖的session執行完成(否則直接鎖等待超時)。

SELECT ... FOR UPDATE 走的是IX鎖(意向排它鎖)，即在符合條件的rows上都加了排它鎖，其他session也就無法在這些記錄上新增任何的S鎖或X鎖。如果不存在一致性非鎖定讀的話，那麼其他session是無法讀取和修改這些記錄的，但是innodb有非鎖定讀(快照讀並不需要加鎖)，for update之後並不會阻塞其他session的快照讀取操作，除了select ...lock in share mode和select ... for update這種顯示加鎖的查詢操作。

通過對比，發現for update的加鎖方式無非是比lock in share mode的方式多阻塞了select...lock in share mode的查詢方式，並不會阻塞快照讀。

應用場景

在我看來，SELECT ... LOCK IN SHARE MODE的應用場景適合於兩張表存在關係時的寫操作，拿mysql官方文件的例子來說，一個表是child表，一個是parent表，假設child表的某一列child_id對映到parent表的c_child_id列，那麼從業務角度講，此時我直接insert一條child_id=100記錄到child表是存在風險的，因為剛insert的時候可能在parent表裡刪除了這條c_child_id=100的記錄，那麼業務資料就存在不一致的風險。正確的方法是再插入時執行select * from parent where c_child_id=100 lock in share mode,鎖定了parent表的這條記錄，然後執行insert into child(child_id) values (100)就ok了。

但是如果是同一張表的應用場景，舉個例子，電商系統中計算一種商品的剩餘數量，在產生訂單之前需要確認商品數量>=1,產生訂單之後應該將商品數量減1。
1 select amount from product where product_name='XX';
2 update product set amount=amount-1 where product_name='XX';

顯然1的做法是是有問題，因為如果1查詢出amount為1，但是這時正好其他session也買了該商品併產生了訂單，那麼amount就變成了0，那麼這時第二步再執行就有問題。

 

那麼採用lock in share mode可行嗎，也是不合理的，因為兩個session同時鎖定該行記錄時，這時兩個session再update時必然會產生死鎖導致事務回滾。以下是操作範例(按時間順序)

session1(開啟事務)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test_jjj lock in share mode;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj        |
+-----+------------+
2 rows in set (0.00 sec)


session2(開啟事務,鎖定了相同的行)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test_jjj lock in share mode;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj        |
+-----+------------+
2 rows in set (0.00 sec)


session1(這時session1在update時就會引起鎖等待，等待session2)
mysql> update test_jjj set name='jjj1' where name='jjj';


session2(這時session2同樣update，引起鎖等待，等待session1，接著檢測到死鎖，回滾session2，注意
執行時間不要超過session1的鎖等待超時檢測時間，即不要超過innodb_lock_wait_timeout設定的值)
mysql> update test_jjj set name='jjj1' where name='jjj';
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction


session1(此時session1執行完成)
mysql> update test_jjj set name='jjj1' where name='jjj';
Query OK, 1 row affected (29.20 sec)
Rows matched: 1  Changed: 1  Warnings: 0

備註：可以通過以下三個命令檢視事務的狀態

1：檢視當前的事務
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

殺死事務程式id（就是上面命令的trx_mysql_thread_id列）

2：檢視當前鎖定的事務

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

3：檢視當前等鎖的事務
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

 

通過該案例可知lock in share mode的方式在這個場景中不適用，我們需要使用for  update的方式直接加X鎖，從而短暫地阻塞session2的select...for update操作;以下是操作範例

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


mysql> select * from test_jjj for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj1       |
+-----+------------+
2 rows in set (0.00 sec)


session2(此時session2處於鎖等待狀態，得不到結果)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from test_jjj for update;


session1(這時session1 update之後提交，可完成)
mysql> update test_jjj set name='jjj1' where name='jjj';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0


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


session2(session1提交之後session2剛才的查詢結果就出來了，也就可以再次update往下執行了)
mysql> select * from test_jjj for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj1       |
+-----+------------+
2 rows in set (37.19 sec)
mysql> select * from test_jjj for update;
+-----+------------+
| id  | name       |
+-----+------------+
| 234 | asdasdy123 |
| 123 | jjj1       |
+-----+------------+
2 rows in set (37.19 sec)


mysql> update test_jjj set name='jjj1' where name='jjj';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0


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

通過對比，lock in share mode適用於兩張表存在業務關係時的一致性要求，for  update適用於操作同一張表時的一致性要求。