Exception handling by deadlock

From: Dmytro Dekhtyaryuk <dekhtyaryuk_at_materna.de>
Date: Thu, 11 Aug 2005 11:34:11 +0200
Message-ID: <ddf62j$5fq$1@pentheus.materna.de>

Hallo,
under circumstances is the deadlock in our application possible. It's TX- lock (row lock).

Session 1 :
select akt_stand_nr from tbl_SID where sid= 4055000000021 for update wait 3
-- 1 row selected

Session 2 :
select akt_stand_nr from tbl_sid where sid = 4055000000022 for update wait 3
-- 1 Rows selected

Session 1 :
select akt_stand_nr from tbl_sid where sid = 4055000000022 for update wait 3
-- Session waits 3 seconds on commit in session 2.

• During these 3 seconds. Session 2 : select akt_stand_nr from tbl_SID where sid = 4055000000021 for update wait 3
• Deadlock

The deadlock is always on the same Select in both sessions. What is your opinion , is it correct to handle "SELECT FOR UPDATE wait 3" with exception:
DECLARE EXC_DEADLOCK EXCEPTION;
PRAGMA EXCEPTION_INIT (EXC_DEADLOCK, -00060); ...
BEGIN
BEGIN
select akt_stand_nr into v_dummy from TBL_SID where sid = v_sid for update wait 3;
EXCEPTION
WHEN EXC_DEADLOCK
THEN
dbms_output.put_line('Deadlock! ');
SELECT akt_stand_nr into v_dummy from tbl_sid where sid = v_sid; END;

................

END; I saw, that deadlock comes in our sample in session 1, and not in session 2. Is it always so ? Makes it influence on our solution ?

Thanks in advance
Dmytro Dekhtyaryuk Received on Thu Aug 11 2005 - 04:34:11 CDT