Concepts and differences
SELECT... LOCK IN SHARE MODE uses the IS lock (intention to share lock), that IS, share locks are added to the qualified rows. In this way, other sessions can read these records or continue to add IS locks, but these records cannot be modified until the execution of your locked session IS completed (otherwise, the lock will wait for timeout).
Select... for update uses the IX lock (intention exclusive lock), that is, exclusive locks are added to the qualified rows, and other sessions cannot add any S lock or X lock to these records. If there are no consistent unlocked reads, other sessions cannot read and modify these records, but innodb has unlocked reads (snapshot reads do not need to be locked). After for update, the snapshot reading operations of other sessions will not be blocked, except select... lock in share mode and select... for update.
Through comparison, it is found that the locking mode of for update blocks the query mode of select... Lock in share mode more than the lock in share mode, and does not block the snapshot reading.
Application scenario
In my opinion, the application scenario of SELECT... LOCK IN SHARE MODE is suitable for writing when there is a relationship between two tables. Take the example of the official mysql document. One table is the child table and the other is the parent table. Suppose a column of child in the child table_ C ID mapped to parent table_ child_ ID column. From a business perspective, I will directly insert a child_ It is risky to record id = 100 to the child table, because this C message may be deleted from the parent table when you first insert it_ child_ For records with id = 100, there is a risk of inconsistency in business data. The correct method is to execute SELECT * from parent where C when reinserting_ child_id=100 LOCK IN SHARE MODE, lock this record in the parent table, and then execute insert into child(child_id) values (100).
However, in the application scenario of the same table, for example, if the remaining quantity of a commodity is calculated in the e-commerce system, the commodity quantity > = 1 needs to be confirmed before the order is generated, and the commodity quantity should be reduced by 1 after the order is generated.
1 select amount from product where product_name='XX'; 2 update product set amount=amount-1 where product_name='XX';
Obviously, there is a problem with the method of 1, because if 1 finds that the amount is 1, but it happens that other session s also buy the product and generate orders, then the amount becomes 0, and there is a problem with the second step.
So is lock in share mode feasible and unreasonable, because when two sessions lock this row of records at the same time, deadlock will occur when the two sessions update again, resulting in transaction rollback. The following are examples of operations (in chronological order)
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 (the same row is also locked)
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 (when session1 update s again, it will cause lock waiting)
mysql> update test_jjj set name='jjj1' where name='jjj';
Session2 (at this time, session2 update will also detect deadlock and roll back session2. Note that the execution time should not exceed the lock waiting timeout detection time of session1, that is, the value set by 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 (at this time, the execution of session1 is completed)
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
It can be seen from this case that the mode of lock in share mode is not applicable in this scenario. We need to use the mode of for update to directly apply the X lock, so as to temporarily block the select... For update operation of session2; The following is an example of operation
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 (at this time, session2 is in lock waiting state, and no result is obtained)
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test_jjj for update;
Session1 (in this case, it can be completed after session1 update is submitted)
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 (after session1 is submitted, the query results of session2 will come out, and you can update and execute again)
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)
Through comparison, lock in share mode is applicable to the consistency requirements when there is a business relationship between two tables, and for update is applicable to the consistency requirements when operating the same table.