数据库db锁得到自增数据

实体

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;

/**
 * @Description: incr
 * @Date:   2023-05-06
 * @Version: V1.0
 */
@Data
@TableName("t_incr")
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = false)
@ApiModel(value="incr对象", description="incr")
public class Incr implements Serializable {
    private static final long serialVersionUID = 1L;

	/**key,唯一*/
    @TableId(type = IdType.ASSIGN_ID)
    private String keyInfo;

    /**增长到多少*/
    private Integer valueInfo;
}

服务


import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.support.TransactionTemplate;

/**
 * 获取自增数据
 */
@Service
@Slf4j
public class IncrementManage extends ServiceImpl<IncrementMapper, Incr> {
    @Autowired
    private IncrementMapper incrementMapper;

    @Autowired
    private TransactionTemplate transactionTemplate;

    /**
     * 获取自增数据
     *
     * @param key
     * @return
     */

    public String getNext(String key) {
        try {
            // 尝试获取是否存在
            Incr incr = incrementMapper.selectById(key);
            if (incr == null) {
                incr = new Incr();
                incr.setKeyInfo(key);
                incr.setValueInfo(1);
                incrementMapper.insert(incr);
            } else {
                incr = getIncr(key);
            }
            return String.format("%04d", incr.getValueInfo());
        } catch (DuplicateKeyException e) {
            // 出现key重复问题
            Incr incr = getIncr(key);
            return String.format("%04d", incr.getValueInfo());
        } catch (Exception e) {
            throw new RuntimeException("获取自增的数据失败，key:" + key, e);
        }
    }

    /**
     * 获取自增数据,并补全位数
     *
     * @param key
     * @return
     */
    public String getNextCompletion(String key, int num) {
        // 获取自增的数据
        String nextValue = getNext(key);
        if (nextValue == null) {
            return null;
        }
        StringBuilder incr = new StringBuilder(nextValue);
        for (int i = incr.length(); i < num; i++) {
            incr.insert(0, "0");
        }
        return incr.toString();
    }


    /**
     * 以db锁获取自增
     *
     * @param key
     * @return
     */
    private Incr getIncr(String key) {
        Incr incr = transactionTemplate.execute(status -> {
            Incr rs = incrementMapper.selectForUpdate(key);
            rs.setValueInfo(rs.getValueInfo() + 1);
            incrementMapper.updateById(rs);
            return rs;
        });
        return incr;
    }
}

sql

        SELECT * FROM t_incr WHERE key_info = #{key} FOR UPDATE

集成测试


import org.junit.jupiter.api.Assertions;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Import;

import java.util.HashSet;
import java.util.Set;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;
import java.util.stream.IntStream;

import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.test.autoconfigure.web.servlet.AutoConfigureMockMvc;
import org.springframework.boot.test.context.SpringBootTest;

