problematic read commited behavior - what should i expect?

Question

small simplified question regarding isolation levels using wildfly, hibernate jpa, jta with isolation level READ_COMMITED. assuming the following scenario:

1. Thread 1, on table SomeTable, using JPA with a new transaction, update a specific entity property (a single row in the DB)
2. Thread 2, on table SomeTable, using JPA with a a different transaction, before transaction #1 is commited, executes a findAll (select *).

my question is: from a transaction Isolation perspective (READ_COMMITED). what is expected:

a) Thread 2 blocks on the read operation until thread 1 commits

b) thread 2 is able to read without blocking and, of course, get all data excluding what Thread 1 did not commit yet

I would think option b is the correct one as this is what isolation means in my mind. however, I have encountered 2 database/driver implementations, the first implementing what I described in option a and the second implemented option b. so I am a little confused, not sure if there is a mandatory behavior here and one db simply has a bug. your comments are appreciated!

Answer 1

As defined by the JDBC-4.3 spec (JSR-221), item 10.2 (and I'm pretty sure it goes way back), the proper behavior when you define the transaction isolation to TRANSACTION_READ_COMMITED "means that any changes made inside a transaction are not visible outside the transaction until the transaction is commited", what would "prevent dirty reads, but nonrepeatable reads and phantom reads are still possible".

A dirty read occurs when a uncommitted change is read outside a transaction. A nonrepeatable read occurs when two subsequent reads of a same row are different because between them a commit occurred. A phantom read occurs when different rows may be returned by different reads.

Going back to your description, when using TRANSACTION_READ_COMMITED as transaction isolation level should lead to the "b" scenario: thread 2 should be able to read data existent before thread 1 commits, and if it tries again, should read the new data (probably a phantom read), all without blocking.

The block behavior described in "a" scenario should occur only when isolation is set to TRANSACTION_REPEATABLE_READ (partially, only for the same row) or to TRANSACTION_SERIALIZABLE.

Per spec, if a JDBC driver do not support a lighter transaction isolation, it should adopt a higher (more restrictive) level and, if it can't, throw a SQLException. Probably this is the case with your first JDBC driver, that used a REPEATABLE_READorSERIALIZABLElevel when you set it toREAD_COMMITED. The spec does not mandates that the JDBC driver implementation reports this "upgrade" of level. You may test the supported isolation level using supportsTransactionIsolationLevel` method.