Issues with JCART operating in separate transaction.

User 7f33ec9a5c

25-09-2013 17:20:31

Hi,


We are encountering a known transactional issue when dealing with a table indexed with jchem.JC_IDXTYPE, and we need a workaround.


We have an oracle VARCHAR2(4000) column of unique smiles, constrained with an oracle Unique constraint.


In registering new smiles, we first do a jc_compare(<smiles_column>,'NEWSMILES','t:d tautomerSearch:n') to test a smiles for uniqueness.  If the smiles is not found in the exact match we then insert it to the database.  We periodically commit the inserts every few smiles, however we don't commit after every smiles.


When we try and load two identical SMILES in the same transaction, the first smiles inserts but is not seen by the JChem server because we have not committed yet, so when the second identical smiles comes along, jc_compare(<smiles_column>,'NEWSMILES','t:d tautomerSearch:n') = 0 as expected, because the first smiles has not been committed, so it is not know to the JChem server, leading to  jc_compare(<smiles_column>,'NEWSMILES','t:d tautomerSearch:n')  giving the wrong result.  This leads our loader to attempt to insert the 2nd smiles, which is stopped by the oracle unique constraint on the SMILES column.


How can we maintain transactional sanity in this scenario? I know the out-of-process jchem server creates serious transactional issues, but I'm guessing that by now ChemAxon has a workaround for this issue which must be all too common.

ChemAxon aa7c50abf8

25-09-2013 17:30:03

Hi,


With duplicate search, you can set the requireCommit search option to n so that INSERTs in the same transaction are included in the search input.


Let us know if this doesn't help.


Peter