JDBC transaction dead-lock: solution required?

Question

My friend has described a scenario and challenged me to find solution. He is using Oracle database and JDBC connection with read committed as transaction isolation level. In one of the transactions, he updates a record, executes a select statement and commits the transaction. When everything happens within a single thread, things are fine. But when multiple requests are handled, dead-lock happens.

1. Thread-A updates a record.
2. Thread B updates another record.
3. Thread-A issues select statement and waits for Thread-B's transaction to complete the commit operation.
4. Thread-B issues select statement and waits for Thread-A's transaction to complete the commit operation.

The above causes dead-lock. Since they use command pattern, the base framework only allows the issuing of a commit only once (at the end of all the db operations), so they are unable to issue commit immediately before select statement.

My argument is: Thread-A is supposed to select all the records which are committed and hence should not be issued. But he said that Thread-A will surely wait till Thread-B commits the record. Is that true?

What are all the ways, to avoid the above issue? Is it possible to change isolation-level (without changing underlying java framework)?

Little information about base framework: it is something similar to Struts action, with each and every request handled by one action, transaction begins before execution and commits after execution.

Answer 1

From here

Oracle explicitly supports the READ COMMITTED and SERIALIZABLE isolation levels as they're defined in the standard. However, this doesn't tell the whole story. The SQL standard was trying to set up isolation levels that would permit various degrees of consistency for queries performed at each level. REPEATABLE READ is the isolation level that the SQL standard claims will guarantee a read-consistent result from a query. In the SQL standard definition, READ COMMITTED doesn't give you consistent results, and READ UNCOMMITTED is the level to use to get nonblocking reads.

However, in Oracle Database, READ COMMITTED has all of the attributes required to achieve read-consistent queries. In other databases, READ COMMITTED queries can and will return answers that never existed in the database. Moreover, Oracle Database also supports the spirit of READ UNCOMMITTED. The goal of providing a dirty read is to supply a nonblocking read, whereby queries are not blocked by, and do not block, updates of the same data. However, Oracle Database doesn't need dirty reads to achieve this goal, nor does it support them. Dirty reads are an implementation other databases must use to provide nonblocking reads.

READ COMMITTED. The READ COMMITTED isolation level states that a transaction may read only data that has been committed in the database. There are no dirty reads (reads of uncommitted data). There may be nonrepeatable reads (that is, rereads of the same row may return a different answer in the same transaction) and phantom reads (that is, newly inserted and committed rows become visible to a query that weren't visible earlier in the trans-action). READ COMMITTED is perhaps the most commonly used isolation level in database applications everywhere, and it's the default mode for Oracle Database. It's rare to see a different isolation level used in Oracle databases.

In Oracle Database, using multi-versioning and read-consistent queries, the answer I get from the ACCOUNTS query is the same in the READ COMMITTED example as it was in the READ UNCOMMITTED example. Oracle Database will reconstruct the modified data as it appeared when the query began, returning the answer that was in the database when the query started.

Answer 2

I believe your friend is correct if the selects are for an update of the record(s) already updated (but not yet committed) by the other thread. If they are simply selecting data, and the JDBC framework isn't helping you by forcing a select for update, then you are correct.

To avoid the issue, make sure you're only selecting for update if you truly need it, and in that case, use the NOWAIT option in the select. This will cause an error to be raised if the operation would block.

Oracle will detect deadlocks and rollback one of the transactions involved.

Answer 3

The scenario as presented will not happen in Oracle for the simple reason that writes do not block reads in that database.

Where we can get a deadlock is in this scenario:

1. Session A updates a record, #1234.
2. Session B updates another record #5678.
3. Session A updates the record, #5678.
4. Session B updates the record #1234.
5. Session A issues a commit.
6. Session B issues a commit.

Oracle will detect the deadlock and rollback one of the Sessions. In traditional client/server applications this situation is avoided by pessimistic locking (SELECT ... FOR UPDATE). In web applications this situation is avoided by using an "optimistic locking column" which is actually no form of locking at all (which is why it avoids deadlocks, albeit at the expense of a lot of additional reads).