Oracle Database Discussions

we are facing locking issue in our database as one of our application's session is holding locks on a particular table's row . we get one locking issue in two or three days. There is a request from development team to find the sql which is causing the lock to be held on the table.

We are able to recreate the locking issue in a test environment . But the problem is , the blocking session in v$session does not have correct sql_id which is responsible for holding the lock . Here are the list of sqls ran to recreate the issue in test environment .

create table test (id number);

insert into test select rownum from all_objects where rownum < 10;


Session1 :

update test set id = 21 where id =1 ;
update test set id = 22 where id = 2;
update test set id = 32 where id = 3;
< do not commit or rollback>

Session 2:
update test set id = 41 where id = 1 ;

< the session hangs here >


From third session :

SQL> select * from v$lock where block > 0 or request > 0 ;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
==== ======= ==== === ==== ==== ===== ======= ===== ===
3F258730 3F258744 291 TX 196628 1030 0 6 105 0
3E1A2E24 3E1A2F40 324 TX 196628 1030 6 0 132 1


SQL> select b.sid, b.sql_id ,a.sql_text from v$sqltext a , v$session b where b.sql_id = a.sql_id (+) and b.sid in (291,324);

SID SQL_ID SQL_TEXT
===== ======= ====================
291 b10dpkc5yx47y update test set id = 41 where id = 1
324


Session 324 is holding the lock on row in table TEST and session 291 is waiting for the lock to be released.

How do we find the sql text which is causing the lock to be held on the particular row in the table TEST eg. "update test set id = 21 where id =1 " from session 1 in this case .


Regards,
Jawahar.

Edited by: Jawahar on Jun 2, 2009 3:29 PM