[ModeShape 5.x, Oracle 11g] Occasional SQLIntegrityConstraintViolationException when storing a binary value

[illia.khokholkov]

I was running abilities testing of the application that utilizes ModeShape 5.x. in a clustered environment (currently, there are 4 members in the cluster). Specifically, such an application is currently running on the latest 5.4-SNAPSHOT of ModeShape. I have a fairly simple test case, where there are N consumers updating N files in parallel, one thread per consumer. Every time fileN needs to be updated, it gets locked first, using shallow session-scoped lock. Every fileN gets updated exactly once. There is no lock contention to update the same fileN. Every time an update needs to be made, the next member of the cluster is requested, i.e. round-robin method is in use. The layout of the repository is presented below:

- root
  - folder1
    - file1
  - ...
    - ...
  - folderN
    - fileN

Occasionally, the following exception occurs:

org.modeshape.jcr.value.ValueFormatException: Error converting InputStream to a BinaryValue
    at org.modeshape.jcr.value.binary.BinaryStoreValueFactory.create(BinaryStoreValueFactory.java:247)
    at org.modeshape.jcr.value.binary.BinaryStoreValueFactory.create(BinaryStoreValueFactory.java:49)
    at org.modeshape.jcr.JcrValueFactory.createBinary(JcrValueFactory.java:142)
    at org.modeshape.jcr.JcrValueFactory.createBinary(JcrValueFactory.java:41)
    ...

Caused by: org.modeshape.jcr.value.binary.BinaryStoreException: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (ORACLE_SCHEMA_NAME.SYS_C003304428) violated

    at org.modeshape.jcr.value.binary.DatabaseBinaryStore.dbCall(DatabaseBinaryStore.java:335)
    at org.modeshape.jcr.value.binary.DatabaseBinaryStore.storeValue(DatabaseBinaryStore.java:162)
    at org.modeshape.jcr.value.binary.BinaryStoreValueFactory.create(BinaryStoreValueFactory.java:244)
    ...

Caused by: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (ORACLE_SCHEMA_NAME.SYS_C003304428) violated

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1046)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1336)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
    at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3714)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1378)
    at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
    at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
    at org.modeshape.jcr.value.binary.Database.execute(Database.java:408)
    at org.modeshape.jcr.value.binary.Database.insertContent(Database.java:244)
    at org.modeshape.jcr.value.binary.DatabaseBinaryStore.lambda$storeValue$0(DatabaseBinaryStore.java:177)
    at org.modeshape.jcr.value.binary.DatabaseBinaryStore.dbCall(DatabaseBinaryStore.java:331)
    ...

In general, does SQLIntegrityConstraintViolationException represent something that you could normally expect from ModeShape when running in a clustered environment? Is this related to the way JGroups messages get delivered, i.e. asynchronous delivery? Many thanks in advance, your help is greatly appreciated.

[illia.khokholkov]

I forgot to mention in the original post that every fileN is mix:versionable and a new version gets created after an update. Additionally, having a different, much simpler test case, I ran into the exact same SQLIntegrityConstraintViolationException when simply performing an update of a given fileN multiple times (actually, thousands of times) via a single thread of execution. Note, that every time the update was made, the next member of the cluster was explicitly asked to fulfill the request.

[hchiorean]

Your exception seems to be related to storing the binary content in the database (not the ModeShape repository data). As far as I can tell, SQLIntegrityConstraintViolationException means that the code tries to insert a binary content in the database with the same ID (which for binary tables is a column:  cid VARCHAR2(255) NOT NULL) as an already existent content.

If you can provide a test case, please log a JIRA (note that a primary key constraint violation should occur on any DB type, not just Oracle).

[hchiorean]

Something else which may/may not aid in creating a test case: whenever JCR nodes use binary properties, behind the scenes ModeShape will create "binary reference documents" which are stored alongside JCR nodes (in the same place) and which contain the binary key (as string) and the number of referrers (at any given time).

This approach allows the generic exclusive locking process (when using DB locking in a cluster) to effectively lock binaries just like any other nodes - namely using SELECT FOR UPDATE binary_key statements. Barring some "interesting" bug (which seems to be your case), when in a cluster, only one cluster node should "process" a binary value at any given time as part of a transaction because only one transaction/cluster node should successfully obtain this lock.