| Dmytro Dekhtyaryuk 2005-08-11, 7:23 am |
| 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
|