问题：Unique约束与ORA-08177: 无法连续访问此事务处理

前语：

       这里关注的是一个Oracle问题，高手路过者请关注。

数据库：Oracle 10g Release 2

JDBC:     ojdbc14- 10.2.0 .4.jar

表结构:

CREATE TABLE Metadata

  (

    id           int,

    uuid         varchar(250)   not null,

     isTemplate   char(1)        default 'n' not null,

    isHarvested  char(1)        default 'n' not null,

    createDate   varchar(24)    not null,

    changeDate   varchar(24)    not null,

    data         long           not null,   

    harvestUuid  varchar(250),   

    primary key(id),

    unique(uuid,source,harvestUuid),

    foreign key(owner) references Users(id),

    foreign key(groupOwner) references Groups(id)

  );

CREATE INDEX MetadataNDX1 ON Metadata(uuid);

CREATE INDEX MetadataNDX2 ON Metadata(source);

事务隔离级别：TRANSACTION_SERIALIZABLE

程序语句：

addMetadata( Dbms dbms , List <> list )

{

       For( int i=0; i<list.size(); i++ )

       {

              Element md = (Element)list.get(i);

              insertMetadata( dbms );

              String isTemplate = getTemplateInfo( … );

              setTemplate( dbms , isTemplate );

              String uuid = getHarvestedUuid(..);

              setHarvested( dbms , uuid );

              dbms.commit();

       }

}

insertMetadata ()

{

StringBuffer fields = new StringBuffer("id, uuid, data, isTemplate,       isHarvested, data, createDate, changeDate");

StringBuffer values = new StringBuffer("?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?");

       Vector args = new Vector();

       args.add(new Integer(serial));

       args.add(….);

       …;

       String query = "INSERT INTO Metadata (" + fields + ") VALUES(" + values + ")";

       dbms.execute(query, args.toArray());

}

public void setTemplate(Dbms dbms, int id, String isTemplate, String title) throws Exception

    {

       if (title == null) dbms.execute("UPDATE Metadata SET isTemplate=? WHERE id=?", isTemplate, id);

       else               dbms.execute("UPDATE Metadata SET isTemplate=?, title=? WHERE id=?", isTemplate, title, id);

    }

public void setHarvested(Dbms dbms, int id, String harvestUuid) throws Exception

    {  

       String value = (harvestUuid != null) ? "y" : "n";

       String query = "UPDATE Metadata SET isHarvested=?, harvestUuid=? WHERE id=?";      

       dbms.execute(query, value, harvestUuid, id);           

    }

出现问题：

运行过程中，偶尔会在setHarvested函数内的update语句抛出异常（大概每循环300次左右，抛出一次，其余全部正常）：

java.sql.SQLException: ORA-08177: 无法连续访问此事务处理

排查：

setHarvested同update对ahavestedUuid字段做了更新，该字段属于unique( uuid, source, harvestUuid)。

1)      将harvestedUuid改为其它字段，如isTemplate, changeDate等都没有该异常。表明该异常与unique有关。

2)      将harvestUuid改为定长字段，照样有异常。

3)      将harvestUuid改为同为unique里的source, 不出现异常。注：source在前面插入语句里已经赋值，但harvestUuid在insert里面没有处理。

4)      测试过程中每条记录的id, uuid都不会重复。因此实际上unique在程序运行过程中是满足的，在抛出异常的时候去检测也没有找到重复的uuid和id。

5)      将unique语句变更：unique( uuid, source)，没有异常抛出。进一步说明该异常与unique字段有关。

暂时解决方案：

暂时处理：将unique(uuid, source, harvestUuid)改为unique( uuid )

查过一些Oracle的文档。

 

ORACLE: ORA-08177: can't serialize access for this transaction
Q:When I configure SERIALIZABLE isolation level for the channels, I frequently get errors like ORA-08177:Can't serializa access for this transaction. How do I resolve this?
A: Answer for this is best answered by Oracle DB experts. The following is written in Oracle documentation.Oracle database uses control information stored in the data block that indicates which rows in the block contain committed and uncommitted changes. In a sense, the block contains a recent history of transactions that affected each row in the block. The amount of history that is retained is controlled by the INITRANS parameter of CREATE TABLE and ALTER TABLE. Under some circumstances, Oracle can have insufficient history information to determine whether a row has been updated by a "too recent" transaction. This can occur when many transactions concurrently modify the same data block, or do so in a very short period. You can avoid this situation by setting higher values of INITRANS for tables that will experience many transactions updating the same blocks. Doing so enables Oracle to allocate sufficient storage in each block to record the history of recent transactions that accessed the block. One of the solutions for this problem is set higher value(say 5) in INITRANS to table that throws the ORA-08177 errors. If table name is 'TEST', then you may use the following command: ALTER TABLE TEST INITRANS 5;

如果真是上面所说的那样操作太频繁了，也就不会出现测试1)的情况了。

笔者虽然解决了一点小问题，但始终不能明白是什么原因导致该异常会被抛出?

请问路人，您知道吗？能说说么？