This paper first introduces the concept of optimistic lock, then introduces the implementation principle of optimistic lock, and finally demonstrates the implementation of optimistic lock with a spring boot project.

catalogue

What is optimistic lock

Implementation principle of optimistic lock

actual combat

What is optimistic lock

During database operation, optimistic lock always assumes that the query will not modify the data, so it will not lock the queried data. Only when the data is really updated can it detect whether there is a conflict. If there is a conflict, the update fails. Optimistic locking can improve the efficiency of concurrent query, and its implementation is very simple.

Implementation principle of optimistic lock

The implementation principle of optimistic lock is to add a version field in the table. Each time the database is updated, check whether the version field meets the expected value. If so, update it, otherwise it will not be updated.

Lifting Chestnut:

There is a user's deposit form account, in which there is a deposit record of Xiaoming, showing that there are 1000 yuan in the account. The table structure is very simple:

id	user_name	account_num	update_time
1	Xiao Ming	1000	null

Now Xiao Ming wants to withdraw 50 yuan from his account. If he doesn't use the lock, the logic of the background will be as follows:

a1. First find out Xiaoming's deposit record select * from account where user_name = "Xiao Ming", the balance found is account_num1

a2. Try to update the table update account set account after adding 50 to the deposit balance_ num=account_ num1-50 where user_ Name = "Xiao Ming"

It seems like there's no problem, but it's not.

While Xiaoming is operating his own account, Xiaohua is also paying Xiaoming back the money in the amount of 100:

b1. Find out Xiaoming's deposit record first select * from account where user_name = "Xiao Ming", the balance found is account_num2

b2. Try to update the table update account set account after adding 50 to the deposit balance_ num=account_ num2+100 where user_ Name = "Xiao Ming"

Xiaoming takes 50 and Xiaohua returns 100. Theoretically, there should be 1050 in Xiaoming's account.

However, because there is no lock and the execution sequence of a1, a2, b1 and b2 above is random, the result may not be expected.

We assume that the order of execution is a1,b1,a2,b2. The balance found by Xiaoming and Xiaohua is 1000. Xiaoming successfully withdrew the money and the balance is set to 950. However, due to the final update of b2, the balance of Xiaoming's account will be 1100 (Xiaoming is happy, but the bank is not happy); If the order of execution is A1, B1, b2 and a2, because a2 is last updated, Xiaoming's account balance will be 950 (Xiaohua is not happy and the money will be paid in vain).

Optimistic locking is used to solve the above concurrency problems. Let's see how to solve them.

