Oracle Locking

Lock

• Multiuser databases use some form of data locking to solve the problems associated with data concurrency, consistency, and integrity. 
• Prevent destructive interaction between transactions accessing the same resource, Data corruption, Inconsistent Read etc. 
• All locks acquired by statements are held for the duration of the transaction.
• Only transactions not waiting for the previously locked resources can acquire locks on now available resources.

Oracle Lock Modes

Exclusive Lock

• Prevents the associates resource from being shared. 
• This lock mode is obtained to modify data.
• The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released. 
• Other Transactions can read the data.
  

Shared Lock

• Allows the associated resource to be shared for read access by multiple transactions. 
• Multiple transactions can acquire share locks on the same resource.
• Shared Lock prevents write access by a transaction(who needs an exclusive lock). 

Oracle Lock Types

Locks are used for shared resources. Resources in general are of two types:

1. User objects, such as tables and rows (structures and data)
2. System objects not visible to users, such as shared data structures in the memory and data dictionary rows

Following are Oracle Lock Types:

• DML locks (data locks) - Applicable on resource type 1 described above
• DDL locks (dictionary locks) 
• Oracle Internal Locks/Latches
• Oracle Distributed Locks
• Oracle Parallell Cache Management Locks

DML Locks (Data Locks)

• DML locks protect data. For example, table locks lock entire tables, row locks lock selected rows. 
• DML operations can acquire data locks at two different levels: for specific rows and for entire tables.

Row Level Locks

• All DML locks Oracle acquires automatically are row-level locks. 
• Both read committed and serializable transactions use row-level locking, and both will wait if they try to change a row updated by an uncommitted concurrent transaction.
• There is no limit to the number of row locks held by a transaction. 
• Readers of data do not wait for writers of the same data rows.
• Row locking provides the lowest level of locking possible provides the best possible transaction concurrency.
• A modified row is always locked exclusively so that other users cannot modify the row until the transaction holding the lock is committed or rolled back.
• If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.
• A transaction gets an exclusive DML lock for each row modified by any of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause. 

Table Level Locks 

• A transaction acquires a table lock for DML statements such as INSERT/UPDATE/DELETE, SELECT with the FOR UPDATE, and LOCK TABLE.  Reasons are to reserve DML access to the table on behalf of a transaction and prevent DDL operations.
• Table locks prevent the an exclusive DDL lock on the same table which prevents DDL operations.  Example, a table cannot be altered or dropped if any uncommitted transaction holds a table lock for it.

* Remember: INSERT, UPDATE, DELETE first scan and query the rows that they affect.

DEADLOCKS

• Oracle automatically detects deadlock situations and resolves them by rolling back one of the statements involved in the deadlock.  This releases one set of the conflicting row locks. A corresponding message also is returned to the transaction that undergoes the rollback.
• Deadlocks often occur when transactions override Oracle default locking. Oracle itself does no lock escalation and does not use read locks for queries and does not use page-level locking, deadlocks rarely occur in Oracle.
• Deadlocks can usually be avoided if transactions accessing the same tables lock those tables in the same order, either through implicit or explicit locks and when a sequence of locks for one transaction are required, you should consider acquiring the most exclusive (least compatible) lock first
• Always close explicit cursors when finished to free locks.

Queries to identify the Locks and Kill Sessions

    SELECT  l.inst_id, 
    SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER,  
    SUBSTR(L.SESSION_ID,1,3) SID, 
    S.serial#, 
    SUBSTR(O.OWNER||'.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID, 
    DECODE(L.LOCKED_MODE,   0,'NONE', 
    1,'NULL', 
    2,'ROW SHARE', 
    3,'ROW EXCLUSIVE', 
    4,'SHARE', 
    5,'SHARE ROW EXCLUSIVE', 
    6,'EXCLUSIVE', 
    NULL) LOCK_MODE 
    FROM    sys.GV_$LOCKED_OBJECT L 
    , DBA_OBJECTS O 
    , sys.GV_$SESSION S 
    , sys.GV_$PROCESS P 
    WHERE     L.OBJECT_ID = O.OBJECT_ID 
      and     l.inst_id = s.inst_id 
      AND     L.SESSION_ID = S.SID 
      and     s.inst_id = p.inst_id 
      AND     S.PADDR = P.ADDR(+) 
    order by l.inst_id  ;
----------------------------------------------------   
   
   
    select lobj.* from v$locked_object lobj, all_objects obj where obj.object_id = lobj.object_id and obj.object_name = 'EMPLOYEES' 

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

select * from v$session where sid = 568
serial num = 1190

alter system
kill session '568,1190'

Database Transaction - Isolation, Consistency, Concurrency, Locks, and more

Reference

http://docs.oracle.com/cd/B12037_01/server.101/b10743/consist.htm