Thursday, August 16, 2012

Locking In Oracle

Locking in Oracle is one of the most common problem we will face as database administrator.


 is the locking Effect on Database performance ?

Yes . impede a transaction from finishing , since the Lock query Take long time running .

When the Locking Happened ?

I will Give you example :

Let assume that we have two Users Each Of them Update on the same table like the following :

User 1 :

SQL> update test set name='lock' where id=1;

1 row updated.

User didn't commit here .

User 2 :

SQL> update test set name='lock2' where id=1;

User 2 will be waiting

Inforamtion about locks :
1-Locks scripts One.
2-Locks Scripts Two

Another way to Lock :

performing a DDL (alter,create....) and get an ORA-00054 error.

ORA-00054: resource busy and acquire with NOWAIT specified
 to solve this issue

SQL> select object_id from dba_objectswhere owner='Username'  and object_name='Table';

 OBJECT_ID
----------
 
SELECT c.owner,
 c.object_name,
 c.object_type,
 b.sid,
 b.serial#,
 b.status,
 b.osuser,
 b.machine
FROM v$locked_object a, v$session b, dba_objects c
WHERE b.sid = a.session_id AND a.object_id = c.object_id
and a.object_id=;

Refer also to :
1-Locks


Osama Mustafa



No comments:

Post a Comment