Thoughts triggered by deductions

Thoughts triggered by deductions

Foreword:

The scenario of deduction is not very common in daily development scenarios, but there are countless interview questions that diverge from this scenario in the interview. The main reason is that the deduction scenario is more sensitive and requires high security, just like asking you a spike (high performance requirements) in an interview. Recently, I encountered a deduction scene, encountered some problems, and recorded some thoughts. Because of the confidentiality of the system, I use my own code to describe the problem. Deduction business data: here are two tables that simulate the core of deduction actions, account and fund flow. When the business needs deduction, the account balance is deducted and the fund flow is updated at the same time. **

One: environment setup

Mainly used: Mysql, SpringBoot, MyBatis

1. Database

Fund account table

CREATE TABLE `my_capital_account` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `current_balance` bigint NOT NULL COMMENT '余额',
  `account_name` varchar(64) NOT NULL DEFAULT '' COMMENT '账户名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 

Fund flow table

CREATE TABLE `my_capital_log` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `account_id` bigint NOT NULL COMMENT '账户id',
  `change_amount` bigint NOT NULL COMMENT '变更金额(可为负数)',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The fund account id is recorded in the fund flow table, and the two tables belong to a one-to-many relationship

Basic account data: The

Insert picture description here


flow of funds is temporarily empty.

Description

Every time the execution is completed, the account balance will be restored to the above state. The
calculation of funds should use BigDecimal, here for simplification, use long directly

2. Basic code

Entity

  • Fund account entity class
package com.example.demo;

import lombok.Data;

@Data
public class MyCapitalAccount {
    private Long id;
    private String accountName;
    private Long currentBalance;
}
  • Fund flow entity class
package com.example.demo;

import lombok.Data;

@Data
public class MyCapitalLog {
    private Long id;
    private Long accountId;
    private Long changeAmount;
}
  • Mapper

Capital Flow DAO

package com.example.demo;

import org.apache.ibatis.annotations.*;

@Mapper
public interface MyCapitalDAO {
    @Results(id = "myCapitalAccount", value = {
            @Result(property = "id", column = "id"),
            @Result(property = "accountName", column = "account_name"),
            @Result(property = "currentBalance", column = "current_balance"),
    })
    @Select(value = "SELECT * FROM my_capital_account WHERE id=#{id}")
    MyCapitalAccount getById(Long id);

    @Update(value = "UPDATE my_capital_account set current_balance=#{banlance} WHERE id=#{id}")
    int coverAccountBalance(@Param("id") Long id, @Param("banlance") Long banlance);
}

Capital Flow DAO

package com.example.demo;

import org.apache.ibatis.annotations.*;

@Mapper
public interface MyCapitalLogDAO {
    @Results(id = "myCapitalLog", value = {
            @Result(property = "id", column = "id"),
            @Result(property = "accountId", column = "account_id"),
            @Result(property = "changeAmount", column = "change_amount"),
    })
    @Select(value = "SELECT * FROM my_capital_log WHERE id=#{id}")
    MyCapitalLog getById(Long id);

    @Insert(value = "INSERT INTO `my_capital_log`(`account_id`, `change_amount`) VALUES (#{accountId}, #{changeAmount});")
    int insert(MyCapitalLog log);
}
  • Service
package com.example.demo;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class CapitalAccountService {
   @Autowired
   private MyCapitalDAO myCapitalDAO;
   @Autowired
   private MyCapitalLogDAO myCapitalLogDAO;

   public MyCapitalAccount getAccountById(Long id) {
       return myCapitalDAO.getById(id);
   }

   /**
    * 修改账户金额
    *
    * @param id
    * @param changeAmount
    * @return
    */
   public boolean coverAccount(Long id, Long changeAmount) {
       if (changeAmount == 0) {
           return true;
       }
       Long nowBalance = myCapitalAccountDb.getCurrentBalance() + changeAmount;
       int i = myCapitalDAO.coverAccountBalance(id, nowBalance);
       MyCapitalLog myCapitalLog = new MyCapitalLog();
       myCapitalLog.setAccountId(id);
       myCapitalLog.setChangeAmount(changeAmount);
       int insert = myCapitalLogDAO.insert(myCapitalLog);
       if (i <= 0 && insert <= 0) {
           //任何一步没有成功更新,抛出异常回滚
           throw new IllegalStateException("操作资金失败");
       }
       return true;
   }

}
  • Controller
