SQL & PL/SQL

I have a general question about how Oracle prevents deadlocks from affecting
applications: do I need to build support into the application for handling
deadlocks when they occur in this particular scenario, or should Oracle handle
this for me? I'd like to know whether this is "normal" behavior for an Oracle
application, or if there is an underlying problem. Consider the following situation:

Two sessions issue individual SELECT FOR UPDATE queries. Each query locates
records in the table using a different index. These indexes point to rows in a
different order from each other, meaning that a deadlock will occur if the two
statement execute simultaneously.

For illustrative purposes, consider these rows in a hypothetical table.

ALPHABET
--------
alpha
bravo
charlie
delta
echo
foxtrot
golf
hotel

Index A results in traversing the table in ascending alphabetical order; index
B, descending. If two SELECT FOR UPDATE statements concurrently execute on this
table--one with an ascending order execution path and one in descending order--
the two processes will deadlock at the point where they meet. If Session A
locks alpha, bravo, charlie, and delta, while Session B locks hotel, golf,
foxtrot, and echo, then neither process can proceed. A needs to lock echo, and
B needs to lock delta, but one cannot continue until the other releases its
locks.

This execution path can be encouraged using hints. Executing queries similar to these on larger tables will generate the "collision" as described above.

-- Session A
select /*+ index_asc (customer) */
*
from customer
where gender = 'M'
for update;

-- Session B
select /*+ index_desc (customer) */
*
from customer
where gender = 'M'
for update;

Oracle will recognize that both sessions are in a stand-off, and it will roll
back the work performed by one of the two sessions to break the deadlock.

My question pivots on whether or not, in this situation, the deadlock gets
reported back to the application executing the queries as an ORA-00060. If
these are the ONLY queries executed during these sessions, I would think that
Oracle would rollback the locking performed in one of the SELECT FOR UPDATE
statements. If I understand correctly,

(1) Oracle silently rolls back and replays work performed by UPDATE statements
when a deadlock situation occurs within the scope of the update statement,
and

(2) A SELECT FOR UPDATE statement causes Oracle, at the point in time the cursor
is opened, to lock all rows matching the WHERE clause.

If this is the case, then should I expect Oracle to produce an ORA-00060
deadlock detection error for two SELECT FOR UPDATE statements?

I would think that, for deadlock situations completely within Oracle's control,
this should be perceived to the application invoking the SELECT FOR UPDATE
statements as regular blocking. Since the query execution plans are the sole
reason for this deadlock situation, I think that Oracle would handle the
situation gracefully (like it does for UPDATE, as referenced in (1)).

Notice, from the trace file below, that the waits appear to be from row locking,
and not from an artificial deadlock (e.g. ITL contention).

Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option

DEADLOCK DETECTED
Current SQL statement for this session:
SELECT XXX FROM YYY WHERE ZZZ LIKE 'AAA%' FOR UPDATE
----- PL/SQL Call Stack -----
object line object
handle number name
58a1f8f18 4 anonymous block
58a1f8f18 11 anonymous block
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-002f004b-000412cf 37 26 X 26 44 X
TX-002e0044-000638b7 26 44 X 37 26 X
session 26: DID 0001-0025-00000002 session 44: DID 0001-001A-00000002
session 44: DID 0001-001A-00000002 session 26: DID 0001-0025-00000002
Rows waited on:
Session 44: obj - rowid = 0000CE31 - AAANCFAApAAAAGBAAX
Session 26: obj - rowid = 0000CE33 - AAANCHAArAAAAOmAAM


Thanks for your insight,

- Curtis

(1) "Oracle will silently roll back your update and restart it"
http://tkyte.blogspot.com/2005/08/something-different-part-i-of-iii.html

(2) "All rows are locked when you open the cursor, not as they are fetched."
http://download-east.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/05_ora.htm#2170

Message was edited by:
Curtis Light