oracle数据库并不保证sequence生成顺序与的数据插入顺序的保持一致。

问题域描述：最近的一个项目中有一个增量数据库（oracle）与服务方、调用方两个方面的应用组成，其中增量数据库里会有其它多个不同的应用不断的插入数据且这些数据的PK是按序增长的。现调用方希望用轮询并得到这些增量数据。最初的设计是：服务方提供返回增量数据的接口，类似于 getDataAfterId() ，对此接口的定义要求是：根据数据的ID将返回比这个ID大的增量数据。当然这样设计的好处是服务方并不用关心什么是增量数据，只依赖调用方的提供的ID返回数据。

问题表现：如上述的 getDaterAfterId() 并不能如实的返回增量数据，因为oracle数据库不保证sequence生成顺序与数据的插入顺序保持一致，当考虑到：当较小的sequence(id)产生后，但后插入数据，将会导致这笔数据有遗漏状况发生。如：sequence 的产生本身是有序的，即先后生成 4，5，6。但可能会id=5这条数据较晚插入。造成getDataAfterId(6)时，id=5这条数据并不能被调用方正确拿到。

问题原因：oracle数据库并不保证sequence生成顺序与数据的插入顺序保持一致。

问题分析与验证：

首先对上述原因持怀疑的态度，查阅了相关文档参见，http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6015.htm

文档描述摘抄如下，因语焉不详或理解有限，造成对NOCACHE 和order仍存在幻想。所以又从如下几个方面加以验证：

NOCACHE  Specify NOCACHE to indicate that values of the sequence are not preallocated. If you omit both CACHE and NOCACHE, then the database caches 20 sequence numbers by default.
 
ORDER Specify ORDER to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
 
ORDER is necessary only to guarantee ordered generation if you are using Oracle Real Application Clusters. If you are using exclusive mode, then sequence numbers are always generated in order.
 
NOORDER  Specify NOORDER if you do not want to guarantee sequence numbers are generated in order of request. This is the default.

实验一：在应用程序中分配sqeuence 模拟并发情形执行 如下sql

insert into table_name (BIZID,BIZTYPEID,THREADID,LASTMODIFYTIME) "+
 " values(S_S_BIZ.Nextval,?,?,?)

1）考虑数据库sequence 中的参数设置 cache与order，分别建有cache的 和无cache的sequence

SQL> select * from user_sequences;

 2)对应建两张表，分别以上面的sequence 作为PK。

3）模拟并发情形，开辟10个DB的connection, 用10个线程分别连接之，每个线程中插入4条数据，最后批量commit。采用JDBC连接，不使用任何ORM框架。

调用代码如下：

package com.smart.db
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import com.sse.db.connect.DBConnectionManager;

public class MyTester {

	/**
	 * @param args
	 * @throws IOException 
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws IOException, SQLException, ClassNotFoundException {

		System.setProperty("db.home", 
		"F:/private_ws/DBTester/src/conf/db.properties");
        System.out.println(System.getProperty("db.home"));
        String relativeFilePath = System.getProperty("db.home");
        DBConnectionManager connectMain  =DBConnectionManager.getInstance(relativeFilePath);
        Connection[] conns=new Connection[10];
        int iNum=10;
        for(int i=0 ;i<iNum;i++){
            conns[i]=connectMain.getConnection("default"+new Integer(i).toString());
        }
        int count=0;
        do{
           (new Thread(new DBWriter(conns[count % iNum]))).start();
           
        	count++;
        }while(count<=iNum);
        System.out.println("Main  end");
  
	}

}

线程的run方法如下：

package com.smart.db
import java.sql.Connection;
import java.sql.SQLException;

public class DBWriter implements Runnable {

	private DBTesterDao dto;
	private Connection conn;
	
	
	public DBTesterDao getDto() {
		return dto;
	}


	public void setDto(DBTesterDao dto) {
		this.dto = dto;
	}
    

	public DBWriter(Connection conn) throws SQLException {
		super();
		this.conn = conn;
		setDto(new DBTesterDaoImpl(this.conn));
	}

	@Override
	public void run() {
		
		BizBean bizBean=new BizBean();
		Thread currentThread = Thread.currentThread(); 
		bizBean.departName=new Long(currentThread.getId()).toString();
		try {
			dto.insertBizEnitityWithOrder(bizBean);
			dto.insertEnitityWoOrder(bizBean);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}

入库的代码片段如下,即DBTesterDaoImpl.java：

String sql="insert into S_BIZWOORDER(BIZID,BIZTYPEID,DEPARTNAME,LASTMODIFYTIME) "+
	" values(S_S_BIZ.Nextval,S_S_BIZ.Nextval,?,?)";
	@Override
	public void insertBizEnitityWithOrder(BizBean bizBean) throws SQLException{
		int i=0;
		while(i++<4){
			PreparedStatement pstm = null;
			try {
					pstm = conn.prepareStatement(sql);
					//pstm.setInt(1,  bTizBean.BizId);
					//pstm.setInt(1, bizBean.BizTypeId);
					pstm.setString(1, bizBean.departName);
					pstm.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
					pstm.executeUpdate();
				} catch (SQLException e) {
					throw e;
				}finally{
					try {
						if(pstm != null) pstm.close();
					} catch (SQLException e) {
					}
				}
			 	
//			 try {
//				Thread.sleep((long) (1000*Math.random()));
//			} catch (InterruptedException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}	
		    
		}
		 conn.commit();
		System.out.println("commit1 end");
	     
	}

4）执行结果如下：

 

5）结论：在实际应用中oracle中的sequence的生成，并不保证与插入数据的先后顺序保持一致。sequence小的数据行，在并发的情形下，往往会后插入。

                 当某个线程中的数据在批量提交之前程序本身crash了或直接终止，oracle已分配给这个线程的sequence并不会被收回。而会出现sequence不连续的情况。

实验二：利用triger 生成 sequence ,验证此时数据的sequence生在是否与数据的插入顺序保持一致。

triger的代码如下：

create or replace trigger tri_s_biz_id 
before  insert on S_BIZ
    for each row 
declare nextid number;
  begin
    IF :new.BIZID IS NULL THEN 
      select S_S_Biz.nextval 
      into nextid from sys.dual;
      :new.BIZID:=nextid;
    end if;
  end  tri_s_biz_id;

入库的代码改成：

String sql="insert into S_BIZ(BIZTYPEID,DEPARTNAME,LASTMODIFYTIME) "+
	" values(2,?,?)";

执行结果：

结论也与实验一的相同，在此就不在重复。

实验三：在mysql 建一张类似的表，bizid设置成AutoIncreament 

建表代码：

CREATE TABLE `s_biz` (
	`BIZID` INT(10) NOT NULL AUTO_INCREMENT,
	`BIZTYPEID` INT(10) NULL DEFAULT '0',
	`THREADID` INT(10) NULL DEFAULT '0',
	`LASTMODIFYTIME` TIMESTAMP NULL DEFAULT NULL,
	PRIMARY KEY (`BIZID`)
)

实验数据如下：

综上所述：1）oracle数据库本身并不保证sequence生成顺序与数据的插入顺序保持一致，所以基于这两者一致性的假设，都可能会导致错误结果发生。

                     2）mysql数据库中Auto_Increment 属性也不保证PK的生成顺序与数据的插入顺序保持一致，所以基于这两者一致性的假设，都可能会导致错误结果发生。