不存在就插入的业务操作的并发问题

最新推荐文章于 2024-04-11 14:48:11 发布

人可德福

最新推荐文章于 2024-04-11 14:48:11 发布

阅读量1.2k

点赞数

文章标签： JavaEE

本文链接：https://blog.csdn.net/u012092826/article/details/103971852

版权

描述
业务操作中常见的不存在就新增一条，在并发操作时发生重复插入记录的问题，尤其是在某些字段存在唯一索引的情况下，第二个操作总会引发唯一键冲突，但第一个操作能正常存入数据。

具体描述

用户偏好设置A和偏好设置B作为一条记录存在业务方数据库，定义表preference

create tabel preference{
id int(11) primark_key;
user_id varchar(20) unique;
setting_a varchar(10) comment 'YES/NO';
setting_b varchar(10) comment 'YES/NO';
}

页面上展示A B两个开关设置按钮，同时进行查询 /getSettingA /getSettingB ,切换开关时调用 /updateA /updateB 接口进行设置更新。新用户发起查询请求，表中不存在记录，在查询不到时按默认设置进行初始化记录， /getSettingA /getSettingB会同时查不到并要求同时初始化一条记录，此时会出现 duplicate_key 异常。

解决方案

非技术方案

什么提前初始化一条记录，两个接口合并一次查完整的记录。。。这些是流程上的优化以及忽略实际开发中的历史遗留及外部系统不可控，甚至牵涉“政治”原因。这里从技术角度讨论如何解决上面的问题：

技术方案

1、SQL优化

A :当前状况

B方案：insert into table_name(*,*) select #{} #{} from table_name where not exists(select * from table_name where user_id = #{})