@SpringBootTest(webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
@AutoConfigureMockMvc
@Slf4j
@ActiveProfiles("test")
@Import(IncrementManage.class)
public class IncrementManageIntegrationTest {


    @Autowired
    private IncrementManage incrementManage;

    @Autowired
    private IncrementMapper incrementMapper;

    @Test
    public void getNextTest() {
        String key = "testKey";
        String value = incrementManage.getNext(key);
        Assertions.assertNotNull(value);
    }

    @Test
    public void getNextCompletionTest() {
        String key = "testKey";
        int num = 6;
        String value = incrementManage.getNextCompletion(key, num);
        Assertions.assertNotNull(value);
        Assertions.assertEquals(num, value.length());
    }


    @Test
    public void concurrentGetNextTest() throws InterruptedException {
        Set<String> sets = new HashSet<>();
        ExecutorService executorService = Executors.newFixedThreadPool(10); // Use a thread pool with 10 threads
        String key = "testKey";

        // Submit 100 tasks to the executor
        IntStream.range(0, 100).forEach(i -> executorService.submit(() -> {
            String value = incrementManage.getNext(key);
            Assertions.assertNotNull(value);
            sets.add(value);

        }));

        executorService.shutdown();
        executorService.awaitTermination(1, TimeUnit.MINUTES); // Wait for all tasks to complete
        System.out.println(sets.toString());
        System.out.println(sets.size()+"");
        Assertions.assertTrue(sets.size()==100);
    }

    @Test
    public void concurrentGetNextCompletionTest() throws InterruptedException {
        Set<String> sets = new HashSet<>();

        ExecutorService executorService = Executors.newFixedThreadPool(10); // Use a thread pool with 10 threads
        String key = "testKey";
        int num = 6;

        // Submit 100 tasks to the executor
        IntStream.range(0, 100).forEach(i -> executorService.submit(() -> {
            String value = incrementManage.getNextCompletion(key, num);
            Assertions.assertNotNull(value);
            Assertions.assertEquals(num, value.length());
            sets.add(value);

        }));

        executorService.shutdown();
        executorService.awaitTermination(1, TimeUnit.MINUTES); // Wait for all tasks to complete
        System.out.println(sets.toString());
        System.out.println(sets.size()+"");
        Assertions.assertTrue(sets.size()==100);
    }
}

通过数据库的db锁来得到自增的数据

以下代码会产生死锁，最终导致并发结果不正确

原因: 1、在一个事务中，select会产生一个s锁（读锁），当获取了读锁，则第二个事物要想获取x锁，则必须等待读锁释放，但是可以获取读锁。

2、在一个事务中，获取了写锁，则其他事物既不能获取读锁，也不能获取写锁

比如

时间1：事物1：获取s锁，事物2：获取s锁

时间2：事物1：尝试获取写锁，但是事物2存s锁，等待事物1释放s锁，事物2：尝试获取写锁，但是事物1有读锁，等待事物1释放读锁，最终导致了相互之间的死锁

解决方法如上:将读提取出来，不在事物中，或者第一步就获取写锁（for update）

package com.cngzh.traceability.biz.incr.service;


import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.cngzh.traceability.biz.incr.entity.Incr;
import com.cngzh.traceability.biz.incr.mapper.IncrementMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.support.TransactionTemplate;

/**
 * 获取自增数据
 */
@Service
@Slf4j
public class IncrementManage extends ServiceImpl<IncrementMapper, Incr> {
    @Autowired
    private IncrementMapper incrementMapper;

    @Autowired
    private TransactionTemplate transactionTemplate;

    /**
     * 获取自增数据
     *
     * @param key
     * @return
     */

    public String getNext(String key) {

        String rs = transactionTemplate.execute(status -> {
        try {
            // 尝试获取是否存在
            Incr incr = incrementMapper.selectById(key);
            if (incr == null) {
                incr = new Incr();
                incr.setKeyInfo(key);
                incr.setValueInfo(1);
                incrementMapper.insert(incr);
            } else {
                incr = getIncr(key);
            }
            return String.format("%04d", incr.getValueInfo());
        } catch (DuplicateKeyException e) {
            // 出现key重复问题
            Incr incr = getIncr(key);
            return String.format("%04d", incr.getValueInfo());
        } catch (Exception e) {
            throw new RuntimeException("获取自增的数据失败，key:" + key, e);
        }

        });
        return  rs;
    }

    /**
     * 获取自增数据,并补全位数
     *
     * @param key
     * @return
     */
    public String getNextCompletion(String key, int num) {
        // 获取自增的数据
        String nextValue = getNext(key);
        if (nextValue == null) {
            return null;
        }
        StringBuilder incr = new StringBuilder(nextValue);
        for (int i = incr.length(); i < num; i++) {
            incr.insert(0, "0");
        }
        return incr.toString();
    }


    /**
     * 以db锁获取自增
     *
     * @param key
     * @return
     */
    private Incr getIncr(String key) {
            Incr rs = incrementMapper.selectForUpdate(key);
            rs.setValueInfo(rs.getValueInfo() + 1);
            incrementMapper.updateById(rs);
            return rs;
//        return incr;
    }
}

博客

数据库db锁得到自增数据