Mysql InnoDB exclusive lock

Mysql InnoDB exclusive lock

Usage: select … for update;

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

Prerequisite for exclusive lock application: No thread uses exclusive locks or shared locks for any row data in the result set, otherwise the application will be blocked.

for update only applies to InnoDB and must be in a transaction block (BEGIN/COMMIT) to take effect. When performing transaction operations, through the “for update” statement, MySQL will add an exclusive lock to each row of data in the query result set, and other threads will block the update and delete operations of the record. Exclusive locks include row locks and table locks.

Scenario analysis

Suppose there is a product table goods, which contains three fields: id, product name, and inventory. The table structure is 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`),
  UNIQUE KEY `idx_name` (`name`) USING HASH
) ENGINE=InnoDB

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. Data consistency

Assume that two users, A and B, each purchase a product with id=1 at the same time. The inventory obtained by user A is 1000, and the inventory obtained by user B is also 1000. User A modifies the inventory of the product after completing the purchase. The quantity is 999. After completing the purchase, user B modifies the inventory quantity of the product to 999. At this time, the inventory data is inconsistent.

There are two solutions:

Pessimistic locking solution: Every time a product is obtained, an exclusive lock is added to the product. That is, when user A obtains the product information with id=1, the row record is locked. During this period, other users are blocked and waiting to access the record. Pessimistic locking is suitable for scenarios with frequent writes.

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

Optimistic locking solution: Each time a product is obtained, the product is not locked. When updating data, you need to compare whether the inventory in the program is equal to the inventory in the database. If they are equal, update it. Otherwise, the program will re-obtain the inventory and compare it again. The data will not be updated until the two inventory values are equal. . Optimistic locking is suitable for scenarios with frequent reads.

#Get the product object with id=1 without locking
select * from goods where id = 1

begin;
#Update the stock value. Here you need to pay attention to the where condition "stock = cur_stock". The update will only be performed if the inventory obtained in the program is equal to the inventory in the database.
update goods set stock = stock - 1 where id = 1 and stock = cur_stock;
commit;

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

The frequency of checking products is higher than the frequency of placing orders and paying. Based on the above, I may give priority to the second option (of course there are other options, but only the above two options are considered here).

2. Row locks and table locks

1. Query only based on the primary key, and the data is queried, and the primary key field generates a row lock.

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

2. Query only based on the primary key. If no data is queried, no lock is generated.

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

3. Query based on the primary key, non-primary key including index (name), and query the data. The primary key field generates a row lock, and the name field generates a row lock.

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

4. Query based on the primary key, non-primary key including index (name), no data is queried, and no lock is generated.

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

5. Query based on the primary key, non-primary key without index (name), and query the data. If other threads query again based on the primary key field, the primary key field will generate a row lock. If other threads query based on the non-primary key without index field , then the non-primary key does not contain index fields to generate table locks. If other threads query based on the non-primary key containing index fields, the non-primary key containing index fields will generate row locks. If the index value is an enumeration type, mysql will also perform table locks. This This paragraph is a bit hard to pronounce, please understand it carefully.

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

6. Query based on the primary key and non-primary key without index (name). No data is queried and no lock is generated.

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

7. Query based on the non-primary key including index (name), and the data is queried, and the name field generates a row lock.

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

8. Query based on non-primary key including index (name), no data is queried, and no lock is generated.

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

9. Query based on the non-primary key without index (name), and the data is queried, and the name field generates a table lock.

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

10. Query based on the non-primary key without index (name), no data is queried, and the name field generates a table lock.

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

11. Query only based on the primary key, the query condition is not equal, and the data is queried, the primary key field generates a table lock.

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

12. Query only based on the primary key. If the query condition is not equal to, no data is queried, and a table lock is generated on the primary key field.

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

13. Query only based on the primary key, the query condition is like, and the data is queried, the primary key field generates a table lock.

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

14. Query only based on the primary key, the query condition is like, no data is queried, and a table lock is generated on the primary key field.

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

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)

Summary

1. InnoDB row locks are implemented by locking index items on the index. InnoDB uses row-level locks only when data is retrieved through index conditions. Otherwise, InnoDB will use table locks.

2. Since MySQL’s row lock is a lock for the index, not for the record, although records of different rows are accessed, if the same index key is used, a lock conflict will occur. Please pay attention to this when designing your application.
3. When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, whether using primary key index, unique index or ordinary index, InnoDB will use row locks to lock data.
4. Even if an index field is used in the condition, whether to use the index to retrieve data is determined by MySQL by judging the cost of different execution plans. If MySQL believes that a full table scan is more efficient, such as for some small tables, It will not use indexes, in which case InnoDB will use table locks instead of row locks. Therefore, when analyzing lock conflicts, don’t forget to check the SQL execution plan to confirm whether the index is actually used.
5. The data type of the retrieved value is different from the index field. Although MySQL can perform data type conversion, it does not use the index, causing InnoDB to use table locks. We can clearly see this by checking the execution plans of the two SQLs with explain.

The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 76073 people are learning the system