Identify record lock in application and remove

Find Record lock through query which provide two record of same form session in which status of one session should be NOWAIT which should be kill for release the lock.

Usually record lock appear due to multiple users are opening same form with same record & trying to update same transaction.

SELECT a.FINAL_BLOCKING_SESSION BLOKING,
       a.SID,
       a.SERIAL#,
       a.STATUS,
       a.PROGRAM,
       a.SQL_ID,
       a.MODULE,
       a.ACTION,
       a.CLIENT_IDENTIFIER,
       a.BLOCKING_SESSION,
       a.BLOCKING_SESSION_STATUS,
       a.*
  FROM v$session a
 WHERE sid IN (SELECT sid
                 FROM v$lock
                WHERE (id1, id2) IN (SELECT id1, id2
                                       FROM v$lock
                                      WHERE request > 0));

Get SID & SERIAL# from above query and kill the session.

alter system kill session 'SID,SERIAL#';

Remove record lock from the specific user

SELECT *
  FROM v$session
 WHERE     client_identifier LIKE 'SYSADMIN%'
       AND TYPE = 'USER'
       AND STATUS = 'ACTIVE'


alter system kill session 'SID,SERIAL#';

Identify record lock in application and remove

Tags