并发自定义主键生成策略

版权声明:本文为博主原创文章,未经博主允许不得转载。如需转载,请标明博文原地址. https://blog.csdn.net/tianshi_kco/article/details/52937888

本人邮箱: kco1989@qq.com
欢迎转载,转载请注明网址 http://blog.csdn.net/tianshi_kco
github: https://github.com/kco1989/kco
代码已经全部托管[github]((https://github.com/kco1989/kco/tree/master/primaryKeyBuild)有需要的同学自行下载

前言

在项目开发中,我遇到一个需求.就是要生成自定义的主键.
主键的格式为:前缀 + 当天日期 + 自增长序列号

自增长序列号: 这个序列号是每天从0或1开始自增长的

在了解完需要之后,我做了简单的分析之后就马上进入编码模式,这里我使用的是mysql数据,全部代码已经上传到github,有需要的同学自行下载

目录结构(项目管理工具用的maven)

  • src
    • main
      • java
        • com.kco.bean.SequenceNumberBean
        • com.kco.dao.SequenceNumberDao
        • com.kco.Enum.SequenceNumberEnum
        • com.kco.service.SequenceNumberService
        • com.kco.service.SequenceNumberServiceImpl
      • resources
        • META-INF/mybatis/mapper/SequenceNumberDao.xml
        • META-INF/mybatis/sql-map-config.xml
        • META-INF/spring/spring-base-jdbc.xml
        • log4j.properties
    • test
      • java
        • com.kco.TestSequenceNumberService
  • pom.xml

编码

1, 首先创建一个maven工程,配置一下jar依赖 pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.kco</groupId>
    <artifactId>mytestcode</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.3.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-io</artifactId>
            <version>1.3.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-collections4</artifactId>
            <version>4.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-dbcp2</artifactId>
            <version>2.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-pool2</artifactId>
            <version>2.3</version>
        </dependency>

        <!-- test -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.10</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-test</artifactId>
            <scope>test</scope>
            <version>4.2.3.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.12</version>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.2.7</version>
        </dependency>
        <!-- spring -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>4.2.3.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context-support</artifactId>
            <version>4.2.3.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>4.2.3.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>4.2.3.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-beans</artifactId>
            <version>4.2.3.RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-aop</artifactId>
            <version>4.2.3.RELEASE</version>
        </dependency>
        <!-- aop -->
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjrt</artifactId>
            <version>1.8.8</version>
        </dependency>
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjtools</artifactId>
            <version>1.8.8</version>
        </dependency>
        <dependency>
            <groupId>org.aspectj</groupId>
            <artifactId>aspectjweaver</artifactId>
            <version>1.8.8</version>
        </dependency>
        <!--spring mybatis 整合-->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.2.2</version>
        </dependency>
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.13</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.13</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>jcl-over-slf4j</artifactId>
            <scope>runtime</scope>
            <version>1.7.13</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <version>3.5.1</version>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                    <encoding>UTF-8</encoding>
                </configuration>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <version>2.6</version>
                <configuration>
                    <nonFilteredFileExtensions>
                        <nonFilteredFileExtension>tbl</nonFilteredFileExtension>
                    </nonFilteredFileExtensions>
                </configuration>
            </plugin>

        </plugins>
    </build>
</project>

2., 初始化数据库

CREATE TABLE PUB_SEQUENCE_NUMBER(
  prefix VARCHAR(10) NOT NULL PRIMARY KEY COMMENT '前缀(主键)',
  NAME  VARCHAR(30) NOT NULL COMMENT '描述',
  toda
  y char(8) NOT NULL COMMENT '当天日期',
  minNum INTEGER NOT NULL DEFAULT 0 COMMENT '序列号最小号码',
  currentNum INTEGER NOT NULL DEFAULT 0 COMMENT '当前序列号',
  numLength INTEGER NOT NULL DEFAULT 8 COMMENT '序列号长度'
);

3, 整合spring和mybatis,创建线程池和事务切面等 spring-base-jdbc.xml

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xmlns:aop="http://www.springframework.org/schema/aop"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
        http://www.springframework.org/schema/beans/spring-beans.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context.xsd
        http://www.springframework.org/schema/aop
        http://www.springframework.org/schema/aop/spring-aop.xsd
        http://www.springframework.org/schema/tx
        http://www.springframework.org/schema/tx/spring-tx.xsd">

    <context:annotation-config/>
    <context:component-scan base-package="com.kco" />

    <!-- 配置config的数据库 -->
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql:///test?useUnicode=yes&amp;characterEncoding=utf8&amp;allowMultiQueries=true"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        <property name="minIdle" value="10"/>
        <property name="maxIdle" value="10"/>
        <property name="maxWaitMillis" value="-1"/>
        <property name="maxTotal" value="10"/>
    </bean>
    <!-- spring和MyBatis完美整合-->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean" >
        <property name="dataSource" ref="dataSource" />
        <property name="configLocation"
                  value="classpath:META-INF/mybatis/sql-map-config.xml" />
        <property name="mapperLocations" value="classpath*:META-INF/mybatis/mapper/*Dao.xml" />
    </bean>

    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer" id="mapperScannerConfigurer">
        <property name="annotationClass" value="org.springframework.stereotype.Repository"/>
        <property name="basePackage" value="com.kco.dao"/>
    </bean>

    <bean id="transactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource" />
    </bean>

    <tx:advice id="txAdvice">
        <tx:attributes>
            <tx:method name="*"  propagation="REQUIRED" rollback-for="java.lang.Throwable"/>
        </tx:attributes>
    </tx:advice>

    <aop:config>
        <aop:advisor advice-ref="txAdvice" pointcut="execution(* *..*ServiceImpl*.*(..))"/>
    </aop:config>
</beans>

4, 配置mybatis sql-map-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<configuration>
<properties>
    <property name="dialect" value="mysql"/>
    <property name="pageSqlId" value=".*Page$"/>
</properties>
<settings>
    <setting name="cacheEnabled" value="false"/>
    <setting name="lazyLoadingEnabled" value="false"/>
</settings>
</configuration>

5, 因为要生成自定义主键一般在开发阶段就已经能确定,所以我这里使用了一个枚举类来管理所有的自定义主键 com.kco.Enum.SequenceNumberEnum

package com.kco.Enum;


import com.kco.bean.SequenceNumberBean;

/**
 * com.kco.Enum
 * Created by swlv on 2016/10/25.
 */
public enum SequenceNumberEnum {
    GD(new SequenceNumberBean("GD","工单主键生成策略", 1, 1, 8));

    private SequenceNumberBean sequenceNumberBean;
    SequenceNumberEnum(SequenceNumberBean sequenceNumberBean){
        this.sequenceNumberBean = sequenceNumberBean;
    }

    public SequenceNumberBean getSequenceNumberBean() {
        return sequenceNumberBean;
    }
}

6, 数据库PUB_SEQUENCE_NUMBER对应的bean类 com.kco.bean.SequenceNumberBean

package com.kco.bean;

/**
 * 主键生成策略的bean
 * com.kco.bean
 * Created by swlv on 2016/10/25.
 */
public class SequenceNumberBean {

    private String prefix;
    private String name;
    private String today;
    private int minNum;
    private int currentNum;
    private int numLength;

    public String getPrefix() {
        return prefix;
    }

    public void setPrefix(String prefix) {
        this.prefix = prefix;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getToday() {
        return today;
    }

    public void setToday(String today) {
        this.today = today;
    }

    public int getMinNum() {
        return minNum;
    }

    public void setMinNum(int minNum) {
        this.minNum = minNum;
    }

    public int getCurrentNum() {
        return currentNum;
    }

    public void setCurrentNum(int currentNum) {
        this.currentNum = currentNum;
    }

    public int getNumLength() {
        return numLength;
    }

    public void setNumLength(int numLength) {
        this.numLength = numLength;
    }

    public SequenceNumberBean() {
    }

    public SequenceNumberBean(String prefix, String name, int minNum, int currentNum, int numLength) {
        this.prefix = prefix;
        this.name = name;
        this.minNum = minNum;
        this.currentNum = currentNum;
        this.numLength = numLength;
    }

    @Override
    public String toString() {
        return "SequenceNumberBean{" +
                "prefix='" + prefix + '\'' +
                ", name='" + name + '\'' +
                ", today='" + today + '\'' +
                ", minNum=" + minNum +
                ", currentNum=" + currentNum +
                ", numLength=" + numLength +
                '}';
    }
}

7, 服务层的接口类以及实现 com.kco.service.SequenceNumberService

package com.kco.service;


import com.kco.Enum.SequenceNumberEnum;

/**
 * com.kco.service
 * Created by swlv on 2016/10/25.
 */
public interface SequenceNumberService {
    /**
     * 生成一个主键
     * @param sequenceNumberEnum 主键生成类型
     * @return 返回一个生成的主键
     */
    String newSequenceNumber(SequenceNumberEnum sequenceNumberEnum);

}

com.kco.service.SequenceNumberServiceImpl

package com.kco.service;

import com.kco.Enum.SequenceNumberEnum;
import com.kco.bean.SequenceNumberBean;
import com.kco.dao.SequenceNumberDao;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;

/**
 * com.kco.service
 * Created by swlv on 2016/10/25.
 */
@Service
public class SequenceNumberServiceImpl implements SequenceNumberService{
    @Resource
    private SequenceNumberDao sequenceNumberDao;
    @Override
    public synchronized String newSequenceNumber(SequenceNumberEnum sequenceNumberEnum) {
        if (sequenceNumberEnum == null){
            return null;
        }
        try {
            SequenceNumberBean sequenceNumberBean = sequenceNumberDao.newSequenceNumber(sequenceNumberEnum.getSequenceNumberBean().getPrefix());
            Thread.sleep(100);
            if (sequenceNumberBean == null){
                sequenceNumberBean = sequenceNumberEnum.getSequenceNumberBean();
                sequenceNumberBean.setToday(sequenceNumberDao.getToday());
            }
            Thread.sleep(100);
            sequenceNumberDao.updateSequenceNumber(sequenceNumberBean);
            Thread.sleep(100);
            return String.format("%s%6s%08d",
                    sequenceNumberBean.getPrefix(), sequenceNumberBean.getToday(), sequenceNumberBean.getCurrentNum());
        } catch (InterruptedException e) {
            e.printStackTrace();
            return "";
        }

    }
}

8, 数据库dao层接口 com.kco.dao.SequenceNumberDao

package com.kco.dao;

import com.kco.bean.SequenceNumberBean;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

/**
 * com.kco.dao
 * Created by swlv on 2016/10/25.
 */
@Repository
public interface SequenceNumberDao {
    /**
     * 根据前缀生成一个序列号信息
     * @param prefix 前缀
     * @return 新的序列号信息
     */
    SequenceNumberBean newSequenceNumber(@Param("prefix") String prefix);

    /**
     * 将生成的序列号信息更新到数据库中
     * @param sequenceNumberBean 需要更新信息
     */
    void updateSequenceNumber(@Param("bean") SequenceNumberBean sequenceNumberBean);

    /**
     * 获取数据库当天日期
     * @return
     */
    String getToday();
}

9., dao层mybatis的实现 SequenceNumberDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<mapper namespace="com.kco.dao.SequenceNumberDao">

    <resultMap id="sequenceNumberResultMap" type="com.kco.bean.SequenceNumberBean">
        <id property="prefix" column="prefix"/>
        <result property="name" column="name"/>
        <result property="today" column="today"/>
        <result property="minNum" column="minNum"/>
        <result property="currentNum" column="currentNum"/>
        <result property="numLength" column="numLength"/>
    </resultMap>

    <select id="newSequenceNumber" resultMap="sequenceNumberResultMap">
        SELECT prefix,name,today,minNum,currentNum + 1 as currentNum,numLength
        FROM PUB_SEQUENCE_NUMBER
        WHERE prefix = #{prefix}
        AND today = DATE_FORMAT(CURRENT_DATE(),'%Y%m%d')
    </select>

    <update id="updateSequenceNumber" parameterType="com.kco.bean.SequenceNumberBean">
        REPLACE INTO PUB_SEQUENCE_NUMBER(prefix,name,today,minNum,currentNum,numLength)
        VALUES(#{bean.prefix}, #{bean.name}, DATE_FORMAT(CURRENT_DATE(),'%Y%m%d'), #{bean.minNum}, #{bean.currentNum},#{bean.numLength})
    </update>

    <select id="getToday" resultType="string">
        SELECT DATE_FORMAT(CURRENT_DATE(),'%Y%m%d')
    </select>
</mapper>

10, 编写测试代码(模拟并发测试) com.kco.TestSequenceNumberService

package com.kco;


import com.kco.Enum.SequenceNumberEnum;
import com.kco.service.SequenceNumberService;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Queue;
import java.util.concurrent.ArrayBlockingQueue;

/**
 * com.cmbchina.base.service
 * Created by swlv on 2016/10/25.
 */

public class TestSequenceNumberService {

    public static void main(String[] args) throws InterruptedException {
        ApplicationContext applicationContext = new ClassPathXmlApplicationContext("META-INF/spring/spring-base-jdbc.xml");
        SequenceNumberService sequenceNumberService = applicationContext.getBean(SequenceNumberService.class);
        Queue<String> queue = new ArrayBlockingQueue<String>(200);
        List<Thread> list = new ArrayList<>();
        for (int i = 0;i < 200; i ++){
            list.add(new Thread(()->{
                String key = sequenceNumberService.newSequenceNumber(SequenceNumberEnum.GD);
                queue.add(key);
            }));
        }
        for (Thread thread : list){
            thread.start();
        }

        while (queue.size() != 200);
        System.out.println(queue);
    }

}

11, 至此整个项目就已经搭建完成

细节解析

com.kco.service.SequenceNumberServiceImpl#newSequenceNumber的实现中在更新当天日期字符串时,使用的是sequenceNumberBean.setToday(sequenceNumberDao.getToday());这是因为服务器的时间跟数据库的时间有可能不一致,这里以数据库的时间为准

测试

1, 查询数据库的数据
SELECT count(1) FROM PUB_SEQUENCE_NUMBER
结果:

count(1)
0

2, 清空数据后,再运行com.kco.TestSequenceNumberService#main

3, 然后查一下数据库
SELECT * FROM PUB_SEQUENCE_NUMBER

prefix NAME today minNum currentNum numLength
GD 工单主键生成策略 20161026 1 176 8

问题出现了,明明我是启动了200个线程在跑,怎么才生成176个主键,检查代码也没有问题啊,newSequenceNumber也加了关键字synchronized.

先把这个问题放一下,来回忆一下synchronized的用法,
synchronized是在线程池在访问共同变量时使其加锁,达到互斥的效果
共同变量?共同变量?咦,好像有什么不对劲的地方.newSequenceNumber使用的都是局部变量,没有共同变量啊!不对?是有共同变量,那个共同变量就是数据库的记录
除此之外使用synchronized还有一个问题,这个项目是web项目的一部分,一般在部署web项目都是有几个单边,即在不同的PC部署几个一模一样的代码,再通过集群访问的方式随机访问其中一台.那么这几个应用其实就是多进程程序,都不再一个JVM中执行,加synchronized来保证互斥,是不够的.

4, 既然知道是数据库同步出现了问题,那么怎么保证数据库同步呢?
上网搜索一下,发现数据库有悲观锁乐观锁

解决方法就是在Dao层的实现

<select id="newSequenceNumber" resultMap="sequenceNumberResultMap">
        SELECT prefix,name,today,minNum,currentNum + 1 as currentNum,numLength
        FROM PUB_SEQUENCE_NUMBER
        WHERE prefix = #{prefix}
        AND today = DATE_FORMAT(CURRENT_DATE(),'%Y%m%d')
    </select>

改为

<select id="newSequenceNumber" resultMap="sequenceNumberResultMap">
        SELECT prefix,name,today,minNum,currentNum + 1 as currentNum,numLength
        FROM PUB_SEQUENCE_NUMBER
        WHERE prefix = #{prefix}
        AND today = DATE_FORMAT(CURRENT_DATE(),'%Y%m%d') for UPDATE
    </select>

5, 然后再重新查一下数据库
SELECT * FROM PUB_SEQUENCE_NUMBER

prefix NAME today minNum currentNum numLength
GD 工单主键生成策略 20161026 1 200 8

发现问题解决了,这里是用到了数据库锁表的机制来实现同步.

以后有机会再学一下数据库同步,悲观锁乐观锁.因为这个我还不太熟悉,这里就不卖弄了.

6., 修改一下 com.kco.Enum.SequenceNumberEnum 再增加一个自定义主键GDD(new SequenceNumberBean("GDD","工单主键生成策略", 1, 1, 8))

7., 清空数据,然后运行com.kco.TestSequenceNumberService#main

8, 修改com.kco.TestSequenceNumberService2#main
String key = sequenceNumberService.newSequenceNumber(SequenceNumberEnum.GD);改为String key = sequenceNumberService.newSequenceNumber(SequenceNumberEnum.GDD);再次运行com.kco.TestSequenceNumberService#main, 这是用来模拟两个程序同时访问数据的情况

9, 查询数据
SELECT * FROM PUB_SEQUENCE_NUMBER

prefix NAME today minNum currentNum numLength
GD 工单主键生成策略 20161026 1 200 8
GDD 工单主键生成策略 20161026 1 200 8

数据正常

10., 清空数据,连续运行两次com.kco.TestSequenceNumberService2#main模拟多进程对生成同一个自定义主键

11, 查询结果
SELECT * FROM PUB_SEQUENCE_NUMBER

prefix NAME today minNum currentNum numLength
GDD 工单主键生成策略 20161026 1 400 8

至此, 全部搞定

遗留问题

  1. 数据库悲观锁乐观锁具体是怎么样的.有什么区别?
  2. for update锁表对性能有没有什么影响,除了性能,还有其他隐藏的隐患?

这几个问题就留给读者(还有我自己)自行查资料研究了


打赏

如果觉得我的文章写的还过得去的话,有钱就捧个钱场,没钱给我捧个人场(帮我点赞或推荐一下)
微信打赏支付宝打赏

展开阅读全文

没有更多推荐了,返回首页