Database: "Select ... for update" exclusive lock analysis in MySQL

Mysql InnoDB Exclusive Lock

Usage: Select ... for update;

For example: SELECT * from goods where id = 1 for update;

Exclusive lock Application Prerequisites: No thread uses an exclusive or shared lock on any row data in the result set, or the request is blocked.

The for update applies only to InnoDB and must be in the transaction block (Begin/commit) to take effect. During a transactional operation, MySQL adds an exclusive lock to each row of data in the query result set through the "for Update" statement, and other threads will block the update and delete operations on that record. Exclusive locks contain row locks, table locks.

Scenario Analysis

Suppose there is a commodity table goods, which contains an ID, a product name, three fields in stock, and a table structure as follows:

CREATE TABLE `goods` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(100) DEFAULT NULL,  `stock` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

Insert the following data:

INSERT INTO `goods` VALUES (‘1‘, ‘prod11‘, ‘1000‘);INSERT INTO `goods` VALUES (‘2‘, ‘prod12‘, ‘1000‘);INSERT INTO `goods` VALUES (‘3‘, ‘prod13‘, ‘1000‘);INSERT INTO `goods` VALUES (‘4‘, ‘prod14‘, ‘1000‘);INSERT INTO `goods` VALUES (‘5‘, ‘prod15‘, ‘1000‘);INSERT INTO `goods` VALUES (‘6‘, ‘prod16‘, ‘1000‘);INSERT INTO `goods` VALUES (‘7‘, ‘prod17‘, ‘1000‘);INSERT INTO `goods` VALUES (‘8‘, ‘prod18‘, ‘1000‘);INSERT INTO `goods` VALUES (‘9‘, ‘prod19‘, ‘1000‘);

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

First, data consistency

Suppose that a, b two users buy a id=1 at the same time, user A to obtain the inventory amount of 1000, User B to obtain the inventory amount of 1000, user A to complete the purchase of the product to modify the inventory of 999, User B after the purchase of the product to modify the inventory of 999, this time the inventory data produced inconsistencies.

There are two types of solutions:

Pessimistic locking scheme: each time a product is acquired, an exclusive lock is added to the item. That is, when user a gets access to the product information for Id=1, the record is locked while other users are blocked from waiting to access the record. Pessimistic locks are suitable for frequently written scenes.

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

• 1
• 2
• 3
• 4
• 5

Optimistic locking scheme: each time a product is acquired, the item is not locked. When updating the data, you need to compare the inventory in the program with the amount of inventory in the database is equal, if the equivalent is updated, and the program re-obtain the inventory, and then compare again until the two inventory value equal to the data update. Optimistic locking is suitable for frequently read scenes.

#不加锁获取 id=1 的商品对象select * from goods where id = 1begin;#更新 stock 值，这里需要注意 where 条件 “stock = cur_stock”，只有程序中获取到的库存量与数据库中的库存量相等才执行更新update goods set stock = stock - 1 where id = 1 and stock = cur_stock;commit;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

If we need to design a mall system, which of the above options?

The frequency of query products is higher than the number of orders paid, based on the above I might give priority to the second scenario (and of course, there are other scenarios, which only consider the above two scenarios).

Two, row lock and table lock

1, only according to the primary key query, and query to the data, primary key fields generate row locks.

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

• 1
• 2
• 3
• 4

2, only according to the primary key to query, no query to the data, no lock generated.

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

• 1
• 2
• 3
• 4

3, query according to primary key, non-primary key index (name), and query to data, primary key field produces row lock, Name field produces row lock.

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

• 1
• 2
• 3
• 4

4, according to the primary key, non-primary key index (name) to query, no query to the data, no lock generated.

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

• 1
• 2
• 3
• 4

5, query according to primary key, non-primary key without index (name), and query to data, if other threads query again by primary key field, the primary key field produces row lock, if other thread is queried by non-primary key without index field, the non-primary key does not contain index field to produce table lock. If other threads query by non-primary key with indexed fields, the non-primary key contains indexed fields to produce row locks, and if the index value is enumerated type, MySQL will also make table lock, this paragraph is a bit awkward, we have a careful understanding.

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

• 1
• 2
• 3
• 4

6, according to the primary key, the non-primary key does not contain the index (name) to query, did not query to the data, does not produce the lock.

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

• 1
• 2
• 3
• 4

7, query according to the non-primary key index (name), and query to the data, the Name field produces row locks.

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

• 1
• 2
• 3
• 4

8, according to the non-primary key index (name) to query, no query to the data, no lock generated.

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

• 1
• 2
• 3
• 4

9. Query based on non-primary key without index (name), and query to data, Name field produces table lock.

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

• 1
• 2
• 3
• 4

10, according to the non-primary key does not contain the index (name) to query, no query to the data, the Name field produces a table lock.

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

• 1
• 2
• 3
• 4

11, only according to the primary key query, the query condition is not equal, and query to the data, the primary key field produces a table lock.

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

• 1
• 2
• 3
• 4

12, only according to the primary key query, the query condition is not equal, no query to the data, the primary key field generated table lock.

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

• 1
• 2
• 3
• 4

13, only according to the primary key query, the query condition is like, and query to the data, the primary key field produces a table lock.

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

• 1
• 2
• 3
• 4

14, only according to the primary key query, the query condition is like, no query to the data, the primary key field produces a table lock.

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

• 1
• 2
• 3
• 4

Test environment

Database version: 5.1.48-community

Database Engine: InnoDB Supports transactions, Row-level locking, and foreign keys

Database Isolation Policy: Repeatable-read (System, session)

Summarize

1. InnoDB row locks are implemented by locking the index entries on the index, and only if the data is retrieved by index criteria, InnoDB uses row-level locks, otherwise innodb will use table locks.

2, because the MySQL row lock is for the index plus lock, not for the record plus the lock, so although it is access to the record, but if you use the same index key, there will be a lock conflict. Be aware of this when applying design.
3. When a table has multiple indexes, different transactions can use different indexes to lock different rows, and InnoDB uses row locks to lock the data, whether it is using a primary key index, a unique index, or a normal index.
4, even if the index field is used in the condition, but whether to use the index to retrieve the data is determined by MySQL by judging the cost of different execution plans, if MySQL considers the full table scan more efficient, such as for some small table, it will not use the index, in this case InnoDB will use table lock, Instead of a row lock. Therefore, when parsing a lock conflict, don't forget to check the SQL execution plan to verify that the index is actually used.
5. The data type of the retrieved value is different from the index field, although MySQL is capable of data type conversion but does not use the index, which causes InnoDB to use table locks. We can clearly see this by checking the execution plan of two SQL with explain.

Database: "Select ... for update" exclusive lock analysis in MySQL