LOCKS IN ORACLE

 ====================================

Find Blocked Sessions in Oracle

===================================

Create Database Lock

Let us create a simple lock inside database and try to create a lock situation

create table lock_test(

  id# number primary key,

  value varchar2(20)

);

insert into lock_test values (1, 'Insert lock test');

Open a new session and try to run the same INSERT command

-- run it in a new session

SQL> insert into lock_test values (1, 'Insert lock test');

Note

In session 1, we did not commit the transaction

Session 2 is trying to insert a record with same primary key value

Session 2 goes on waiting until session 1 issues COMMIT / ROLLBACK

======================

Find Blocked Sessions

========================

Open a new sessions and run below query to find Blocking and Blocked session

select a.SID "Blocking Session", b.SID "Blocked Session"  

from v$lock a, v$lock b 

where a.SID != b.SID and a.ID1 = b.ID1  and a.ID2 = b.ID2 and 

b.request > 0 and a.block = 1;

Blocking Session Blocked Session

---------------- ---------------

              13             135

=============================================

 Another query that can help you with finding the blocking and blocked sessions

=================================

col blocking_status for a120;

select s1.username || '@' || s1.machine

 || ' ( SID=' || s1.sid || ' ) is blocking '

 || s2.username || '@' || s2.machine 

 || ' ( SID=' || s2.sid || ' ) ' AS blocking_status

 from v$lock l1, v$session s1, v$lock l2, v$session s2

 where s1.sid=l1.sid and s2.sid=l2.sid

 and l1.BLOCK=1 and l2.request > 0

 and l1.id1 = l2.id1

 and l2.id2 = l2.id2 ;

 

 >BLOCKING_STATUS

--------------------------------------------------------------------------------

SYS@test.com ( SID=13 ) is blocking SYS@test.com ( SID=135 )

======================

Find Lock Wait Time

===================

To find how long the blocked session is waiting (in minutes)

SELECT 

  blocking_session "BLOCKING_SESSION",

  sid "BLOCKED_SESSION",

  serial# "BLOCKED_SERIAL#", 

  seconds_in_wait/60 "WAIT_TIME(MINUTES)"

FROM v$session

WHERE blocking_session is not NULL

ORDER BY blocking_session;

BLOCKING_SESSION BLOCKED_SESSION BLOCKED_SERIAL# WAIT_TIME(MINUTES)

---------------- --------------- --------------- ------------------

              13             135           52665               4.55

========================

Find Blocked SQL

========================

To check what SQL is being run by the BLOCKED SESSION inside the database OR which SQL command is waiting

SELECT SES.SID, SES.SERIAL# SER#, SES.PROCESS OS_ID, SES.STATUS, SQL.SQL_FULLTEXT

FROM V$SESSION SES, V$SQL SQL, V$PROCESS PRC

WHERE

   SES.SQL_ID=SQL.SQL_ID AND

   SES.SQL_HASH_VALUE=SQL.HASH_VALUE AND 

   SES.PADDR=PRC.ADDR AND

   SES.SID=&Enter_blocked_session_SID;

 SID       SER# OS_ID                    STATUS   SQL_FULLTEXT

---------- ---------- ------------------------ -------- --------------------------------------------------------------------------------

       135      52665 4461                     ACTIVE   insert into emp values (15,'ABC',15)

   

==========================================

Find Locked Table

=====================================

Run below query to find the table locked, table owner, lock type and other details

col session_id head 'Sid' form 9999

col object_name head "Table|Locked" form a30

col oracle_username head "Oracle|Username" form a10 truncate 

col os_user_name head "OS|Username" form a10 truncate 

col process head "Client|Process|ID" form 99999999

col owner head "Table|Owner" form a10

col mode_held form a15

select lo.session_id,lo.oracle_username,lo.os_user_name,

lo.process,do.object_name,do.owner,

decode(lo.locked_mode,0, 'None',1, 'Null',2, 'Row Share (SS)',

3, 'Row Excl (SX)',4, 'Share',5, 'Share Row Excl (SSX)',6, 'Exclusive',

to_char(lo.locked_mode)) mode_held

from gv$locked_object lo, dba_objects do

where lo.object_id = do.object_id

order by 5

/

>

                           Client

      Oracle     OS         Process                  Table                          Table

  Sid Username   Username   ID                       Locked                         Owner      MODE_HELD

----- ---------- ---------- ------------------------ ------------------------------ ---------- ---------------

  135 SYS        oracle     4461                     EMP                            SYS        Row Excl (SX)

   14 SYS        oracle     5036                     EMP                            SYS        Row Excl (SX)

=============================

Resolving Locks in Oracle

=============================

As per Oracle, the blocked (or waiting) session will continue to wait until

Blocking session issues a COMMIT

Blocking session issues a ROLLBACK

Blocking session disconnects from the database

As a DBA, the only way you can help resolve a lock conflict is by killing either the blocking session or by killing the blocked (waiting) session.

Before you decide which session to kill, you must send the information to application team to get their approval on which session to be killed.

Use below query to kill a session inside Oracle

SQL> alter system kill session '35,52665';

--actual syntax

SQL> alter system kill session 'SID,Serial#';

If you don’t know the serial# then query v$session

SQL> select serial# from v$session where SID=135;

   SERIAL#

----------

     52665

====================

SELECT FOR UPDATE

SELECT FOR UPDATE command will select the specific records from the table and lock those records. This prevents other users from updating the records

 as the lock on those records is held by the current session.

SELECT * FROM EMP WHERE ID=10 FOR UPDATE;

Let us assume that some other session is holding lock on the records returned by – SELECT * FROM EMP WHERE DEPTNO=10. The above query will wait until the lock is released.

Locking a Table:

SQL> LOCK TABLE emp IN EXCLUSIVE MODE;

Table(s) Locked.

==========================================

Removing Deadlocks in Oracle

=======================================

You locked row A, another session locked row B. Now, you want to lock row B but you are blocked by the other session.

 The other session wants to lock row A but they are blocked by you. Hence, deadlock!

Create a Deadlock 

Resolving Deadlock 

Read our article on finding locked objects inside database and killing sessions!

Deadlock is a situation where two or more transactions are waiting for one another to give up locks.

ex-

SQL> update emp set name='MNL' where id =10;

1 row updated.

SQL> update emp set name='SCOT' where id =11;

----waiting session 

SQL> update emp set name='SCOT' where id =11;

1 row updated.

SQL> update emp set name='MNL' where id =10;

update emp set name='MNL' where id =10

               *

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

==================

Resolving Deadlock

==================

Oracle is smart and it is able to find deadlock situation within 3 seconds. Below are the options to avoid deadlocks inside the database

Ask the session getting deadlock error ORA-00060 to issue either COMMIT or ROLLBACK

Ask the waiting session to kill the SQL / transaction

Look inside alert log / trace file for the sessions involved in deadlock and inform application team to improve the code

As a DBA, whenever you get a Deadlock alert, immediately contact application team and inform them.