Resolving lock conflict with SQL in Oracle (Killing locked session)

In this topic, we will demonstrate how row is locked when one user is updating and at the same time other user fire update sql for the same row.

For this, we will

• create two application users user1 and user2
• create role hr_employee to create session and select, update on hr.employees table.
• grant hr_employee to user1 and user2
• user1 will fire update sql for a particular row and user1 will not commit. (i.e., row will be locked)
• At the same time, user2 will fire update sql for the same row. (user2 will not be prompted any update success message)
• dba will identify the locking conflict and resolve the locking conflict by killing user1 session

#Drop the users and role if they already exist:
drop user user1;
drop user user2;
drop role hr_employee;

#Create a role with the permissions needed for this application
create role hr_employee;
grant create session to hr_employee;
grant select, update on hr.employees to hr_employee;

#Grant them the application role.
grant hr_employee to user1 identified by oracle_4U;
grant hr_employee to user2 identified by oracle_4U;

#Now connect in as user1 and update record with employee_id=110.
connect user1/oracle_4U
show user
update hr.employees set phone_number='650.555.1212' where employee_id = 110;

Please note that user1 has not committed his change yet.

#Open next terminal and connect as user2
connect user2/oracle_4U
update hr.employees set phone_number='650.555.1212' where employee_id = 110;

Please not that user2 has not been prompted any update success message. Just the cursor is blinking.

#Open next terminal and login as sys. We are going to see and kill the locked session
sqlplus /nolog
connect sys as sysdba

select SID, SERIAL#, USERNAME from V$SESSION where SID in (select BLOCKING_SESSION from V$SESSION);

alter system kill session '25,441' immediate;

Now, In the terminal where user2 was trying to update the record, we can see the message '1 row updated'.

Here, the changes that user1 made changes has been rolled back.

Now, user2 can commit its changes.

Thank you. :)