preface
When it comes to solving high concurrency problems through multi-threading, thread safety is often the first concern, followed by performance. There are many technical solutions to the inventory oversold problem, such as pessimistic locking, distributed locking, optimistic locking, queue serialization, Redis atomic operation, etc. This article uses MySQL optimistic locking to demonstrate the basic implementation.
Pre-development preparation
1. Environment parameters
- Development tool: IDEA
- Basic tools: Maven+JDK8
- Technology: SpringBoot+Mybatis
- Database: MySQL5.7
- SpringBoot version: 2.2.5.release
2. Create a database
With basic Scheme already in place, the demo is best done with the simplest data structures.
DROP TABLE IF EXISTS `goods`; CREATE TABLE 'goods' (' id' int(11) NOT NULL AUTO_INCREMENT COMMENT' increment ', 'name' varchar(30) DEFAULT NULL COMMENT '主 体 ',' stock 'int(11) DEFAULT '0' COMMENT' 主 体 ', 'version' int(11) DEFAULT '0' COMMENT ', PRIMARY KEY (' id ')) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '表'; INSERT INTO `goods` VALUES (1, 'iphone', 10, 0); INSERT INTO `goods` VALUES (2, 'huawei', 10, 0); DROP TABLE IF EXISTS `order`; CREATE TABLE 'order' (' id' int(11) AUTO_INCREMENT, 'uid' int(11) COMMENT 'id',' gid 'int(11) COMMENT' id', PRIMARY KEY (' id ') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '表';Copy the code
If you don’t have an environment, you can quickly build the MySQL environment by using the MySQL master-slave replication in Docker combat. Create database test and import related SQL to initialize Table.
3. Configure dependencies in poM files
Below is the POM.xml dependency configuration.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.1</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<scope>runtime</scope>
<optional>true</optional>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
Copy the code
4. Configuration application. Yml
As MyBatis in the demo is based on interface mapping, it is easy to configure. In application. Yml, you only need to configure mysql
spring:
datasource:
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3307/test? allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=UTC
username: root
password: root
Copy the code
5. Create related beans
package com.idcmind.ants.entity;
public class Goods {
private int id;
private String name;
private int stock;
private intversion; . Omit getters, setters, and toString methods}Copy the code
public class Order {
private int id;
private int uid;
private intgid; . Omit getters, setters, and toString methods}Copy the code
Optimistic lock solution for overselling inventory
1. Dao layer development
GoodsDao.java
@Mapper
public interface GoodsDao {
/** **@paramId Id of the product *@return* /
@Select("SELECT * FROM goods WHERE id = #{id}")
Goods getStock(@Param("id") int id);
/** * Optimistic lock scheme to reduce inventory *@paramId Id of the product *@paramVersion Version *@return* /
@Update("UPDATE goods SET stock = stock - 1, version = version + 1 WHERE id = #{id} AND stock > 0 AND version = #{version}")
int decreaseStockForVersion(@Param("id") int id, @Param("version") int version);
}
Copy the code
OrderDao.java
It is important to note here that since order is the keyword in SQL, the table name needs to be quoted back.
@Mapper
public interface OrderDao {
/** * insert order * note: the order table is a keyword that requires' order '*@param order
*/
@Insert("INSERT INTO `order` (uid, gid) VALUES (#{uid}, #{gid})")
@Options(useGeneratedKeys = true, keyProperty = "id")
int insertOrder(Order order);
}
Copy the code
2. Service layer development
GoodsService.java
@Service
public class GoodsService {
@Autowired
private GoodsDao goodsDao;
@Autowired
private OrderDao orderDao;
/** * deduct inventory *@paramGid Commodity ID *@paramUid User ID *@return SUCCESS 1 FAILURE 0
*/
@Transactional
public int sellGoods(int gid, int uid) {
// Get inventory
Goods goods = goodsDao.getStock(gid);
if (goods.getStock() > 0) {
// Optimistic lock update inventory
int update = goodsDao.decreaseStockForVersion(gid, goods.getVersion());
// Update failed, indicating that other threads have modified the data, this time inventory reduction failed, you can retry a certain number of times or return
if (update == 0) {
return 0;
}
// Inventory is deducted successfully, and order is generated
Order order = new Order();
order.setUid(uid);
order.setGid(gid);
int result = orderDao.insertOrder(order);
return result;
}
// Return on failure
return 0; }}Copy the code
Concurrent test
Here we write a unit test for concurrent testing.
@SpringBootTest
class GoodsServiceTest {
@Autowired
GoodsService goodsService;
@Test
void seckill(a) throws InterruptedException {
// The inventory is initialized to 10, where 100 concurrency is simulated through CountDownLatch and thread pool
int threadTotal = 100;
ExecutorService executorService = Executors.newCachedThreadPool();
final CountDownLatch countDownLatch = new CountDownLatch(threadTotal);
for (int i = 0; i < threadTotal ; i++) {
int uid = i;
executorService.execute(() -> {
try {
goodsService.sellGoods(1, uid);
} catch(Exception e) { e.printStackTrace(); } countDownLatch.countDown(); }); } countDownLatch.await(); executorService.shutdown(); }}Copy the code
Check the database to verify that it is oversold
The results of the chart above are in line with our expectations. Concurrent testing can also be done via Postman or Jmeter. Since it is not the focus here, I will not do the demonstration, interested partners can leave a message, I will sort out the relevant tutorial.
subsequent
This article has solved the problem of oversold inventory through database optimistic locking, but it is not the optimal solution in terms of efficiency. The demonstration of other solutions will be improved in the future. If there are mistakes and omissions in the article, but also hope you don’t hesitate to give advice.
Public number [When I meet you]