What is the principle of optimistic lock deduction inventory in MySQL?

1 Basic knowledge

Deducting inventory in the e-commerce system is a very critical operation. For example, in the flash sale system, overselling must be prevented. , if the merchant sets up 100 pieces of inventory but ends up selling 1,000 pieces, this will cause a financial loss. The following statement is generally used when deducting inventory:

udpate goods set stock = stock - #{acquire} 
where sku_id = #{skuId} and stock - #{acquire} >= 0

Let us analyze how this statement can effectively prevent inventory oversold in order to protect inventory resources. In the demonstration of this article, we use the MySQL Innodb engine and set the isolation level to repeatable read.

1.1 Shared lock and exclusive lock

Shared lock (share Lock) is also called read lock. The statement to implement the shared lock is as follows:

select lock in share mode

Exclusive lock (exclusive Lock) is also called write lock. The statement to implement exclusive lock is as follows:

select for update
update
delete
insert

The compatibility relationship between shared lock and exclusive lock is as follows:

us Analyze the above compatibility relationship through examples. First, create a test table and write the test data:

CREATE TABLE `test_account` (
  `id` bigint(20) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `account` bigint(20) DEFAULT NULL,
  `version` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `test_account`(`id`,`name`,`account`,`version`) values (1,'A',100,1);
insert  into `test_account`(`id`,`name`,`account`,`version`) values (2,'B',200,1);
insert  into `test_account`(`id`,`name`,`account`,`version`) values (3,'C',300,1);

(1) Read and read compatibility

Shared lock and shared lock In the following example, session1 can execute the query at time t3 and session2 can obtain the expected results by executing the query at time t4:

(2) Read and write mutual exclusion

Shared locks and exclusive locks are mutually exclusive. In the following example, session1 adds a shared lock at time t3, and the result can be read correctly, but session2 tries to add an exclusive lock at time t4, but this When the lock is occupied by session1, session2 needs to wait. When session1 does not release the lock for a long time, session2 throws a lock timeout exception:

(3) Write-write interaction Exclusion

Exclusive locks and exclusive locks are mutually exclusive. In the following example, session1 adds an exclusive lock at time t3, and the result can be read correctly, but session2 tries to add an exclusive lock at time t4. lock, but the lock is occupied by session1 at this time, and session2 needs to wait. When session1 does not release the lock for a long time, session2 throws a lock timeout exception:

1.2 Current Reading and Snapshot Read

MySQL Innodb storage engine is implemented based on the multi-version concurrency control protocol MVCC. In MVCC concurrency control, read operations can be divided into snapshot reads and current reads.

Snapshot reading does not require locking. What is read is the visible version of the record, which may be a historical version. Similar to an order snapshot, even if the price of the product changes after the user places the order, the order snapshot remains unchanged. The current read statement is implemented as follows:

select

In order to read the latest version of the record without being modified by other transactions, the current record needs to be locked. The implementation of the current read statement is as follows:

select lock in share mode
select for update
update
delete
insert

We analyze the snapshot read and current read through an example. Session2 modified the record at t4 and submitted it at t5. Session1 performed a snapshot read at t6 and read this The result was 100 when the transaction started. The current read was performed at t7, and the latest version of the result 101 was read:

What is the current reading process like? We take update as an example to analyze the current reading process:

The first time the program instance issues a current read request, the storage engine returns the first record that satisfies the where condition and locks it. , the program instance then issues an update request, and the storage causes the operation to complete the response successfully. Execute in sequence until all records that satisfy the where condition are executed.

Here we make some extensions. The RR level provides two mechanisms to avoid phantom reading problems: The first method is snapshot reading, which reads the snapshot when the current transaction is started. One method for current reads is to use the Next-Key Lock mechanism to prevent phantom reads.

2 Optimistic Locking Principle

We integrate the above knowledge through a question: There are two threads executing the following statements at the same time. Will the account value of the record id=1 be successful? Deduction twice?

update test_account set account = account - 100, version = version + 1 
where id = 1 and version = 1

The above statement uses optimistic locking. We know that optimistic locking protects resources, so the answer is not to deduct twice, but we cannot stop there. We need to combine the knowledge in Chapter 1 for further analysis. :

At time t2, session1 and session2 execute update operations at the same time. Since the update will add an exclusive lock, only one of the two can succeed: session1 succeeds, and session2 blocks and waits for the queue to be queued. It locks release.

At time t3, session1 commits the transaction to release the exclusive lock. At this time, session2 acquires the lock for current reading, but at this time the version value of the record with id=1 has become 2, and the executed statement cannot query the data to be updated, so there is no The record is updated.

3 Principle of inventory deduction

If you understand the optimistic locking principle in Chapter 2, then the principle of inventory deduction is already obvious. We assume that there is only 1 item left in stock. If two threads Will oversolding occur if inventory is reduced at the same time?

At t2, session1 and session2 execute updatek to reduce inventory at the same time. Since update will add an exclusive lock, only one of the two can succeed: session1 succeeds, and session2 blocks and waits. Exclusive lock released.

At time t3, session1 commits the transaction to release the exclusive lock. At this time, session2 acquires the lock for current reading, but at this time, the inventory of product 1 has become 0, which is no longer satisfied (where stock - 1 >= 0) Condition: The execution statement cannot query the data to be updated, so no records are updated.

The above is the detailed content of What is the principle of optimistic lock deduction inventory in MySQL?. For more information, please follow other related articles on the PHP Chinese website!