Mysql提供sequence服务

2019独角兽企业重金招聘Python工程师标准>>>

使用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>

 

转载于:https://my.oschina.net/dreamnight/blog/729777