8-28 to 8-31. Lock Conflicts Causes, Detection and Resolution

Causes

• Uncommited changes
  
• Long running transactions: Many applications use batch processing to perform bulk updates. Lock conflicts are common when transaction and batch processing are being performed simultaneously.
• Unnecessarily high lock levels: Some database still lock at page or table level. As a consequence, developers writing applications to be run among multiple databases use artificially high lock levels which make Oracle behave like those less capable DBMS.
  

Detection
Use Blocking Sessions page in Enterprise Manager to locate lock conflicts.
Alternatively, the Automatic Diagnostic Monitor (ADDM) also automatically detects lock conflicts and can advise you on inefficient locking trends.

Resolution
The best way: inform the user and let them complete the transaction.
In emergency: kill the session in session detail page (Enterprise Manager) or manually using SQL:

SQL> select sid, serial#, username from v$session where sid in (select blocking_session from v$session);

The above selects sessions where its SID matches those SIDs that are blocking other sessions.

SQL> alter system kill session 'sid,serial_number' immediate;