How to set Transaction Isolation level for a single kodo query?

Question

We have been facing deadlocks in some queries(select queries causing deadlocked update queries). By default kodo READS_COMMITTED,which is okay for update queries. I know that we can set this property at application level :

kodo.jdbc.TransactionIsolation: read-uncommitted

But in my case, I just want to specify the transaction isolation on a single kodo query. Is that possible? I am pretty new to kodo, so any help/comments any highly appreciated.

Using JDO or JPA ? JDO(2.2) allows Transaction.setIsolationLevel. I doubt that Kodo supports JDO 2.2 though, but DataNucleus JDO does. — Neil Stockton, Nov 13 '14 at 09:44
We are using kodo-jdo 3.4.0 and jdo 1.0.2 and we are not upgrading it any time soon. I guess we are stuck then..?? — Shweta, Nov 13 '14 at 15:11

score 0 · Answer 1 · answered Oct 10 '16 at 18:34

Using the documentation of Kodo with JDO:

Kodo provides explicit APIs to lock objects and to retrieve their current lock level. The Kodo JDO KodoPersistenceManager exposes the following methods to lock objects explicitly

You have the next methods of locking a object:

public void lockPersistent (Object pc);
public void lockPersistent (Object pc, int level, long timeout);
public void lockPersistentAll (Object[] pcs);
public void lockPersistentAll (Object[] pcs, int level, long timeout);
public void lockPersistentAll (Collection pcs);
public void lockPersistentAll (Collection pcs, int level, long timeout);

And the next example:

// retrieve the lock level of an object

Stock stock = ...;
int level = KodoJDOHelper.getLockLevel (stock);
if (level == KodoJDOHelper.LOCK_WRITE) 
        PersistenceManager pm = ...;
        pm.currentTransaction ().setOptimistic (true);
        pm.currentTransaction ().begin ();
        // override default of not locking during an opt trans to lock stock object
        KodoPersistenceManager kpm = KodoJDOHelper.cast (pm);
        kpm.lockPersistent (stock, KodoPersistenceManager.LOCK_WRITE, -1);
        stock.setPrice (market.calculatePrice (stock));
        pm.currentTransaction ().commit ();

score 0 · Answer 2 · answered Dec 15 '16 at 16:57

What we end up doing in Kodo 3.4.0 is use CONN_RETAIN_PM (downcasting required):

try {
    pm = (KodoPersistenceManager)
        ((KodoPersistenceManagerFactory) kpmf).getPersistenceManager(false,
        KodoPersistenceManager.CONN_RETAIN_PM);
    Connection c = ((Connection)pm.getConnection());
    c.setTransactionIsolation(
        ISQLServerConnection.TRANSACTION_READ_UNCOMMITTED);

    // ... use pm with retained connection ...

} finally { // reset retained connection, e.g. if pooled
    if (pm != null) {
        Connection c = ((Connection)pm.getConnection());
        c.setTransactionIsolation(
            ISQLServerConnection.TRANSACTION_READ_UNCOMMITTED);
    }
}

How to set Transaction Isolation level for a single kodo query?

2 Answers2