package com.example.demo;

import com.sun.istack.internal.NotNull;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("my/test")
@Validated
public class MyTestController {
   @Autowired
   CapitalAccountService capitalAccountService;

   @GetMapping("getById")
   @ResponseBody
   public MyCapitalAccount test(@NotNull Long id) {
       return capitalAccountService.getAccountById(id);
   }


   @GetMapping("coverAccount")
   @ResponseBody
   public boolean coverAccount(@NotNull Long id, @NotNull Long changeAmount) {
       return capitalAccountService.coverAccount(id, changeAmount);
   }

}
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mytest?useUnicode=true&character_set_server=utf8mb4&autoReconnect=true&useSSL=false&verifyServerCertificate=false&allowPublicKeyRetrieval=true&zeroDateTimeBehavior=CONVERT_TO_NULL
spring.datasource.hikari.maximum-pool-size=128
spring.datasource.hikari.minimum-idle=10


logging.level.root=info
server.compression.enabled=true
#mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

Two: scenes and problems

scene 1

Users have recharge and withdrawal requirements, and need to add or reduce money to an account.

Here is 10 yuan for Zhang Sanjia

Insert picture description here


. It's a success.

Question 1: Over-deduction problem

The code will have an over-deduction problem:
this will not be demonstrated, the amount of the account cannot be a negative number

solution

1. Memory judgment (the default method used in the examples in this article)
 public boolean coverAccount(Long id, Long changeAmount) {
        if (changeAmount == 0) {
            return true;
        }
          MyCapitalAccount accountDbOut = myCapitalDAO.getById(id);
        if (changeAmount < 0 && accountDbOut.getCurrentBalance() + changeAmount  < 0) {
            throw new RuntimeException("余额不足");
        }
        Long nowBalance = myCapitalAccountDb.getCurrentBalance() + changeAmount;
        int i = myCapitalDAO.coverAccountBalance(id, nowBalance);
        MyCapitalLog myCapitalLog = new MyCapitalLog();
        myCapitalLog.setAccountId(id);
        myCapitalLog.setChangeAmount(changeAmount);
        int insert = myCapitalLogDAO.insert(myCapitalLog);
        if (i <= 0 && insert <= 0) {
            //任何一步没有成功更新,抛出异常回滚
            throw new IllegalStateException("操作资金失败");
        }
        return true;
    }
2. Use database restrictions
Insert picture description here


Set the type of fund balance to unsigned, when trying to change the fund to a negative number

Insert picture description here

Question 2: The second type of missing updates

Multi-threaded update occurs when the last update is overwritten. ( Two types of missing updates )

Problem recurrence

