Query on Locks - QUICK HELP REQUIRED!!! 1

[engineer2100]

Hi,

Please help me on this one as it is critical on our architecture.

My app server connects to the DB server and places a lock on a row (SELECT..FOR UPDATE). After few seconds, while still holding the row lock, the connection fails.

My question is after how much time will the Lock be released? Is this time configurable? if yes, please help me with pointers

this is may be a very trivial query to most of you... Santa, Sem - i am counting on you folks again...

Thanks
Engi

 

[SantaMufasa]

Engi,

(Unfortunately, Sem has not been on Tek-Tips for 15 months...I miss that guy!)

There is no timeout on locks...Unless one either explicitly executes a COMMIT or ROLLBACK, or implicitly releases a lock via a termination of a session, something else is occurring to cause your connection to fail.

Not enough information exists here presently to diagnose your problem.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[engineer2100]

Thanks mufasa.

So what will happen to the Lock. Will it still be there...holding the row from being touched for modifications by other processes?

Also can you provide me witha query to query process wise locks (Username, OS user, locks, which SQL was run?). So that I can keep looking for some unhappy situations.

Thanks a million in advance!!!

Regards,
Engi

 

[DrD123]

Hey eng.

Here's a query that'll help ID the locks and what/who/where they're occurring. From there you can decide how to proceed with them:


select
oracle_username
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object lo,dba_objects do
where
lo.object_id = do.object_id;


Good luck
DrD

 

[SantaMufasa]

I'll post my "lock.sql" script, as well. It lists:

Owner and name of locked object
Name of Oracle user locking the object
Name of o/s user locking the object
Machine name of the locker
O/s process id of locker
Oracle session ID and serial # (for easy killing of process)
Name of rollback/undo segment of locking transaction
Date/Time of locking user's login to Oracle:

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 "Unix|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

Here is sample output (which I have "stacked" vertically to avoid ugly wrapping):

Locked Object        Oracle Username  O/S Username 
-------------------- ---------------- -------------
TEST.TABLE1          TEST (ACTIVE)    <domain>\<SID>


Machine name         O/S                    RBS
of Locker            Process   SID/Ser#     Name Logon Date/Time
-------------------- --------- ------------ ---- -------------------
<domain>\<machine>   2600:2376 '8,6'        RBS6 2007/04/13 08:41:36
********************************************************************

Let us know if this is helpful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[engineer2100]

Thanks Mufasa! it surely helps.

Could you also please reply to query "So what will happen to the Lock. Will it still be there...holding the row from being touched for modifications by other processes?"


You said in your first reply "...or implicitly releases a lock via a termination of a session... ". Does this mean that when the session terminates, any reason, the lock gets released? In my case only one row is getting locked at any point in time, over a single thread/connection to the DB.

Thanks,
Engi

 

[SantaMufasa]

Engi,

In Oracle, row locks occur implicitly anytime someone does an INSERT, UPDATE, DELETE, or SELECT...FOR UPDATE.

Locks on all rows of a table occur (during actual execution/completion of the command) when someone does a CREATE, ALTER, or DROP on a table. Oracle implicitly COMMITs changes and releases locks resulting from a CREATE, ALTER, or DROP. (But following a DROP table, there are, of course, no rows left to SELECT.)

When such locks occur, anyone with permission can still read (i.e., SELECT) against any or all rows of the table, but trying to modify a row that is locked results in a "block" to that action until the lock disappears. A reader of the data will see the "old" image of the changed data until the changer does a COMMIT to her/his transaction.

Does this mean that when the session terminates, any reason, the lock gets released?

If a changer exits her/his session gracefully (i.e., EXIT or QUIT), then Oracle COMMITs the changes; if a changer's session abnormally terminates (i.e., electrical failure, DBA kills session, et cetera), then Oracle does a ROLLBACK on the transaction's changes.

In either of the above cases (COMMIT or ROLLBACK), Oracle releases any locks on rows.

Let us know if this resolves your questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[engineer2100]

it has Santa!!! You're great!!!

Thanks a million, again!!

Engi