C方案：insert into table_name (*,*) values (#{}, #{}) on duplicate key update * = #{}, * = #{}

2、java代码优化

D方案：代码进行同步

具体见代码，具体说明见代码，调试结果分析见代码，表定义见代码

WorkCard.java

@Data
@NoArgsConstructor
@AllArgsConstructor
public class WorkCard {
    private Integer id;
    private Integer empId;
    private String realName;
    private String department;
}

WorkCardMapper.java

public interface WorkCardMapper {
    int simpleInsert(WorkCard workCard);
    int insertIfNotExists(WorkCard workCard);
    int insertOnDuplicateKeyUpdate(WorkCard workCard);
    WorkCard getWorkCardByEmpId(Integer empId);
    WorkCard getByPessimisticId(Integer empId);

    @Select("select id , emp_id as empId, real_name as realName , department " +
            "from work_card where emp_id = #{empId} and department = #{department}") // for update
    WorkCard getByOptimisticEmpIdAndDepartName(@Param("empId") Integer empId, @Param("department") String department);

    @Update("update work_card set emp_id = #{workCard.empId}, real_name = #{workCard.realName},department = #{workCard.department} " +
            "where id = #{workCard.id}")
    int updateByPrimaryKey(@Param("workCard") WorkCard workCard);
    @Update("update work_card set real_name = concat(real_name, ',', #{workCard.realName}),department = #{workCard.department} where id = #{workCard.id}")
    int appendRealNameAndUpdateDepartById(@Param("workCard") WorkCard workCard);
}

WorkCardMapper.xml

<mapper namespace="com.mybatis.chapter05.mapper.WorkCardMapper">
  <insert id="simpleInsert" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    insert into work_card
    (emp_id, real_name, department)
    values
    (#{empId},#{realName},#{department})
  </insert>
  <insert id="insertIfNotExists" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    insert into work_card
    (emp_id, real_name, department)
    select
    #{empId},#{realName},#{department}
    from work_card
    where not exists(select * from work_card where emp_id = #{empId})
  </insert>
  <insert id="insertOnDuplicateKeyUpdate" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
    insert into work_card
    (emp_id, real_name, department)
    values
    (#{empId},#{realName},#{department})
    on duplicate key update
    real_name = #{realName},department = #{department};
  </insert>
  <select id = "getWorkCardByEmpId" parameterType="int" resultType="workCard">
    select id , emp_id as empId, real_name as realName , department from work_card
    where emp_id = #{empId}
  </select>
  <select id="getByPessimisticId" resultType="com.mybatis.chapter05.model.WorkCard">
    select id , emp_id as empId, real_name as realName , department from work_card
    where emp_id = #{empId} for update
  </select>
</mapper>

SqlSessionFactoryUtils.java

//加锁保证SqlSessionFactory创建的唯一性
public class SqlSessionFactoryUtils {
    private final static Class<SqlSessionFactoryUtils> LOCK = SqlSessionFactoryUtils.class;
    private static SqlSessionFactory sqlSessionFactory = null;
    private SqlSessionFactoryUtils(){}

    //使用property stream生成SqlSessionFactory
    public static SqlSessionFactory getSqlSessionFactory(String configFileRPath){
        synchronized (LOCK){
            if (sqlSessionFactory != null){
                return sqlSessionFactory;
            }
            //String configFile = "mybatis-config.xml";
            InputStream inputStream;
            try {
                inputStream = Resources.getResourceAsStream(configFileRPath);
                SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
                sqlSessionFactory = sqlSessionFactoryBuilder.build(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
                return null;
            }
            return sqlSessionFactory;
        }
    }

    //使用代码方式生成SqlSessionFactory
    public static SqlSessionFactory getSqlSessionFactory2(String jdbcFileRPath, boolean autoCommit){
        synchronized (LOCK){
            Properties props = null;
            try {
                props = Resources.getResourceAsProperties(jdbcFileRPath);
            } catch (IOException e) {
                e.printStackTrace();
                return null;
            }
            PooledDataSource dataSource = new PooledDataSource();
            dataSource.setDriver("com.mysql.jdbc.Driver");
            dataSource.setUsername(props.getProperty("db.username"));
            dataSource.setPassword(props.getProperty("db.password"));
            dataSource.setUrl(props.getProperty("db.url"));
            dataSource.setDefaultAutoCommit(autoCommit);//AutoC1
            dataSource.setDefaultTransactionIsolationLevel(TransactionIsolationLevel.READ_UNCOMMITTED.getLevel());

            TransactionFactory transacFact = new JdbcTransactionFactory();
            //Environment实例对象也可以使用内置的建造器进行构建
            //Environment.Builder builder = new Environment.Builder("dev").transactionFactory(transacFact).dataSource(dataSource);
            //Environment environment = builder.build();
            Environment env = new Environment("dev", transacFact, dataSource);
            Configuration config = new Configuration(env);

            //单个注册和批量注册 别名+Mapper
            //config.getTypeAliasRegistry().registerAlias("role",Role.class);
            //config.addMapper(RoleMapper.class);
            config.getTypeAliasRegistry().registerAliases("com.mybatis.chapter05.model");
            config.addMappers("com.mybatis.chapter05.mapper");

            SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
            sqlSessionFactory = builder.build(config);
            return sqlSessionFactory;
        }
    }

    public static SqlSession openSqlSession(String configFileRPath){
        if (sqlSessionFactory == null){
            getSqlSessionFactory(configFileRPath);
        }
        return sqlSessionFactory.openSession();
    }

    public static <T> T getMapper2(Class<T> clazz, String jdbcFileRPath, boolean autoCommit){
        SqlSessionFactory sqlSessFact = getSqlSessionFactory2(jdbcFileRPath,autoCommit);
        //sqlSessFact.getConfiguration().getEnvironment().getDataSource().getConnection().setAutoCommit(true);//AutoC2
        //在AutoC1处设置的事务隔离级别在openSession()后是有效，但不论在AutoC1还是在AutoC2设置autoCommit都不能在openSession()后带过来，还要再设置一遍
        SqlSession sqlSession = sqlSessFact.openSession(true);
        Connection sqlSessionConnection = sqlSession.getConnection();
        try {
            //1 TRANSACTION_READ_UNCOMMITTED; 2 TRANSACTION_READ_COMMITTED;
            //4 TRANSACTION_REPEATABLE_READ; 8 TRANSACTION_SERIALIZABLE;
            int transactionIsolation = sqlSessionConnection.getTransactionIsolation();
            boolean sqlSessionAutoCommit = sqlSessionConnection.getAutoCommit();
            System.out.println("事务隔离级别："+transactionIsolation+"，是否自动提交："+sqlSessionAutoCommit);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        T mapper = sqlSession.getMapper(clazz);
        return mapper;
    }

    public static <T> T getMapper(Class<T> clazz, String configFileRPath){
        SqlSessionFactory sessFact = getSqlSessionFactory(configFileRPath);
        //SqlSession sqlSession = sessFact.openSession();
        //这里可以调整autoCommit或事务隔离级别，但限制较多，建议在DataSource中设置好
        SqlSession sqlSession = sessFact.openSession(true);
        //SqlSession sqlSession1 = sessFact.openSession(TransactionIsolationLevel.READ_COMMITTED);
        T t = sqlSession.getMapper(clazz);
        //sqlSession.close();
        return t;
    }

}

Chapter05Test.java

public class Chapter5{
    /** -=-=-=-=-=- 专题研究 业务操作中常见的不存在就新增一条，在并发操作时发生重复插入记录的问题，如何从技术上解决？ =-=-=-=-= **/
    /**
     * create table work_card (
     *   id         int auto_increment primary key,
     *   emp_id     int         null,
     *   real_name  varchar(20) null,
     *   department varchar(20) null,
     *   constraint work_card_emp_id_uindex
     *   unique (emp_id)
     * );
     */

    static class InsertBeforeQueryTask implements Runnable{
        private String configFileRPath;
        private String jdbcFileRPath;
        private Integer empId;

        public InsertBeforeQueryTask(String configFileRPath, String jdbcFileRPath, Integer empId) {
            this.configFileRPath = configFileRPath;
            this.jdbcFileRPath = jdbcFileRPath;
            this.empId = empId;
        }

        @Override
        public void run() {
            //WorkCardMapper workCardMapper = SqlSessionFactoryUtils.getMapper2(WorkCardMapper.class,jdbcFileRPath);
            WorkCardMapper workCardMapper = SqlSessionFactoryUtils.getMapper(WorkCardMapper.class,configFileRPath);
            //查询是否存在
            WorkCard workCard = workCardMapper.getWorkCardByEmpId(empId);
            if (workCard == null){
                workCard = new WorkCard();
                workCard.setEmpId(empId);
                workCard.setRealName("lucy");
                workCard.setDepartment("tianqin");
                //不存在就初始化
                String currentThreadName = Thread.currentThread().getName();
                System.out.println(currentThreadName+" 开始执行初始化操作");
                try {
                    //workCardMapper.simpleInsert(workCard); //A方案
                    workCardMapper.insertIfNotExists(workCard);//B方案
                    //workCardMapper.insertOnDuplicateKeyUpdate(workCard);//C方案
                }catch (Exception e){
                    System.out.println(currentThreadName + " 操作发生异常："+ e.getMessage());
                }
                System.out.println(workCard.getId());
            }
        }
        //怀疑B方案是不是使用姿势不对,此处在没数据时仍然会抛出死锁异常，调整事务隔离级别也没有效果，不建议使用
        static class InsertDeliberatelyTask implements Runnable{
            private String configFileRPath;
            private String jdbcFileRPath;
            private Integer empId;

            public InsertDeliberatelyTask(String configFileRPath, String jdbcFileRPath, Integer empId) {
                this.configFileRPath = configFileRPath;
                this.jdbcFileRPath = jdbcFileRPath;
                this.empId = empId;
            }

            @Override
            public void run() {
                //WorkCardMapper workCardMapper = SqlSessionFactoryUtils.getMapper2(WorkCardMapper.class,jdbcFileRPath);
                WorkCardMapper workCardMapper = SqlSessionFactoryUtils.getMapper(WorkCardMapper.class,configFileRPath);
                WorkCard workCard = new WorkCard();
                workCard.setEmpId(empId);
                workCard.setRealName("lucy");
                workCard.setDepartment("dust9");
                try {
                    workCardMapper.insertIfNotExists(workCard);
                }catch (Exception e){
                    System.out.println(Thread.currentThread().getName()+" 插入操作发生异常："+e.getMessage());
                }
            }
        }

        /**
         * C方案 使用悲观锁，很遗憾，悲观所只对更新操作有效果，连查都查不到没法落锁，不适合查不到就插入一条的场景
         */
        static class PessimisticLockTask implements Runnable{
            private String configFileRPath;
            private String jdbcFileRPath;
            private Integer empId;

            public PessimisticLockTask(String configFileRPath, String jdbcFileRPath, Integer empId) {
                this.configFileRPath = configFileRPath;
                this.jdbcFileRPath = jdbcFileRPath;
                this.empId = empId;
            }
            @Override
            public void run() {
                SqlSessionFactory sqlSessionFact = SqlSessionFactoryUtils.getSqlSessionFactory2(jdbcFileRPath, false);
                try {
                    SqlSession sqlSession = sqlSessionFact.openSession();
                    WorkCardMapper workCardMapper = sqlSession.getMapper(WorkCardMapper.class);
                    //insertIfNotExist(workCardMapper);
                    selectAndUpdateStatusCode(workCardMapper);
                    sqlSession.commit();
                }catch (Exception e){
                    System.out.println(Thread.currentThread().getName()+" 操作发生异常："+e.getMessage());
                }
            }
            //悲观锁对不存在就插入的情况无解
            private void insertIfNotExist(WorkCardMapper workCardMapper){
                WorkCard workCard = workCardMapper.getByPessimisticId(empId);
                if (workCard == null){
                    workCard = new WorkCard();
                    workCard.setEmpId(empId);
                    workCard.setRealName("lucy");
                    workCard.setDepartment("dust9");
                }
                int i = workCardMapper.simpleInsert(workCard);
                System.out.println(Thread.currentThread().getName()+" 操作完成准备提交："+i+", 返回主键 = "+workCard.getId());
            }
            //有两个人事同时操作了员工的work_card,emp_id是稀有关联资源，需要在一条记录里记录使用此emp_id的历史人员姓名，但department只需要记录当前人员的部门名称
            //推而广之，依赖status_code进行业务判断和操作的也必然存在并发问题
            private void selectAndUpdateStatusCode(WorkCardMapper workCardMapper){
                Long start = System.currentTimeMillis();
                WorkCard workCard = workCardMapper.getByOptimisticEmpIdAndDepartName(12,"tianqin");
                System.out.println(Thread.currentThread().getName() + " 查询耗时："+ (System.currentTimeMillis() - start));
                if (workCard != null){
                    //确认部门信息,进行一些操作
                    try {
                        Thread.sleep(2000);
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                    workCard.setRealName("daniel");
                    workCard.setDepartment("tianyu");
                    workCardMapper.appendRealNameAndUpdateDepartById(workCard);
                }
            }
            //两个人事操作不同业务更新了同一条数据,时机难以掌握，但通过不同软件连接一个数据库可以模拟
        }
        //乐观锁使用version标记，这里研究的场景跟它扯不上关系。。。
        /**
         * D方案 java代码层面使用同步
         */
        static class CommonRecordLock implements Runnable{
            private String configFileRPath;
            private String jdbcFileRPath;
            private WorkCard workCard;

            public CommonRecordLock(String configFileRPath, String jdbcFileRPath, WorkCard workCard) {
                this.configFileRPath = configFileRPath;
                this.jdbcFileRPath = jdbcFileRPath;
                this.workCard = workCard;
            }

            private final Map<Integer,WorkCard> records = new ConcurrentHashMap();
            public synchronized void lock(WorkCard workCard){
                String threadName = Thread.currentThread().getName();
                while (records.containsKey(workCard.getEmpId())){
                    System.out.println(threadName+" waiting!");
                    try {
                        wait();
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                }
                System.out.println(threadName+" putting!");
                records.put(workCard.getEmpId(), workCard);
            }
            public synchronized void release(WorkCard workCard){
                System.out.println(Thread.currentThread().getName()+" notifying!");
                records.remove(workCard.getEmpId());
                notify();
            }

            @Override
            public void run() {
                String threadName = Thread.currentThread().getName();
                lock(this.workCard);
                try {
                    WorkCardMapper mapper = SqlSessionFactoryUtils.getMapper(WorkCardMapper.class, configFileRPath);
                    WorkCard workCard = mapper.getWorkCardByEmpId(this.workCard.getEmpId());
                    Thread.sleep(1000);//业务操作耗时
                    if (workCard != null){
                        this.workCard.setId(workCard.getId());
                        mapper.updateByPrimaryKey(this.workCard);
                        System.out.println(threadName+" updating!");
                    }else {
                        mapper.simpleInsert(this.workCard);
                        System.out.println(threadName+" inserting!");
                    }
                }catch (Exception e){
                    System.out.println(threadName + " 发生异常："+e.getMessage());
                }finally {
                    release(this.workCard);
                }
            }
        }
        //方案DD 简化版的同步锁控制
        static class SimpleBizOperationLock implements Runnable{
            private String configFileRPath;
            private String jdbcFileRPath;
            private WorkCard workCard;

            public SimpleBizOperationLock(String configFileRPath, String jdbcFileRPath, WorkCard workCard) {
                this.configFileRPath = configFileRPath;
                this.jdbcFileRPath = jdbcFileRPath;
                this.workCard = workCard;
            }
            public synchronized void selectInsertOrUpdate(){
                String threadName = Thread.currentThread().getName();
                WorkCardMapper mapper = SqlSessionFactoryUtils.getMapper(WorkCardMapper.class, configFileRPath);
                WorkCard workCard = mapper.getWorkCardByEmpId(this.workCard.getEmpId());
                if (workCard == null){
                    mapper.simpleInsert(this.workCard);
                    System.out.println(threadName + " inserting!");
                }else {
                    this.workCard.setId(workCard.getId());
                    mapper.updateByPrimaryKey(this.workCard);
                    System.out.println(threadName+ " updating!");
                }
            }
            @Override
            public void run() {
                selectInsertOrUpdate();
            }
        }
        public static void main(String[] args) {
            String configFileRPath = "com/mybatis/chapter05/mapper/mybatis-config.xml";
            String jdbcFileRPath = "jdbc.properties";
            Integer empId = 13;
            WorkCard workCard = new WorkCard(null,15,"alice","tianqin");

            InsertBeforeQueryTask oneQueryTask = new InsertBeforeQueryTask(configFileRPath,jdbcFileRPath,empId);
            InsertDeliberatelyTask deliberateTask = new InsertDeliberatelyTask(configFileRPath,jdbcFileRPath,empId);
            PessimisticLockTask pessimisticLockTask = new PessimisticLockTask(configFileRPath,jdbcFileRPath,empId);
            CommonRecordLock commonRecordLock = new CommonRecordLock(configFileRPath,jdbcFileRPath,workCard);
            SimpleBizOperationLock simpleBizOperationLock = new SimpleBizOperationLock(configFileRPath,jdbcFileRPath,workCard);

            Thread thread1 = new Thread(simpleBizOperationLock);thread1.setName("A线程");
            Thread thread2 = new Thread(simpleBizOperationLock);thread2.setName("B线程");
            thread1.start();
            thread2.start();
        }
        /**
         * 演示各种insert方式在并发场景下必然发生唯一索引冲突时的健壮性
         * A方式 无数据时会有一个线程抛出异常（是A是B不确定），正常插入一条数据。再次执行就是两个异常；
         * B方式 能够插入数据，使用getMapper方法：Deadlock found when trying to get lock; try restarting transaction，也有不发生死锁的情况；使用getMapper2方法：始终报唯一索引冲突
         * C方案 正常操作，只有一个线程能正常插入数据，且其他线程调用插入方法也不会抛出异常，当据说这种SQL在某些版本存在死锁的BUG
         * D方案 java代码层面的同步加锁完美解决：不存在就插入，存在就更新。日志情况：
         *  A线程 putting!
         *  B线程 waiting!
         *  A线程 inserting!
         *  A线程 notifying!
         *  B线程 putting!
         *  B线程 updating!
         *  B线程 notifying!
         * 整个过程没有异常抛出，数据正确插入。
         * DD方案效果一致
         */
}

不需要spring依赖，使用了mybatis，直接走main方法