Foreword
In the previous section, we introduced in detail the causes of the oversold problem and how to solve the oversold problem in single-application projects – controlling concurrent access through jvm local locks to solve the “oversold problem”. At the same time, we also proposed that local locks can only solve the overselling problem of a single application service. In this section, we continue from the previous part and use traditional locks to solve the concurrency problem in multi-application service access. Mainly through mysql’s optimistic locking and pessimistic locking to solve concurrency problems.
Text
- Enable the allow parallel run function of idea, and open three applications with the same service, the ports are 7000, 7001, 7002
- Use nginx to proxy the above three services to achieve concurrent access
– nginx.conf configuration file modification
– Access interface, you can access the deduction inventory interface normally, and the nginx proxy has taken effect.
- Modify the inventory quantity to 10,000 and use jmeter to test the concurrent access interface
- Using jmeter concurrent access test, result description
– jmeter test results
– Database inventory deduction results
ps: It can be concluded from the test results that the deducted inventory is 4810. When the application uses the jvm local lock, it cannot solve the concurrent “oversold problem” and the problem of concurrent access still occurs. .
- By using mysql row lock, use a sql to solve the concurrent access problem
– Modify the checkAndReduceStock method in the WmsStockServiceImpl class
– Deduct inventory through sql
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.ht.atp.plat.mapper.WmsStockMapper"> <update id="checkAndReduceStock"> update wms_stock set stock_quantity = (stock_quantity - #{reduceStock}) where id = 1 and (stock_quantity - #{reduceStock}) >= 0; </update> </mapper>
- Modify the inventory to 10,000, restart the service, and use jmeter to stress test the modified inventory deduction interface
– jmeter test results: average access time 214ms, throughput 455 per second
– Database deduction inventory result: 0
PS: The “oversold” problem of concurrent access can be solved through mysql row locks
- Use mysql’s pessimistic lock to solve the “oversold” problem of concurrent access
– Modify the checkAndReduceStock method in the WmsStockServiceImpl class
– Use for update to lock and query inventory
- Modify the inventory to 10,000 again, restart the modified service, and use jmeter to stress test the modified inventory reduction interface
– jmeter test results: average access time 528ms, throughput 185 per second
– Database inventory deduction result: 0
PS: MySQL’s pessimistic lock can solve the “oversold” problem of concurrent access. The average access time has increased and the throughput has also decreased, relative to row locks. It should be noted that the operations of querying inventory and updating inventory must be placed in the same local transaction, otherwise the pessimistic lock will fail. The pessimistic lock will only release the lock after all transactions are submitted for this operation. If they are not in the same transaction, there will still be concurrency problems if the lock is released early to update the inventory.
- Use mysql’s optimistic lock to solve the “oversold” problem of concurrent access
– Add a new field version in the database wms_stock table, and control concurrent access through the version number field
– Modify the checkAndReduceStock method in the WmsStockServiceImpl class
@Override public void checkAndReduceStock() { // Query inventory WmsStock wmsStock = baseMapper.selectWmsStockForUpdate(1L); // Verify that the inventory is greater than 0 and then deduct the inventory if (wmsStock != null & amp; & amp; wmsStock.getStockQuantity() > 0) { // Get the version number Integer version = wmsStock.getVersion(); //update version number wmsStock.setVersion(version + 1); wmsStock.setStockQuantity(wmsStock.getStockQuantity() - 1); // Before updating, first determine whether it is the version queried before. If not, try again. int update = baseMapper.update(wmsStock, new UpdateWrapper<WmsStock>().eq("id", wmsStock.getId()).eq("version", version)); if (update == 0) { checkAndReduceStock(); } } }
- Modify the inventory to 10,000 again, restart the modified service, and use jmeter to stress test the modified inventory reduction interface
– jmeter test results: average access time 1447ms, throughput 65 per second
– Database inventory deduction result: 0
PS: The “oversold” problem of concurrent access can be solved through mysql’s optimistic locking. Here we need to add a retry mechanism, otherwise there will be a problem of a large number of request execution results failing.
- Summary of test results of three types of locks
Judging from the test results: If you pursue the ultimate performance, the business scenario is simple, and there is no need to record changes in data before and after, row locks are preferred; if the write concurrency is low (multiple reads), and the contention is not very intense, row locks are preferred. Optimistic locking, if the amount of write concurrency is high, will generally conflict frequently. Choosing optimistic locking at this time will lead to uninterrupted retries of the business code; pessimistic locking is generally chosen in the system.
| Lock type | Average access time | Throughput |
|---|---|---|
| Row lock | 214ms | 455 |
| Pessimistic lock | 528ms | 185 |
| Optimistic Lock | 1447ms | 65 |