Transaction Isolation level issues

[j8055]

I'm running some tests with JBoss where I'm spawning two threads within a 'test client'. The first thread updates a complex object and the second thread which is started half a second later simply attempts to read the same object. What I'm testing for is that the read thread returns the updated values not the initial values.

I have changed the transaction isolation levels within the database (SQL2K) configuration each of the following: TRANSACTION_SERIALIZABLE, TRANSACTION_REPEATABLE_READ, TRANSACTION_READ_COMMITTED, TRANSACTION_READ_UNCOMMITTED

When I set to either TRANSACTION_READ_COMMITTED or TRANSACTION_READ_UNCOMMITTED, my test runs through successfully and the results returned by the read thread are the updated values in the object thus it is not allowing a 'dirty read'.

However, when I set it to either TRANSACTION_SERIALIZABLE or TRANSACTION_REPEATABLE_READ the "read" thread hangs and eventually times out.

Does anyone know why I'm getting this kind of behaviour?

[nborwankar]

You don't say whether you commit the transaction on the first thread or not. And whether that makes a difference to the behavior you see

[davidjencks]

I suspect you are deadlocking in the db. I don't have any personal experience with that db but have heard of some apps using it that had to set the isolation level to read uncommitted to avoid deadlock. I think they make liberal use of table or page locks at higher isolation levels.