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:
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
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
6. Now, to check the exact record I used following query