Prepare two threads, in order to reproduce 100%, use idea multi-thread debugging (click here if you don't understand this ).
Each thread will be deducted 600 yuan.


    @GetMapping("multi/thread/coverAccount")
    @ResponseBody
    public boolean multiThreadCoverAccount() {
        ExecutorService executor = Executors.newCachedThreadPool();
        Long id = 1L;
        Long changeAmount = 600L;
        for (int i = 0; i < 2; i++) {
            executor.submit(() -> capitalAccountService.coverAccount(id, changeAmount));
        }
        return true;
    }

result

Insert picture description here


Insert picture description here


1200 money, but only 600 credited

solution

1: Optimistic lock

The database uses optimistic locking. In order to prevent ABA problems, the version field is added as the basis for optimistic locking judgment. (Demonstration omitted)

Advantages: no loss of code performance, but also safe.
Disadvantages: collisions will cause the need to retry. For ToB systems, performance requirements are not prominent, and the success rate is higher.

2: Pessimistic lock

i. The for update lock must be in the transaction to take effect
ii. The query statement of the for update lock must apply a valid index, otherwise row locks and table locks will occur, which will seriously affect performance.
iii. The for update row lock is only effective for the main library, that is, the existence of this statement will restrict your system to use the slave library. If you use it, you must force the for update statement to be specified for the main library query, otherwise for update The row lock will be invalidated.

Use distributed lock, lock the account id and
modify the code to update the account as follows ( note: local lock simulation, the real scene should use the distributed lock to lock the account id )
here is a double check, the first check can be pre-filtered, If the balance is insufficient before the lock is obtained, there is no need to take the lock to waste time. This can save performance (here is a foreshadowing)

   public boolean coverAccount(Long id, Long changeAmount) {
        if (changeAmount == 0) {
            return true;
        }
        //超扣解决方案
        MyCapitalAccount accountDbOut = myCapitalDAO.getById(id);
        if (changeAmount < 0 && accountDbOut.getCurrentBalance() + changeAmount  < 0) {
            throw new RuntimeException("余额不足");
        }
		//并发解决方案
        LOCK.lock();
        try {
            //double check
            MyCapitalAccount myCapitalAccountDb = myCapitalDAO.getById(id);
            if (changeAmount < 0 && myCapitalAccountDb.getCurrentBalance() + changeAmount  < 0) {
                throw new RuntimeException("余额不足");
            }
            Long nowBalance = myCapitalAccountDb.getCurrentBalance() + changeAmount;
            int i = myCapitalDAO.coverAccountBalance(id, nowBalance);
            MyCapitalLog myCapitalLog = new MyCapitalLog();
            myCapitalLog.setAccountId(id);
            myCapitalLog.setChangeAmount(changeAmount);
            int insert = myCapitalLogDAO.insert(myCapitalLog);
            if (i <= 0 && insert <= 0) {
                //任何一步没有成功更新,抛出异常回滚
                throw new IllegalStateException("操作资金失败");
            }
        } finally {
            LOCK.unlock();
        }

        return true;
    }

100 threads, each thread plus 10 yuan result:

Insert picture description here

Use UPDATE my_capital_account set current_balance=current_balance+#{changeAmount} WHERE id=#{id}
this syntax will open the database write lock, the read will use the current read, the write process is serial (actually the same as java pessimistic lock, if Turn on the transaction granularity will become larger) It is said on the Internet that this method cannot guarantee transactionality, this article has a detailed introduction. In this case, let's test it.
New method:

public boolean updateAccountBalance(Long id, Long changeAmount) {
        if (changeAmount == 0) {
            return true;
        }
        //超扣解决方案
        MyCapitalAccount accountDbOut = myCapitalDAO.getById(id);
        if (changeAmount < 0 && accountDbOut.getCurrentBalance() + changeAmount  < 0) {
            throw new RuntimeException("余额不足");
        }
        //并发解决方案
        LOCK.lock();
        try {
            MyCapitalAccount myCapitalAccountDb = myCapitalDAO.getById(id);
            if (changeAmount < 0 && myCapitalAccountDb.getCurrentBalance() + changeAmount  < 0) {
                throw new RuntimeException("余额不足");
            }
            int i = myCapitalDAO.updateAccountBalance(id, changeAmount);
            MyCapitalLog myCapitalLog = new MyCapitalLog();
            myCapitalLog.setAccountId(id);
            myCapitalLog.setChangeAmount(changeAmount);
            int insert = myCapitalLogDAO.insert(myCapitalLog);
            if (i <= 0 && insert <= 0) {
                //任何一步没有成功更新,抛出异常回滚
                throw new IllegalStateException("操作资金失败");
            }

        } finally {
            LOCK.unlock();
        }
        return true;
    }
 @Update(value = "UPDATE my_capital_account set current_balance=current_balance+#{changeAmount} WHERE id=#{id}")
    int updateAccountBalance(@Param("id") Long id, @Param("changeAmount") Long changeAmount);

Use 100 threads to increase by 10 yuan each time.

 @GetMapping("multi/thread/updateAccount")
    @ResponseBody
    public boolean testUpdate() {
        ExecutorService executor = Executors.newCachedThreadPool();
        Long id = 2L;
        Long changeAmount = 10L;
        int size = 100;
        CountDownLatch countDownLatch = new CountDownLatch(size);
        for (int i = 0; i < size; i++) {
            executor.submit(() -> {
                countDownLatch.countDown();
                try {
            		countDownLatch.await(20, TimeUnit.SECONDS);
       			 } catch (InterruptedException e) {
           			 e.printStackTrace();
        		}
                capitalAccountService.updateAccountBalance(id, changeAmount);
            });
        }
        return true;
    }

I experimented 9 times in a row (the data with id 2 is used here): the

Insert picture description here


amount has increased from 1,000 to 10,000.

Question 3: The records of funds and cash flow cannot guarantee atomicity.

Problem recurrence

  public boolean coverAccount(Long id, Long changeAmount) {
        if (changeAmount == 0) {
            return true;
        }
        MyCapitalAccount accountDbOut = myCapitalDAO.getById(id);
        if (changeAmount < 0 && accountDbOut.getCurrentBalance() + changeAmount < 0) {
            System.out.println("外部判断余额不足");
            throw new RuntimeException("余额不足");
        }

        LOCK.lock();
        try {
            //double check
            MyCapitalAccount myCapitalAccountDb = myCapitalDAO.getById(id);
            if (changeAmount < 0 && myCapitalAccountDb.getCurrentBalance() + changeAmount < 0) {
                System.out.println("内部判断余额不足");
                throw new RuntimeException("余额不足");
            }
            Long nowBalance = myCapitalAccountDb.getCurrentBalance() + changeAmount;
            int i = myCapitalDAO.coverAccountBalance(id, nowBalance);
            MyCapitalLog myCapitalLog = new MyCapitalLog();
            myCapitalLog.setAccountId(id);
            myCapitalLog.setChangeAmount(changeAmount);
           	int test=1/0;
            int insert = myCapitalLogDAO.insert(myCapitalLog);
            if (i <= 0 && insert <= 0) {
                //任何一步没有成功更新,抛出异常回滚
                throw new IllegalStateException("操作资金失败");
            }
        } finally {
            LOCK.unlock();
        }

        return true;
    }

Add manual exceptions before logging.
Add 10 yuan to Zhang San's account again

result

Insert picture description here


Insert picture description here

The account amount is deducted, but the flow of funds is not recorded.

solution

Add transaction annotations to ensure the atomicity of two table operations (here, choose the Java pessimistic locking scheme as an example)

@Transactional
public boolean coverAccount(Long id, Long changeAmount) {
        if (changeAmount == 0) {
            return true;
        }
        MyCapitalAccount accountDbOut = myCapitalDAO.getById(id);
        if (changeAmount < 0 && accountDbOut.getCurrentBalance() + changeAmount < 0) {
            System.out.println("外部判断余额不足");
            throw new RuntimeException("余额不足");
        }

        LOCK.lock();
        try {
            //double check
            MyCapitalAccount myCapitalAccountDb = myCapitalDAO.getById(id);
            if (changeAmount < 0 && myCapitalAccountDb.getCurrentBalance() + changeAmount < 0) {
                System.out.println("内部判断余额不足");
                throw new RuntimeException("余额不足");
            }
            Long nowBalance = myCapitalAccountDb.getCurrentBalance() + changeAmount;
            int i = myCapitalDAO.coverAccountBalance(id, nowBalance);
            MyCapitalLog myCapitalLog = new MyCapitalLog();
            myCapitalLog.setAccountId(id);
            myCapitalLog.setChangeAmount(changeAmount);
            int insert = myCapitalLogDAO.insert(myCapitalLog);
            if (i <= 0 && insert <= 0) {
                //任何一步没有成功更新,抛出异常回滚
                throw new IllegalStateException("操作资金失败");
            }
        } finally {
            LOCK.unlock();
        }

        return true;
    } 

Add 10 yuan to Zhang San again

The log and the amount failed at the same time (this is relatively simple and does not map)

-------------------------------I am the dividing line--------------- -----------------------------
Alright, now we have encountered 3 problems and the current solutions.

Serial numberproblemdescriptionsolution
1Over-deduction problemAccount amount cannot be negativeIncrease memory fund judgment
2Concurrency security issuesAt the same time, the last update will be overwritten when visitingIncrease distributed lock
3Atomicity problemFund balance and fund flow must meet AJoin the transaction

The demo now looks like this:

 	public static final ReentrantLock LOCK = new ReentrantLock();
	@Transactional
    public boolean coverAccount(Long id, Long changeAmount) {
        if (changeAmount == 0) {
            return true;
        }
        MyCapitalAccount accountDbOut = myCapitalDAO.getById(id);
        if (changeAmount < 0 && accountDbOut.getCurrentBalance() + changeAmount < 0) {
            System.out.println("外部判断余额不足");
            throw new RuntimeException("余额不足");
        }

        LOCK.lock();
        try {
            //double check
            MyCapitalAccount myCapitalAccountDb = myCapitalDAO.getById(id);
            if (changeAmount < 0 && myCapitalAccountDb.getCurrentBalance() + changeAmount < 0) {
                System.out.println("内部判断余额不足");
                throw new RuntimeException("余额不足");
            }
            Long nowBalance = myCapitalAccountDb.getCurrentBalance() + changeAmount;
            int i = myCapitalDAO.coverAccountBalance(id, nowBalance);
            MyCapitalLog myCapitalLog = new MyCapitalLog();
            myCapitalLog.setAccountId(id);
            myCapitalLog.setChangeAmount(changeAmount);
            int insert = myCapitalLogDAO.insert(myCapitalLog);
            if (i <= 0 && insert <= 0) {
                //任何一步没有成功更新,抛出异常回滚
                throw new IllegalStateException("操作资金失败");
            }
        } finally {
            LOCK.unlock();
        }

        return true;
    }

Data is initialized, and 100 threads are started again to run down the deduction

 @GetMapping("multi/thread/coverAccount")
    @ResponseBody
    public boolean multiThreadCoverAccount() {
        ExecutorService executor = Executors.newCachedThreadPool();
        Long id = 1L;
        Long changeAmount = -10L;
        for (int i = 0; i < 100; i++) {
            executor.submit(() -> capitalAccountService.coverAccount(id, changeAmount));
        }
        return true;
    }

result:

Insert picture description here


Execute again:

Insert picture description here


The second type of lost update occurs. After closing the transaction, test again.

Insert picture description here


No problem, now the problem is found

Question 4: After opening the transaction, the second type of lost update reappears

Recap: The previous question 2 is updated to cover. It is because of concurrent modification problems that lead to overwriting. Distributed locks have been added here, so why are there still concurrency problems?
Execute the current update code, and see the log log of

Insert picture description here


mybatis. The first level cache of mybatis takes effect, causing the latest data to be invisible (in fact, the root cause is not this , so let’s bury a thunder ). The first level cache of mybatis is at the sqlSession level, the same The second query will use the first-level cache (the specific first-level cache is Baidu). The transaction is opened here, so the sqlSession must be the same.

Scrabble

1. Try to control only one query in the transaction, and eliminate the interference of the secondary cache of sqlSession

Since it is suspected to be the problem of sqlSession,
the logic of the double check query after the transaction before the distributed lock is directly removed, the query is only one time, and in the distributed lock, the problem of the first level cache will not occur. Here it loops again 100 times, subtracting 10 yuan each time, in order to simulate concurrency, use countDownLatch to intercept all threads, and execute them at the same time after opening the transaction.

 @GetMapping("multi/thread/coverAccount")
    @ResponseBody
    public boolean multiThreadCoverAccount() {
        ExecutorService executor = Executors.newCachedThreadPool();
        Long id = 1L;
        Long changeAmount = -10L;
        int size = 100;
        CountDownLatch countDownLatch = new CountDownLatch(size);
        for (int i = 0; i < size; i++) {
            int num = i;
            executor.submit(() -> {
                countDownLatch.countDown();
                try {
                    countDownLatch.await(20, TimeUnit.SECONDS);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
                capitalAccountService.coverAccount(id, changeAmount, num);
            });
        }

        return true;
    }
  @Transactional
    public boolean coverAccount(Long id, Long changeAmount, int num) {
        if (changeAmount == 0) {
            return true;
        }
        System.out.println("编号" + num + "正在执行");
        LOCK.lock();
        try {
            MyCapitalAccount myCapitalAccountDb = myCapitalDAO.getById(id);
            if (changeAmount < 0 && myCapitalAccountDb.getCurrentBalance() + changeAmount < 0) {
                System.out.println("内部判断余额不足");
                throw new RuntimeException("余额不足");
            }
            Long nowBalance = myCapitalAccountDb.getCurrentBalance() + changeAmount;
            int i = myCapitalDAO.coverAccountBalance(id, nowBalance);
            MyCapitalLog myCapitalLog = new MyCapitalLog();
            myCapitalLog.setAccountId(id);
            myCapitalLog.setChangeAmount(changeAmount);
            int insert = myCapitalLogDAO.insert(myCapitalLog);
            if (i <= 0 && insert <= 0) {
                //任何一步没有成功更新,抛出异常回滚
                throw new IllegalStateException("操作资金失败");
            }
            System.out.println(nowBalance + "元");
        } finally {
            LOCK.unlock();
        }
        return true;
    }

result:

![Insert the picture description here](https://img-blog.csdnimg.cn/20200910173146410.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4FxNjY1FF#FF_color_center, Insert picture description here)(https://img-blog.csdnimg.cn/20201107121604205.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG3NzYFF_center_color_center_FF_70Fx,
Insert picture description here


It can be seen from the log that the current query is from the DB, but there is still a second type of missing update (-_-!!). And 1000 should be deducted, here only 500 is deducted. Analyzing the log, we can see that every time it is repeated two by two, why do I still read the same data when the lock is locked? , The problem is that the release of this lock is before the transaction takes effect! !
We all know that Spring's transaction is based on the aspect. When the aspect method is executed, it will commit the transaction. At this time, the code for finally releasing the distributed lock in the method has been executed! After the lock is released and before the transaction is committed, the lock is still not locked, causing concurrent modification problems.

Let's draw a timing table to analyze this problem

timeTransaction ATransaction B
t1Open transaction
t2Open transaction
t3Obtain a distributed lockObtain a distributed lock
t4Obtain a distributed lockDistributed lock not acquired
t5Execute query and modify dataDistributed lock blocking
t6Release the distributed lockDistributed lock blocking
t7Obtain a distributed lock
t8Execute query
t9Commit the transaction (actually effective)change the data
t10Commit the transaction (actually effective)

You can focus on t8-t9, as long as the query operation of transaction B (t8) is committed earlier than transaction A (t9), the concurrent modification is established, which leads to the second type of lost update.
For the commit transaction (really effective), this process involves remote mysql calls, and the execution rate of the local code is not an order of magnitude, so the above situation can definitely be universal.

Therefore:
Distributed locks cannot be placed in the transaction, because the release of the lock is executed before the transaction is committed. Once the data is read by other transactions in the gap, the concurrent modification conditions are met, which causes the second type of lost update

2. Distributed locks move forward to the business layer

Here for performance, pre-check the balance:
business code

 @GetMapping("multi/thread/coverAccount")
    @ResponseBody
    public boolean multiThreadCoverAccount1() {
        //模拟业务多次调用
        ExecutorService executor = Executors.newCachedThreadPool();
        Long id = 1L;
        Long changeAmount = -10L;
        int size = 100;
        CountDownLatch countDownLatch = new CountDownLatch(size);
        for (int i = 0; i < size; i++) {
            executor.submit(() -> {
                countDownLatch.countDown();
                try {
                    countDownLatch.await(20, TimeUnit.SECONDS);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
                doCoverAccount(id,changeAmount);
            });
        }

        return true;
    }
    
    private void doCoverAccount(Long id,Long changeAmount){
        //预校验
        MyCapitalAccount accountDbOut = capitalAccountService.getAccountById(id);
        if (changeAmount < 0 && accountDbOut.getCurrentBalance() + changeAmount < 0) {
            throw new RuntimeException("余额不足");
        }
        LOCK.lock();
        try {
            capitalAccountService.coverAccount(id, changeAmount);
        }finally {
            LOCK.unlock();
        }
    }
   @Transactional
    public boolean coverAccount(Long id, Long changeAmount) {
        if (changeAmount == 0) {
            return true;
        }
        MyCapitalAccount myCapitalAccountDb = myCapitalDAO.getById(id);
         if (changeAmount < 0 && myCapitalAccountDb.getCurrentBalance() + changeAmount < 0) {
            System.out.println("内部判断余额不足");
            throw new RuntimeException("余额不足");
        }
        Long nowBalance = myCapitalAccountDb.getCurrentBalance() + changeAmount;
        int i = myCapitalDAO.coverAccountBalance(id, nowBalance);
        MyCapitalLog myCapitalLog = new MyCapitalLog();
        myCapitalLog.setAccountId(id);
        myCapitalLog.setChangeAmount(changeAmount);
        int insert = myCapitalLogDAO.insert(myCapitalLog);
        if (i <= 0 && insert <= 0) {
            //任何一步没有成功更新,抛出异常回滚
            throw new IllegalStateException("操作资金失败");
        }
        return true;
    }

Set the original value to 990 here

Insert picture description here

The result of executing the above code:

Insert picture description here


Insert picture description here


Insert picture description here

Ok the problem is solved

to sum up

If the system only has such a simple single-person account operation scenario, the above methods can be used. Note that when using pessimistic lock, it must be used outside the transaction.

Scenario 2: Multi-account payroll operation

The company needs to pay its employees. When sending money, employees increase a, b, c...yuan, and the company deducts a+b+c...yuan

According to the above problems and solutions, try a version to achieve this requirement.
Demand analysis: Unlike the above scenario, the behavior of pay should be transactional, so it is necessary to add transactions at the upper level.

 @GetMapping("doTest")
    @ResponseBody
    public boolean payEmployee() {
    	//模拟业务并发执行
        ExecutorService executor = Executors.newCachedThreadPool();
        int size = 100;
        CountDownLatch countDownLatch = new CountDownLatch(size);
        for (int i = 0; i < size; i++) {
            executor.submit(() -> {
                countDownLatch.countDown();
                try {
                    countDownLatch.await(20, TimeUnit.SECONDS);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
                payService.payMoney();
            });
        }

        return true;
    }

Newly written service PayService

 public static final ReentrantLock LOCK_ZHANG_SAN = new ReentrantLock();
    public static final ReentrantLock LOCK_LI_SI = new ReentrantLock();
    public static final ReentrantLock LOCK_COMPANY = new ReentrantLock();



    @Transactional
    public void payMoney(){
        doCoverAccount(1L, 10L);
        doCoverAccount(2L, 15L);
        doCoverAccount(3L, -25L);
    }

    private void doCoverAccount(Long id,Long changeAmount){
        ReentrantLock lock = null;
        if (id==1L){
            lock = LOCK_ZHANG_SAN;
        }else if (id==2L){
            lock = LOCK_LI_SI;
        }else if (id==3L){
            lock = LOCK_COMPANY;
        }
        //预校验
        MyCapitalAccount accountDbOut = capitalAccountService.getAccountById(id);
        if (changeAmount < 0 && accountDbOut.getCurrentBalance() + changeAmount < 0) {
            throw new RuntimeException("余额不足");
        }
        lock.lock();
        try {
            capitalAccountService.coverAccount(id, changeAmount);
        }finally {
            lock.unlock();
        }
    }

There is no change in the actual execution method

    @Transactional
    public boolean coverAccount(Long id, Long changeAmount) {
        if (changeAmount == 0) {
            return true;
        }
        MyCapitalAccount myCapitalAccountDb = myCapitalDAO.getById(id);
        if (changeAmount < 0 && myCapitalAccountDb.getCurrentBalance() + changeAmount < 0) {
            System.out.println("内部判断余额不足");
            throw new RuntimeException("余额不足");
        }
        Long nowBalance = myCapitalAccountDb.getCurrentBalance() + changeAmount;
        int i = myCapitalDAO.coverAccountBalance(id, nowBalance);
        MyCapitalLog myCapitalLog = new MyCapitalLog();
        myCapitalLog.setAccountId(id);
        myCapitalLog.setChangeAmount(changeAmount);
        int insert = myCapitalLogDAO.insert(myCapitalLog);
        if (i <= 0 && insert <= 0) {
            //任何一步没有成功更新,抛出异常回滚
            throw new IllegalStateException("操作资金失败");
        }
        return true;
    }

Now initialize the data as:

Insert picture description here

Execute 100 times, should Zhang San: 1000+1000=2000 Li Siwei: 1000+1500=2500 XX company: 20000-1000-1500=17500
Execution code:

Insert picture description here

It is completely incorrect, and each execution is different, and then carefully observe the code:

Insert picture description here

This is still the lock in the transaction, this is the problem discussed before. And this problem is even more serious, because multiple pieces of data will not be submitted immediately after releasing the lock, but all of them will be submitted together after successful, which causes other transactions to read the old data before the data is submitted.

Choosing to use distributed pessimistic locks is now caught in a very complicated dilemma. Without transactions, it is impossible to guarantee that the batch of data is atomic. Adding transactions will cause the above problems. Some people will say that after the transaction is submitted, it will be unlocked again. Then we have to consider the deadlock problem. . . . It has become extremely complicated! ! ! ! Moreover, there are also issues such as invisible transactions.

Full text summary

It is strongly not recommended to use distributed locks to implement the account fund change function in the above scenarios, not to mention poor scalability and extremely high complexity.

There are mysql optimistic locks and mysql row locks to choose from

In fact, after mysql row lock + transaction, it is directly a natural distributed lock of the database layer, and optimistic lock is not needed at all. Because there is no snapshot read during the update process. At the mysql level, the data of a single account is a row. The data is locked when updated, the data is read in the snapshot, and the row lock is released after the update is completed. The entire process is serial. There will be no problem that the data read is different from the data actually updated. Perfectly solve the concurrency problem. If you need to get the latest data in the system, you can get it directly after the row is locked. At this time, snapshot read = current read, because the row is locked in your own transaction, and the data cannot be changed.

And if you use optimistic lock, the row lock will still be turned on. The natural and easy-to-use row lock is not used. Use the memory snapshot to read, and then intercept the optimistic lock, which will increase the possibility of collision.
Therefore, it is recommended to use the row lock + current read method to solve this type of problem in the system that opens the transaction .

In addition: No matter whether you use snapshot read + optimistic locking, or use row lock + current read, row locks will be turned on under the transaction. At this time, you should pay attention to the problem of database row lock deadlock for batch tasks.

Example: A transaction needs to update rows a and b, and transaction B needs to update rows b and a

A transaction is opened, a is updated (transaction is not committed), at this time A transaction holds a row lock, ready to fetch b's row lock
B transaction is opened, b is updated (transaction is not committed), at this time B transaction holds b Row lock, ready to take the row lock of a

Since the transaction is not committed, the row lock will not be released, and a database deadlock will occur at this time.

Solution: Before batch update, the data is sorted according to a uniform standard, such as using accountId to sort. Once the data flow direction is the same, there will be no constraints on each other and deadlock will occur.

If your system does not open transactions, then the distributed lock scheme and the optimistic lock scheme are completely different from the above. I won’t talk about specifics.