Transaction Isolation level SERIALIZABLE

[Martin Thelian <ma...@lovo.cc>]

Hi!

What transaction isolation level is used by cayenne and is there a way
to change it to use serializable transactions?
If this is not possible, what other possibilities do I have to lock
table rows or to at least detect concurrent changes? The only hint I've
found is a description how to use optimistic locking in [1].

Thanks,
Martin

[1] http://cwiki.apache.org/CAY/optimistic-locking-explained.html

Re: Transaction Isolation level

[Andrus Adamchik <an...@objectstyle.org>]

On Oct 4, 2007, at 1:45 PM, Martin Thelian wrote:

> Yes, but this would not help to avoid a possible race-condition  
> between
> thread1 and another thread2. Because there is no guarantee that  
> thread2
> has not already send an SQL statement via the connection when  
> thread1 is
> reseting the transaction isolation level.

Ok, looks like you are right, and there is a possibility of a race  
condition between tx.commit/rollback and the user code resetting the  
connection policy. A workaround would be a TransactionDelegate:

* willAddConnection may set the isolation level
* didCommit/didRollback may reset it back before the connection is  
returned to the pool.

Do you think this will work?

Andrus

Re: Transaction Isolation level

[Martin Thelian <ma...@lovo.cc>]

Andrus Adamchik schrieb:
>>>> and is there a way to change it to use serializable transactions?
>>> There's no direct API (maybe we should add it to Cayenne Transaction
>>> in 3.0?), but you can enable serializable transactions by wrapping
>>> your code in a Cayenne transaction manually [1] (see "User-Defined
>>> Transaction Scope" down the bottom), and setting Connection properties
>>> by hand.
>> Thank you for this tip.
>> It seems to work fine, except that the connection with the changed
>> isolation level is returned to pool [...] and therefore the next
>> thread (thread2) gets this connection
>> from pool, but with the changed transation-isolation-level.
> Or you can reset the state in the "finally" block of the manual
> transaction.
Yes, but this would not help to avoid a possible race-condition between
thread1 and another thread2. Because there is no guarantee that thread2
has not already send an SQL statement via the connection when thread1 is
reseting the transaction isolation level.

What about exclusive table locks? Any chance to use them with cayenne?

Thanks,
Martin

Re: Transaction Isolation level

[Andrus Adamchik <an...@objectstyle.org>]

On Oct 4, 2007, at 1:07 PM, Martin Thelian wrote:

> Andrus Adamchik schrieb:
>>> and is there a way to change it to use serializable transactions?
>> There's no direct API (maybe we should add it to Cayenne Transaction
>> in 3.0?), but you can enable serializable transactions by wrapping
>> your code in a Cayenne transaction manually [1] (see "User-Defined
>> Transaction Scope" down the bottom), and setting Connection  
>> properties
>> by hand.
> Thank you for this tip.
> It seems to work fine, except that the connection with the changed
> isolation level is returned to pool once transaction.commit() is  
> called
> by thread1, and therefore the next thread (thread2) gets this  
> connection
> from pool, but with the changed transation-isolation-level. A
> race-condition between thead1 and thread2 could arise if the
> isolation-level would be set back by thread1 after the
> transaction-commit. So each thread in the system would need to  
> check the
> transaction-isolation-level of a connection to ensure that the proper
> isolation-level is used.
>
> Martin

Or you can reset the state in the "finally" block of the manual  
transaction.

Andrus

Re: Transaction Isolation level

[Martin Thelian <ma...@lovo.cc>]

Andrus Adamchik schrieb:
>> and is there a way to change it to use serializable transactions?
> There's no direct API (maybe we should add it to Cayenne Transaction
> in 3.0?), but you can enable serializable transactions by wrapping
> your code in a Cayenne transaction manually [1] (see "User-Defined
> Transaction Scope" down the bottom), and setting Connection properties
> by hand.
Thank you for this tip.
It seems to work fine, except that the connection with the changed
isolation level is returned to pool once transaction.commit() is called
by thread1, and therefore the next thread (thread2) gets this connection
from pool, but with the changed transation-isolation-level. A
race-condition between thead1 and thread2 could arise if the
isolation-level would be set back by thread1 after the
transaction-commit. So each thread in the system would need to check the
transaction-isolation-level of a connection to ensure that the proper
isolation-level is used.

Martin

Re: Transaction Isolation level SERIALIZABLE

[Andrus Adamchik <an...@objectstyle.org>]

On Oct 4, 2007, at 10:46 AM, Martin Thelian wrote:

> Hi!
>
> What transaction isolation level is used by cayenne

By default in-memory changes to the DataContext are not wrapped in a  
transaction. Life-span of an underlying DB transaction is much  
shorter, roughly corresponding to the 'performQuery' or  
'commitChanges' method call.

> and is there a way
> to change it to use serializable transactions?

There's no direct API (maybe we should add it to Cayenne Transaction  
in 3.0?), but you can enable serializable transactions by wrapping  
your code in a Cayenne transaction manually [1] (see "User-Defined  
Transaction Scope" down the bottom), and setting Connection  
properties by hand. Modifying the example from the docs:

DataDomain domain = Configuration.getSharedConfiguration().getDomain();
Transaction tx = domain.createTransaction();
Transaction.bindThreadTransaction(tx);
try {

    DataNode node = domain.getNode("MyNode");

    // this returns shared thread connection
    Connection c = node.getDataSource().getConnection();
    c.setTransactionIsolation(...);

....


> If this is not possible, what other possibilities do I have to lock
> table rows or to at least detect concurrent changes? The only hint  
> I've
> found is a description how to use optimistic locking in [1].

Yes, optimistic locking is a good strategy for concurrency management  
(wonder why this page is not a part of the User Guide?).

Andrus

[1] http://cayenne.apache.org/doc/understanding-transactions.html