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