JDBC transaction dead-lock: solution required?

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.

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.

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.

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: Session A updates a record, #1234. Session B updates another record #5678.

Session A updates the record, #5678. Session B updates the record #1234. Session A issues a commit.

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).

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.