How to resolve lock conflicts in Oracle?

For resolving locks first we will have to understand how locks are created.
Suppose two sessios s1 and s2 are going on simultaneously
s1 entered following command:

SQL>update emp
set sal=sal+100
where empid=50;

> Here lock is acquired by s1 over s2 as there is no commit or rollback happened.

s2 entered following command:

SQL>update emp
set sal= sal*1.1
where emp_id=100;

> Here s2 is waiting for s1 to release lock.

So how we will know which session has acquired a lock and which session is waiting to take lock?

Run following query :

SQL> select sid, serial#, username, blocking_session from v$session where username is not null;

SID SERIAL# USERNAME BLOCKING_SESSION
———- ———- —————————— —————-
19 5 SCOTT
125 5 SYS
139 29 SCOTT 19

To Kill session we sould have privilages

Command to kill session

SQL>alter system
kill session ‘sid,serial#’;

e.g
SQL>alter system
kill session ‘108,1005’ immediate;

Above scenario was just locking and not deadlocking.

Oracle automatically handles deadlock conditions with giving error message.
This error message needed for handling error mechanisms prperly(One of the example of auto handling errors
is ‘WHEN OTHERS THEN‘ in execption handling)
Deadlocks are created in following case

s1 entered follwing command:

SQL>update emp
set sal=sal+1000
where empid=105;

here s1 acquired lock foe empid 105.

s2 entered following command:

SQL>update emp
set mgr=500
where empid=110;

here s2 acquired lock for empid 110.

s1 entered following command:

SQL>update emp
set sal=sal+2000
where empid=110;

s1 is waiting to release lock by s2.

s2 entered following command:

SQL>update emp
set mgr=500
where empid=105;

s2 is waiting to release lock by s1

Here we will get ORA-00060:
Deadlock detected while waiting for resource.

Author Profile

Tejas

Passionate traveller,Reviewer of restaurants and bars,tech lover,everything about data processing,analyzing,SQL,PLSQL,pig,hive,zookeeper,mahout,kafka,neo4j