Add a field version in the table (the name doesn't matter):

id	user_name	account_num	update_time	version
1	Xiao Ming	1000	null	1

Xiao Ming still withdraws 50 yuan:

a1. First find out Xiaoming's deposit record select * from account where user_name = "Xiao Ming", the balance found is account_num1,version is version1

a2. Try to update the table update account set account after adding 50 to the deposit balance_ num=account_ num1-50, version = version+1 where user_name = "Xiao Ming" and version=version1

Xiaohuacun 100:

b1. Find out Xiaoming's deposit record first select * from account where user_name = "Xiao Ming", the balance found is account_num2, version is version2

b2. Try to update the table update account set account after adding 50 to the deposit balance_ num=account_ num2+100, version=version+1 where user_ Name = "Xiaoming" and version=version2

Note that after the optimistic lock is added, a where condition version is added when updating the record, and an attempt is made to add version+1.

1. If the execution sequence is still A1, B1, a2 and b2, since version+1 changes to 2 after a2 is updated successfully, when b2 attempts to update, because version=1 in the where condition does not meet, the update statement will not be executed, Xiaoming's balance will change to 950, and Xiaohua fails to repay the money;

2. Similarly, if the execution order is A1, B1, B2 and A2, Xiaoming fails to withdraw the money, Xiaohua returns the money successfully, and the balance becomes 1100;

3. Or the execution sequence is A1, A2, B1 and B2. After Xiaoming withdraws the money, the balance becomes 950 and the version becomes 2. At this time, Xiaohua pays back the money and the update is still successful. The balance becomes 1050 and the version becomes 3. Both of them are updated successfully.

Someone may ask, in case 1 and case 2, some people failed to update successfully. What should I do. It should be stated that the function of optimistic lock is to prevent inconsistent data updates during concurrency. In fact, it has been implemented here. As for how to deal with the failed update, you need to implement a retry mechanism in the background (shown in the next section), which is not within the scope of the optimistic lock function.

actual combat

Let's take a springboot project as an example to see how the optimistic lock is implemented, which also provides a retry mechanism.

Create an account table:

CREATE TABLE `account_wallet` (
  `id` int(11) NOT NULL COMMENT 'User wallet primary key',
  `user_open_id` varchar(64) DEFAULT NULL COMMENT 'Unique user number of the user center',
  `user_amount` decimal(10,5) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  `pay_password` varchar(64) DEFAULT NULL,
  `is_open` int(11) DEFAULT NULL COMMENT '0:Means the payment password is not opened, 1:Develop payment password on behalf of',
  `check_key` varchar(64) DEFAULT NULL COMMENT 'When the platform changes the user balance, first verify it key Value, otherwise the user balance cannot be changed',
  `version` int(11) DEFAULT NULL COMMENT 'be based on mysql Optimistic lock to solve concurrent access',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Insert a record into the table:

INSERT INTO `account_wallet` (`id`, `user_open_id`, `user_amount`, `create_time`, `update_time`, `pay_password`, `is_open`, `check_key`, `version`)
VALUES
	(1, '1', 1000.00000, NULL, NULL, NULL, NULL, 'haha', 1);

The project structure is as follows:

The configuration information is as follows:

# apply name
spring.application.name=optimiclock

# Application service WEB access port
server.port=8087

spring.datasource.url=jdbc:mysql://IP:port/demo?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=GMT%2B8
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

#Entity class alias
mybatis.type-aliases-package=com.example.demo.model
#Location of mapping file
mybatis.mapper-locations=classpath:mapper/*.xml

mybatis.configuration.map-underscore-to-camel-case=true

dao layer:

The mybatis generator plug-in I use here is generated directly. There are a lot of specific methods on the Internet, but I don't want to introduce them more.

service layer:

public interface TestService {

    AccountWallet selectByOpenId(String openId);

    int updateAccountWallet(AccountWallet record);

    List<User> initUsers();

    void process(User user) throws InterruptedException;
}

The selectByOpenId method is used to query deposit records:

<select id="selectByOpenId" resultType="com.example.demo.model.AccountWallet">
    select
    <include refid="Base_Column_List" />
    from account_wallet
    where user_open_id = #{openId,jdbcType=VARCHAR}
  </select>

updateAccountWallet is used to update deposit records:

<update id="updateAccountWallet">
    <![CDATA[
          	update account_wallet set 
          	user_amount = #{userAmount,jdbcType=DECIMAL}, 
          	version = version + 1 
          	where id =#{id,jdbcType=INTEGER} and version = #{version,jdbcType=INTEGER}
           ]]>
  </update>

initUsers is used to initialize users. For demonstration, I initialized 10 users and randomly specified whether the user saves or withdraws, and the amount is also randomly specified.

public List<User> initUsers() {
        List<User> res = new ArrayList<>();
        Random random = new Random();

        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setUserName(i + "");
            user.setAmount((String.valueOf(random.nextInt(10) * 5)));//Amount of randomly assigned user access
            user.setOpenId("1");
            res.add(user);
            user.setOpenType(random.nextBoolean());//Randomly specify whether users save or retrieve
        }

        return res;
    }

process is used to simulate deposit and withdrawal operations.

Here is the retry mechanism. First, set a retry time for the user. I set 35 seconds here. The user will repeatedly try to update the data within this time period until it succeeds or the timeout ends.

public void process(User user) throws InterruptedException {
        //User startup time
        long startTime = System.currentTimeMillis();
        Boolean success = false;
        String message = "";
        //During the while time, it will try to update continuously until it succeeds
        while ((startTime + 35000L) >= System.currentTimeMillis()) {
            AccountWallet accountWallet = selectByOpenId("1");
            //cash is the amount that the user wants to deposit or withdraw
            BigDecimal cash = BigDecimal.valueOf(Double.parseDouble(user.getAmount()));
            cash.doubleValue();
            cash.floatValue();
            String add = "+";//+Indicates deposit, - indicates withdrawal
            BigDecimal original = accountWallet.getUserAmount();
            if (user.getOpenType()) {
                accountWallet.setUserAmount(accountWallet.getUserAmount().add(cash));
            } else {
                add = "-";
                accountWallet.setUserAmount(accountWallet.getUserAmount().subtract(cash));
            }

            //Attempt to update database
            int res = updateAccountWallet(accountWallet);
            if (res == 1) {
                success = true;
                message = "success" + " Base: " + original + add + cash + " After update:" + accountWallet.getUserAmount();
                break;
            }

            //Try the update again after the break
            Thread.sleep(10L);
        }
        if (success) {
            System.out.println(message);
        } else {
            System.out.println("Failed!");
        }

    }

controller layer: parallel stream is used to simulate concurrency.

@RestController
@Slf4j
public class TestController {
    @Autowired
    TestService accountWalletService;

    @PostMapping(value="/test")
    @ResponseBody
    public void test() {
        List<User> users = accountWalletService.initUsers();
        //Simulation concurrency
        users.parallelStream().forEach(b -> {
            try {
                accountWalletService.process(b);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        });

    }
}

The program will simulate 10 users to operate the same record in the database concurrently, run the program and call the test interface:

The messages printed in the IDE are as follows:

It can be seen from the printed message that the concurrent access of 10 users was successful, and the deposit balance was correctly updated.

To view records in the database:

You can see that the deposit balance is updated correctly, and the version is successfully updated 10 times.  

OK, that's all for the introduction of optimistic lock, and the source code is here lisz112/optimicLock