Mysql-based Sequence implementation method and mysqlsequence implementation

Mysql-based Sequence implementation method and mysqlsequence implementation

The team changes the new framework. All new businesses use the new framework, or even the new database-Mysql.

Oracle has been used for the past. The serial numbers provided by sequence of oracle are used directly for various order numbers, serial numbers, and batch numbers. Now that the database is changed to Mysql, the old method is obviously not applicable.

You need to write a new one:

• Distributed scenarios

• Meeting certain concurrency requirements

I found some related information and found that the implementation of mysql is a database record and its value is constantly updated. Then most of the implementation schemes use functions.

Paste the online code:

Implementation Based on mysql Functions

Table Structure

Create table 't_ sequence '('sequence _ name' varchar (64) character set utf8 COLLATE utf8_general_ci not null comment 'sequence name', 'value' int (11) null default null comment 'current value', primary key ('sequence _ name') ENGINE = InnoDBDEFAULT character set = utf8 COLLATE = utf8_general_ciROW_FORMAT = COMPACT;

Get next value

CREATE DEFINER = `root`@`localhost` FUNCTION `nextval`(sequence_name varchar(64)) RETURNS int(11)BEGIN declare current integer; set current = 0;  update t_sequence t set t.value = t.value + 1 where t.sequence_name = sequence_name; select t.value into current from t_sequence t where t.sequence_name = sequence_name; return current;end;

Concurrency scenarios may cause problems. Although locks can be applied at the business layer, distributed Scenarios cannot be guaranteed, and the efficiency should not be high.

Implement one by yourself, java version

Principle:

• Read a record and cache a data segment, for example, 0-100. Change the current value of the record from 0 to 100.

• Optimistic database lock update, allowing retries

• Read data from the cache and read the database after use

No nonsense. Go to the Code:

Java-based implementation

Table Structure

Every update, SEQ_VALUE is set to SEQ_VALUE + STEP.

Create table 't_ pub_sequence '('seq _ name' varchar (128) character set utf8 not null comment 'sequence name', 'seq _ value' bigint (20) not null comment 'current sequence value', 'min _ value' bigint (20) not null comment 'minimal', 'max _ value' bigint (20) not null comment 'maxim', 'step' bigint (20) not null comment' number of values each time ', 'Tm _ create' datetime not null comment' creation time ', 'Tm _ SMP 'datetime not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT 'modification time', primary key ('seq _ name ')) ENGINE = InnoDB default charset = utf8mb4 COMMENT = 'generate a sequential number table ';

Sequence Interface

/*** <P> </p> * @ author coderzl * @ Title MysqlSequence * @ Description Sequence Based on mysql database * @ date sequence /6/6 */public interface MysqlSequence {/ * ** <p> * obtain the serial number of the specified sequence * </p> * @ param seqName sequence name * @ return String serial number */public String nextVal (String seqName );}

Sequence Interval

Used to cache a sequence locally, from min to max

/*** <P> </p> ** @ author coderzl * @ Title SequenceRange * @ Description sequence interval, used for cache sequence * @ date sequence /6/6 */@ Datapublic class SequenceRange {private final long min; private final long max;/***/private final AtomicLong value; /** exceeded limit */private volatile boolean over = false;/*** structure. ** @ param min * @ param max */public SequenceRange (long min, long max) {this. min = min; this. max = max; this. value = new AtomicLong (min);}/*** <p> Gets and increment </p> ** @ return */public long getAndIncrement () {long currentValue = value. getAndIncrement (); if (currentValue> max) {over = true; return-1;} return currentValue ;}}

BO

Corresponding database records

@ Datapublic class MysqlSequenceBo {/*** seq name */private String seqName;/*** current value */private Long seqValue;/*** minimum value */private Long minValue; /*** maximum value */private Long maxValue;/*** number of values each time */private Long step;/***/private Date tmCreate; /***/private Date tmSmp; public boolean validate () {// some simple verifications. For example, the current value must be between the maximum and minimum values. The step value cannot be greater than the difference between max and min if (StringUtil. isBlank (seqName) | minValue <0 | maxValue <= 0 | step <= 0 | minValue> = maxValue | maxValue-minValue <= step | seqValue <minValue | seqValue> maxValue) {return false;} return true ;}}

DAO

Addition, deletion, modification, and query are actually used.

public interface MysqlSequenceDAO { /** *  */ public int createSequence(MysqlSequenceBo bo); public int updSequence(@Param("seqName") String seqName, @Param("oldValue") long oldValue ,@Param("newValue") long newValue); public int delSequence(@Param("seqName") String seqName); public MysqlSequenceBo getSequence(@Param("seqName") String seqName); public List<MysqlSequenceBo> getAll();}

Mapper

<?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.xxxxx.core.sequence.impl.dao.MysqlSequenceDAO" > <resultMap id="BaseResultMap" type="com.xxxxx.core.sequence.impl.MysqlSequenceBo" >  <result column="SEQ_NAME" property="seqName" jdbcType="VARCHAR" />  <result column="SEQ_VALUE" property="seqValue" jdbcType="BIGINT" />  <result column="MIN_VALUE" property="minValue" jdbcType="BIGINT" />  <result column="MAX_VALUE" property="maxValue" jdbcType="BIGINT" />  <result column="STEP" property="step" jdbcType="BIGINT" />  <result column="TM_CREATE" property="tmCreate" jdbcType="TIMESTAMP" />  <result column="TM_SMP" property="tmSmp" jdbcType="TIMESTAMP" /> </resultMap> <delete id="delSequence" parameterType="java.lang.String" >  delete from t_pub_sequence  where SEQ_NAME = #{seqName,jdbcType=VARCHAR} </delete> <insert id="createSequence" parameterType="com.xxxxx.core.sequence.impl.MysqlSequenceBo" >  insert into t_pub_sequence (SEQ_NAME,SEQ_VALUE,MIN_VALUE,MAX_VALUE,STEP,TM_CREATE)  values (#{seqName,jdbcType=VARCHAR}, #{seqValue,jdbcType=BIGINT},  #{minValue,jdbcType=BIGINT}, #{maxValue,jdbcType=BIGINT}, #{step,jdbcType=BIGINT},  now()) </insert> <update id="updSequence" parameterType="com.xxxxx.core.sequence.impl.MysqlSequenceBo" >  update t_pub_sequence  set SEQ_VALUE = #{newValue,jdbcType=BIGINT}  where SEQ_NAME = #{seqName,jdbcType=VARCHAR} and SEQ_VALUE = #{oldValue,jdbcType=BIGINT} </update> <select id="getAll" resultMap="BaseResultMap" >  select SEQ_NAME, SEQ_VALUE, MIN_VALUE, MAX_VALUE, STEP  from t_pub_sequence </select> <select id="getSequence" resultMap="BaseResultMap" >  select SEQ_NAME, SEQ_VALUE, MIN_VALUE, MAX_VALUE, STEP  from t_pub_sequence  where SEQ_NAME = #{seqName,jdbcType=VARCHAR} </select></mapper>

Interface implementation

@ Repository ("mysqlSequence") public class MysqlSequenceImpl implements MysqlSequence {@ Autowired private MysqlSequenceFactory mysqlSequenceFactory; /*** <p> * Get the serial number of the specified sequence * </p> ** @ param seqName sequence name * @ return String serial number * @ author coderzl */@ Override public String nextVal (String seqName) {return Objects. toString (mysqlSequenceFactory. getNextVal (seqName ));}}

Factory

The factory only does two things.

• When the service is started, Initialize all sequence in the database [complete the sequential interval cache]

• Obtain the next value of sequence

@ Componentpublic class MysqlSequenceFactory {private final Lock lock = new ReentrantLock ();/***/private Map <String, MysqlSequenceHolder> holderMap = new ConcurrentHashMap <> (); @ Autowired private MysqlSequenceDAO msqlSequenceDAO;/** Number of Retries for updating an optimistic lock upon initialization of a single sequence */@ Value ("$ {seq. init. retry: 5} ") private int initRetryNum;/** Number of Retries of an optimistic lock update failure in a single sequence update sequence */@ Value (" $ {seq. get. retry: 20} ") private int getR EtryNum; @ PostConstruct private void init () {// Initialize all sequence initAll ();}/*** <p> load all sequence in the table, complete initialization </p> * @ return void * @ author coderzl */private void initAll () {try {lock. lock (); List <MysqlSequenceBo> boList = msqlSequenceDAO. getAll (); if (boList = null) {throw new IllegalArgumentException ("The sequenceRecord is null! ") ;}For (MysqlSequenceBo: boList) {MysqlSequenceHolder holder = new MysqlSequenceHolder (msqlSequenceDAO, bo, initRetryNum, getRetryNum); holder. init (); holderMap. put (bo. getSeqName (), holder) ;}} finally {lock. unlock () ;}/ ***** <p> </p> * @ param seqName * @ return long * @ author coderzl */public long getNextVal (String seqName) {MysqlSequenceHolder holder = holderMap. get (seqName); if (holder = Null) {try {lock. lock (); holder = holderMap. get (seqName); if (holder! = Null) {return holder. getNextVal ();} MysqlSequenceBo bo = msqlSequenceDAO. getSequence (seqName); holder = new MysqlSequenceHolder (msqlSequenceDAO, bo, initRetryNum, getRetryNum); holder. init (); holderMap. put (seqName, holder);} finally {lock. unlock () ;}} return holder. getNextVal ();}}

Single sequence Holder

• Init () initialization includes parameter verification, database record update, and sequence interval Creation

• GetNextVal () gets the next value

Public class sequence {private final Lock lock = new ReentrantLock ();/** seqName */private String seqName;/** sequenceDao */private MysqlSequenceDAO sequenceDAO; private MysqlSequenceBo sequenceBo; /***/private SequenceRange sequenceRange;/** whether to initialize */private volatile boolean isInitialize = false;/*** Number of sequence initialization retries */private int initRetryNum; /** sequence get the number of retries */private int GetRetryNum;/*** <p> constructor </p> * @ Title MysqlSequenceHolder * @ param sequenceDAO * @ param sequenceBo * @ param initRetryNum, number of Retries after database update failure * @ param getRetryNum get nextVal, number of retries after database update failure * @ return * @ author coderzl */public retry (MysqlSequenceDAO sequenceDAO, MysqlSequenceBo sequenceBo, int initRetryNum, int getRetryNum) {this. sequenceDAO = sequenceDAO; this. sequenceBo = sequenceBo; This. initRetryNum = initRetryNum; this. getRetryNum = getRetryNum; if (sequenceBo! = Null) this. seqName = sequenceBo. getSeqName ();}/*** <p> initialization </p> * @ Title init * @ param * @ return void * @ author coderzl */public void init () {if (isInitialize = true) {throw new SequenceException ("[" + seqName + "] the MysqlSequenceHolder has inited");} if (sequenceDAO = null) {throw new SequenceException ("[" + seqName + "] the sequenceDao is null");} if (seqName = null | seqName. trim (). Length () = 0) {throw new SequenceException ("[" + seqName + "] the sequenceName is null");} if (sequenceBo = null) {throw new SequenceException ("[" + seqName + "] the sequenceBo is null");} if (! SequenceBo. validate () {throw new SequenceException ("[" + seqName + "] the sequenceBo validate fail. BO: "+ sequenceBo);} // initialize the sequence try {initSequenceRecord (sequenceBo);} catch (SequenceException e) {throw e;} isInitialize = true ;} /*** <p> get the next serial number </p> * @ Title getNextVal * @ param * @ return long * @ author coderzl */public long getNextVal () {if (isInitialize = false) {throw new Sequence Exception ("[" + seqName + "] the MysqlSequenceHolder not inited");} if (sequenceRange = null) {throw new SequenceException ("[" + seqName + "] the sequenceRange is null");} long curValue = sequenceRange. getAndIncrement (); if (curValue =-1) {try {lock. lock (); curValue = sequenceRange. getAndIncrement (); if (curValue! =-1) {return curValue;} sequenceRange = retryRange (); curValue = sequenceRange. getAndIncrement ();} finally {lock. unlock () ;}} return curValue ;} /*** <p> initialize the current record </p> * @ Title initSequenceRecord * @ Description * @ param sequenceBo * @ return void * @ author coderzl */private void initSequenceRecord (mysqlSequenceBo sequenceBo) {// within the specified number of times, the optimistic lock updates the database records for (int I = 1; I <initRetryNum; I ++) {// query bo M YsqlSequenceBo curBo = sequenceDAO. getSequence (sequenceBo. getSeqName (); if (curBo = null) {throw new SequenceException ("[" + seqName + "] the current sequenceBo is null");} if (! CurBo. validate () {throw new SequenceException ("[" + seqName + "] the current sequenceBo validate fail");} // change the current value long newValue = curBo. getSeqValue () + curBo. getStep (); // check the current value if (! CheckCurrentValue (newValue, curBo) {newValue = resetCurrentValue (curBo);} int result = sequenceDAO. updSequence (sequenceBo. getSeqName (), curBo. getSeqValue (), newValue); if (result> 0) {sequenceRange = new SequenceRange (curBo. getSeqValue (), newValue-1); curBo. setSeqValue (newValue); this. sequenceBo = curBo; return;} else {continue ;}// if the update fails within a limited number of times, throw the exception throw new SequenceException ("[" + seqName + "] s EquenceBo update error ");} /*** <p> check whether the new value is valid and whether the new value is between the maximum and minimum values </p> * @ param curValue * @ param curBo * @ return boolean * @ author coderzl */private boolean checkCurrentValue (long curValue, mysqlSequenceBo curBo) {if (curValue> curBo. getMinValue () & curValue <= curBo. getMaxValue () {return true;} return false;}/*** <p> Reset the current sequence Value: when the current sequence reaches the maximum value, start from the minimum value again </p> * @ Title resetCurrentValue * @ p Aram curBo * @ return long * @ author coderzl */private long resetCurrentValue (MysqlSequenceBo curBo) {return curBo. getMinValue ();}/*** <p> when the cache interval is used up, read the database records again, cache new sequence segment </p> * @ Title retryRange * @ param SequenceRange * @ author coderzl */private SequenceRange retryRange () {for (int I = 1; I <getRetryNum; I ++) {// query bo MysqlSequenceBo curBo = sequenceDAO. getSequence (sequenceBo. getSeqName (); if (curB O = null) {throw new SequenceException ("[" + seqName + "] the current sequenceBo is null");} if (! CurBo. validate () {throw new SequenceException ("[" + seqName + "] the current sequenceBo validate fail");} // change the current value long newValue = curBo. getSeqValue () + curBo. getStep (); // check the current value if (! CheckCurrentValue (newValue, curBo) {newValue = resetCurrentValue (curBo);} int result = sequenceDAO. updSequence (sequenceBo. getSeqName (), curBo. getSeqValue (), newValue); if (result> 0) {sequenceRange = new SequenceRange (curBo. getSeqValue (), newValue-1); curBo. setSeqValue (newValue); this. sequenceBo = curBo; return sequenceRange;} else {continue;} throw new SequenceException ("[" + seqName + "] sequenceBo update error ");}}

Summary

• When the service is restarted or abnormal, the cache and unused sequence of the current service will be lost.

• In distributed scenarios, when multiple services are initialized at the same time, or the sequence is re-acquired, optimistic locks do not conflict with each other. Service A gets 0-99, service B gets 100-199, and so on.

• When this sequence is obtained frequently, increasing the step value can improve performance. However, when a service exception occurs, there are also many loss sequences.

• Modify the attribute values of sequence in the database, such as step and max. The new parameter is Enabled Next time you obtain the sequence from the database.

• Sequence only provides a limited number of serial numbers (max-min at most). After reaching max, the sequence starts from the beginning.

• As sequence loops, retrieving after reaching max won't be unique. We recommend that you use sequence to splice the time when you use it to create a business flow number. Example: 20170612235101 + serial number

Business id concatenation Method

@ Servicepublic class JrnGeneratorService {private static final String SEQ_NAME = "T_SEQ_TEST";/** sequence Service */@ Autowired private MySqlSequence mySqlSequence; public String generateJrn () {try {String sequence = mySqlSequence. getNextValue (SEQ_NAME); sequence = leftPadding (sequence, 8); Calendar calendar AR = Calendar ar. getInstance (); SimpleDateFormat sDateFormat = new SimpleDateFormat ("yyyyMMddHHmmss"); String nowdate = sDateFormat. format (calendar. getTime (); nowdate. substring (4, nowdate. length (); String jrn = nowdate + sequence + RandomUtil. getFixedLengthRandom (6); // 10-bit time + 8-bit sequence + 6-bit random number = 24-bit serial number return jrn;} catch (Exception e) {// TODO} private String leftPadding (String seq, int len) {String res = ""; String str = ""; if (seq. length () <len) {for (int I = 0; I <len-seq.length (); I ++) {str + = "0" ;}} res = str + seq; return res ;}}

The above Sequence Implementation Method Based on Mysql is all the content that I have shared with you. I hope you can give us a reference and support the help house.