Oracle Query to Check Lock on Rows and Objects

Locks can occur whenever any session holds exclusive lock on any specific object and another session tries to update the same record.  This lock will get released only when first session completes its work.

Locks on rows can also cause performance issues and can stop any transactions from completion.I have used the following example to provide more detail about locks

In First Session –

Here, I had written PL/SQL block, where I am using SELECT query on EMP table using FOR UPDATE to get exclusive lock on the table.

DECLARE
   TYPE TAB_EMP IS TABLE OF EMP%ROWTYPE;
   TYP_EMP   TAB_EMP;
BEGIN
  SELECT   *
   BULK COLLECT INTO TYP_EMP
         FROM EMP
   FOR UPDATE   ;

   FOR i IN 1 .. 10
   LOOP
      UPDATE EMP
         SET SAL = SAL + 100
       WHERE EMPNO = 7839;
   END LOOP;
END;
/

In Second Session –

Here, I will update EMP table WHERE EMPNO = 7839 at the same time

UPDATE EMP SET SAL = SAL + 1000  WHERE EMPNO = 7839;

Now after running the above UPDATE query, it kept running continuously.

3. To check the slowness of query in above statement, I ran the following query on v$lock view to check any lock on table:

oracle lock query

As shown above, here one session (SID 16) was blocking another session (SID 23).

Note: v$lock is a view that lists the lock currently held by the Oracle Database.

4. Now, I used the following query to check for all locked objects

oracle lock query

After running query on v$locked_object, dba_objects, v$lock & v$session views, we can see that there is a row level lock on EMP table.

Note: Row-S means Row Share Lock & Row-X means Row Exclusive Rock

5. Now, to identify the row id for the locked record I used following query on v$session and dba_objects

oracle lock query

6. Now, to check the exact record I used following query

oracle lock query

In this way, we can find the more precise details about locked objects.  You can download all of the above queries here 

You may also like...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.