使用mysql提供sequence的服务,我们在执行的一个方案:
1:创建sequence相关表
CREATE TABLE `seq_order_id` (
`seq` bigint(20) NOT NULL AUTO_INCREMENT,
`stub` varchar(3) NOT NULL,
PRIMARY KEY (`seq`),
UNIQUE KEY `stub` (`stub`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8;
2:获取seq时相关SQL:
REPLACE INTO seq_order_id(stub) VALUES (#stub#)
SELECT last_insert_id()
这种方式可以一个表提供给多个业务做seq的服务,但是由于REPLACE INTO会有锁表状况存在,在业务量很大时,对数据库的性能影响较大。
3:修改为使用spring的org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer,方法加了同步锁,每次数据库操作会直接获取一个段的值cacheSize,业务分布式部署也不影响sequence的唯一性
@Override
protected synchronized long getNextKey() throws DataAccessException {
if (this.maxId == this.nextId) {
/*
* Need to use straight JDBC code because we need to make sure that the insert and select
* are performed on the same connection (otherwise we can't be sure that last_insert_id()
* returned the correct value)
*/
Connection con = DataSourceUtils.getConnection(getDataSource());
Statement stmt = null;
try {
stmt = con.createStatement();
DataSourceUtils.applyTransactionTimeout(stmt, getDataSource());
// Increment the sequence column...
String columnName = getColumnName();
stmt.executeUpdate("update "+ getIncrementerName() + " set " + columnName +
" = last_insert_id(" + columnName + " + " + getCacheSize() + ")");
// Retrieve the new max of the sequence column...
ResultSet rs = stmt.executeQuery(VALUE_SQL);
try {
if (!rs.next()) {
throw new DataAccessResourceFailureException("last_insert_id() failed after executing an update");
}
this.maxId = rs.getLong(1);
}
finally {
JdbcUtils.closeResultSet(rs);
}
this.nextId = this.maxId - getCacheSize() + 1;
}
catch (SQLException ex) {
throw new DataAccessResourceFailureException("Could not obtain last_insert_id()", ex);
}
finally {
JdbcUtils.closeStatement(stmt);
DataSourceUtils.releaseConnection(con, getDataSource());
}
}
else {
this.nextId++;
}
return this.nextId;
}
<bean id="orderIdSeqGenerater" class="org.springframework.jdbc.support.incrementer.MySQLMaxValueIncrementer">
<property name="incrementerName" value="seq_order_id"/>
<property name="columnName" value="seq"/>
<property name="cacheSize" value="1000"/>
<property name="dataSource" ref="dataSource"/>
</bean>