Force rollback after lock

[SBGibson]

Hi guys, I've a strange situation using oracle with the .Net provider.

1. I open a transaction
2. I LOCK a table
3. The network have a failure (I force it unplugging the cable from the adapter)
4. The transaction and expecially the lock remains there for an indefinite time and I can do nothing with that table if I didn't restart the oracle service.

I know that is not a good thing to lock a table but my questions are:
1. there's a way to define a lock timeout for automatically rollback a transaction that is not closed until a certain time?
2. There's a way to force the rollback of an existing transaction? I saw the ROLLBACK FORCE statement but I'm not able to understand where can I find the Transaction ID.

Thanks in advance, happy 2006.

Stevie B. Gibson

 

[SantaMufasa]

SB,

A DBA (or someone with the "ALTER SYSTEM" privilege) can kill a session that holds locks on an object.

Here is my script that I use to identify not only what locks exist on objects, but the script also displays the Session ID (SID) and Session Serial number, which, together provide the necessary information for one to kill a locking session. (The script even encloses those data in single quote to facilitate your issuing the "...kill session..." command. Also, I widened the display on this thread to accommodate "unwrapped" output for easier understanding.):

Section 1 -- Code and output for the "locks.sql" script:

ttitle "Lock Listing"
set linesize 150
set echo off
col oruser format a16 heading "Oracle Username"
col osuser format a13 heading "O/S Username"
col obj format a20 heading "Locked Object"
col ss heading "SID/Ser#" format a12
col time heading "Logon Date/Time" format a19
col rs heading "RBS|Name" format a4
col unix heading "O/S |Process" format a9
col computer heading "Machine name|of Locker" format a20
set linesize 120
select     owner||'.'||object_name obj
   ,oracle_username||' ('||s.status||')' oruser
   ,os_user_name osuser
   ,machine computer
   ,l.process unix
   ,''''||s.sid||','||s.serial#||'''' ss
   ,r.name rs
   ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
from       v$locked_object l
   ,dba_objects o
   ,v$session s
   ,v$transaction t
   ,v$rollname r
where l.object_id = o.object_id
  and s.sid=l.session_id
  and s.taddr=t.addr
  and t.xidusn=r.usn
order by osuser, ss, obj
/
ttitle off
set linesize 132

Mon Jan 02                                                                                                     page    1
                                                      Lock Listing

                                                    Machine name         O/S                    RBS
Locked Object        Oracle Username  O/S Username  of Locker            Process   SID/Ser#     Name Logon Date/Time
-------------------- ---------------- ------------- -------------------- --------- ------------ ---- -------------------
TEST.AUDITOR         TEST (ACTIVE)    DOMAINX\dhunt DOMAINX\TFMOBILE2709 2008:1080 '7,123'      RBS1 2006/01/02 13:04:33
TEST.S_EMP           TEST (ACTIVE)    DOMAINX\dhunt DOMAINX\TFMOBILE2709 2008:1080 '7,123'      RBS1 2006/01/02 13:04:33
************************************************************************************************************************

Section 2 -- Application of information from the script output to release the locks:

SQL> alter system kill session '7,123';

System altered.

SQL> @locks

no rows selected

SQL>

Let us know, SB, if this helps to resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SBGibson]

It seems to work well, thanks

This let me think that is not possible to handle a timeouted automatic unlock handled by Oracle :-/

Btw this i s a solution.

Stevie B. Gibson

 

[SantaMufasa]

Stevie Bee, you can "kinda, sorta" simulate what you want by creating an Oracle PROFILE that limits the idle_time of sessions that use that profile. Granted, idle_time doesn't necessarily relate to lock timeouts, but, for your purposes, it might be close enough to satisfy your need:

CREATE PROFILE stevie_bee LIMIT IDLE_TIME 5;

ALTER USER <some_user> PROFILE stevie_bee;

For this to work, your instance must have the following parameter:

resource_limit = TRUE

Let us know if this is "close enough for government work".

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.