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

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

 Database Transaction 

•  Set of one or more statements that are executed as a unit.
• Logical, atomic unit of work that contains one or more SQL statements.

When a connection is created, it is in auto-commit mode, individual SQL statements are treated as a transaction.

The default is for a SQL statement to be committed when it is completed, not when it is executed. A statement is completed when all of its result sets and update counts have been retrieved.

Transactions can help to preserve the integrity of the data in a table, providing some level of protection against conflicts that arise when two users access data at the same time.

Acid Properties of Transaction

ATOMICITY

All tasks of a transaction are performed or none of them are.

CONSISTENCY

The transaction takes the database from one consistent state to another consistent state. 

ISOLATION

The effect of a transaction is not visible to other transactions until the transaction is committed.

DURABILITY

Changes made by committed transactions are permanent.

Data Consistency and Data Concurrency

• Multiple transactions executing at the same time need to produce meaningful and consistent results, that’s why Data Consistency and Data Concurrency are important.

Data concurrency 

• Many users should be able to access data at the same time.

Data consistency

• Each user sees a consistent view of the data, including visible changes made by the user's own transactions and transactions of other users.

The ANSI/ISO standard SQL 92 speaks of three different phenomena when a transaction reads data that another transaction might have changed:

• Dirty read
• Non-repeatable read
• Phantom read

DIRTY READ  

Occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.

 

NON-REPEATABLE READ 

Occurs when transaction A retrieves a row, transaction B subsequently updates the row, and transaction A later retrieves the same row again. Transaction A retrieves the same row twice but sees different data.

PHANTOM READ  

Occurs when transaction A retrieves a set of rows satisfying a given condition, transaction B subsequently inserts or updates a row such that the row now meets the condition in transaction A, and transaction A later repeats the conditional retrieval. Transaction A now sees an additional row. This row is referred to as a phantom.

 

Isolation Levels

The ANSI/ISO SQL standard (SQL92) defines four levels of transaction isolation with differing degrees of impact on transaction processing throughput.These isolation levels are defined in terms of three phenomena discussed above.

• Read Uncommitted
• Read Committed
• Repeatable Read
• Serializable

Read Uncommitted 

Each query can see only data last updated before the query, uncommitted data changes are also visible. 

Read Committed 

Each query can see only data committed before the query, not the transaction, began. 

Repeatable Read 

Retains locks on every row it touches until the end of the transaction. Even the rows that are not part of the query results are locked. Phantom Read can come because there is no range lock on table (Only insert with values that satisfy the criteria can introduce Phantom Rows).

Serializable

Serializable is similar to Repeatable Read but it also acquires a key range lock which prevents the insertion of any new rows anywhere within the range.

Please note Seriaizable are not usable in Distributed Transactions.

The default isolation level of different DBMS varies quite widely. Most databases that feature transactions allow the user to set any isolation level. Some DBMS's also require additional syntax when performing a SELECT statement to acquire locks (e.g. SELECT ... FOR UPDATE to acquire exclusive write locks on accessed rows).

According to SQL 92 (but not to Oracle), a transaction is always in exactly one of these isolation levels. Further: the isolation level cannot change within a transaction.

The Oracle database uses the Read Committed and Serializable isolation levels in addition to a read-only mode. By default, Oracle uses the read committed isolation level for database transactions.

Read-Only transaction isolation level - Only data that was committed at the start of the transaction can be accessed by a query.  No modification to the data is allowed.

Difference between Read Committed Transactions and Serializable Transactions:

• Serializable transactions acquires a range lock so that no new rows can be inserted that satisfy the query criteria of Selected rows.
  
• if the blocking transaction commits and releases its locks, a read committed transaction proceeds with its intended update. A serializable transaction, however, fails with the error "Cannot serialize access", because the other transaction has committed a change that was made since the serializable transaction began.

Setting Oracle Isolation Level

Setting at Transaction Level:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET TRANSACTION ISOLATION LEVEL READ ONLY;

Setting at Session Level:

ALTER SESSION SET ISOLATION_LEVEL READ COMMITTED;
ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
ALTER SESSION SET ISOLATION_LEVEL READ ONLY;

Next Topic - Oracle Locking