Welcome to pay attention to the public account “JAVA Front” to view more wonderful sharing articles, mainly including source code analysis, practical application, architecture thinking, workplace sharing, product thinking and so on, at the same time, welcome to add my personal wechat “JAVA_front” to communicate and learn together

1 Basic Knowledge

Inventory reduction is a very critical operation in the e-commerce system. For example, overselling must be prevented in the second kill system. If the merchant sets 100 pieces of inventory but sells 1000 pieces in the end, capital loss will occur. The following statements are generally used when deducting inventory:

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

This statement can protect inventory resources from oversold, let’s examine why this statement works. This article uses the MySQL Innodb engine for demonstration with an isolation level of repeatable reads.

1.1 Shared and exclusive Locks

A share Lock is also called a read Lock. The statement for implementing a share Lock is as follows:

select lock in share mode
Copy the code

An exclusive Lock is also known as a write Lock.

select for update
update
delete
insert
Copy the code

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

We analyze the compatibility relationship through an example, first build a test table and write 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 ',' version ') values (1,'A',100,1); Insert into 'test_account' (' id ', 'name ',' version ') values (2,'B',200,1); insert into 'test_account' (' id ', 'name ',' version ') values (2,'B',200,1); Insert into 'test_account' (' id ', 'name ',' version ') values (3,'C',300,1);Copy the code

(1) Read compatible

Shared locks are compatible with shared locks. In the following example, session1 at t3 and session2 at T4 can obtain the expected results:

(2) Read and write are mutually exclusive

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

(3) Write mutually exclusive

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

1.2 Current Read and Snapshot Read

MySQL Innodb storage engine implements multi-version concurrency control protocol MVCC. In MVCC concurrency control, read operations can be divided into snapshot read and current read.

Snapshot reading does not need to be locked. The snapshot reading is the record visible version, which may be the historical version. An analogy to an order snapshot is that the price of an item changes after the user places an order, but the order snapshot does not change. Implement the current read statement as follows:

select
Copy the code

The current read requires locking. The latest version of the record is read. Locking ensures that the current record will not be modified by other transactions while reading. Implement the current read statement as follows:

select lock in share mode
select for update
update
delete
insert
Copy the code

We analyze snapshot reads and current reads with an example, session2 changes the record at t4 and commits it at T5, session1 makes a snapshot read at T6 and reads the result 100 at the start of the transaction, session1 makes a current read at T7 and reads the latest version 101:

What is the current read flow? Take update as an example:

The first time the program instance sends the current read request, the storage engine returns the first record that meets the WHERE condition and locks it, the program instance sends the update request, and the storage causes the operation to complete and responds successfully. Execute until all records that meet the WHERE condition have been executed.

The RR level provides two mechanisms to avoid illusory reading problems. The first is snapshot reading, which reads a snapshot of the current transaction. The second approach is for the current read and prevents phantom reads from relying on the next-key Lock mechanism.

2 Principle of optimistic locking

We put this together with a question: if two threads execute the following statement at the same time, will the account value of the record id=1 be successfully deducted twice?

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

We know that optimistic locks protect resources, so we won’t deduct twice. But we can’t stop there.

Update session1 and session2 at t2 at the same time, because the update will add an exclusive lock, session1 succeeds, session2 blocks and waits for the exclusive lock to be released.

At t3, session1 commits transaction to release the exclusive lock. At this time, session2 obtains the lock for the current read, but at this time, the record version value of ID =1 has changed to 2, and the execution statement cannot query the data to be updated, so no record is updated.

3. The principle of inventory deduction

If you understand the optimistic locking principle in Chapter 2, the principle of destocking is already obvious. If we assume that there is only one item left in stock, will it be oversold if both threads perform destocking at the same time?

Session1 succeeds, and session2 blocks until the exclusive lock is released.

At t3, session1 commits transaction to release the exclusive lock. At this time, session2 obtains the lock for the current read, but at this time, the inventory of commodity 1 has become 0, which does not meet the condition (where stock-1 >= 0), and the execution statement cannot query the data to be updated, so no record is updated.

4 Article Summary

First, we analyzed two groups of basic knowledge: shared and exclusive locks, snapshot read and current read. Secondly, we fuse the two groups of basic knowledge and analyze how optimistic lock works. Third, we start from the optimistic lock principle, and finally understand the deduction of inventory principle, I hope this article is helpful to you.

Welcome to pay attention to the public account “JAVA Front” to view more wonderful sharing articles, mainly including source code analysis, practical application, architecture thinking, workplace sharing, product thinking and so on, at the same time, welcome to add my personal wechat “JAVA_front” to communicate and learn together