Sunday, August 21, 2011

ORACLE LOCKS

Oracle Database always performs necessary locking to ensure data concurrency,
integrity, and statement-level read consistency. You can override these default locking
mechanisms. For example, you might want to override the default locking of Oracle
Database if:

■ You want transaction-level read consistency or "repeatable reads"—where
transactions query a consistent set of data for the duration of the transaction,
knowing that the data has not been changed by any other transactions. This level
of consistency can be achieved by using explicit locking, read-only transactions,
serializable transactions, or overriding default locking for the system.

■ A transaction requires exclusive access to a resource. To proceed with its
statements, the transaction with exclusive access to a resource does not have to
wait for other transactions to complete.

ROW-LEVEL AND TABLE-LEVEL LOCKS:

DML locks are locks which are acquired automatically by Oracle to protect data in tables and indexes Whenever you issue a DML statement to modify data i.e SELECT..FOR UPDATE,INSERT, UPDATE, MERGE, and DELETE statments.DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations.
DML statments automatically acquire lock on two levels :

i) Row-Level Locks (TX)
ii)Table Level Locks(TM)

These DML locks are always and only (exclusive) at row level but it can be shared as well as exclusive at table level.

Here TX specify lock at row level while TM is at table level.

i) Row Level Locks (TX)
Row-level locks are primarily used to prevent two transactions from modifying the same row. When a transaction needs to modify a row, a row lock is acquired.Any INSERT, DELETE, UPDATE, or SELECT FOR UPDATE statements will automatically issue an exclusive lock on the rows affected by the transaction. This exclusive lock at row means that other transactions can’t modify the affected rows until the original transaction commits or rolls back, thereby releasing the exclusive locks.

ii) Table Level Lock (TM)
Whenever you acquire any row level lock there is ultimately a table level is also acquired to prvent others session to alter, drop this table whose rows are being modified.TM Per table locks are acquired during the execution of a transaction when referencing a table with a DML statement so that the object is not dropped or altered during the execution of the transaction.



Choosing a Locking Strategy :

Different Locking modes:

The v$lock view has an ‘LMODE’ column which indicates the Lock mode in which the session holds the lock. The value of LMODE can vary from 0 to 6.The higher the value of LMODE, stronger is the lock. I am describing below each of these locking modes.

• 0 - none
• 1 - null (NULL)
• 2 - row-S (RS)
• 3 - row-X (RX)
• 4 - share (S)
• 5 - S/Row-X (SRX)
• 6 - exclusive (X)

ROW SHARE (RS)
ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access.

ROW EXCLUSIVE (RX)
ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.
The following statements lock a table in ROW-EXCLUSIVE mode.
INSERT INTO table ... ;

UPDATE table ... ; (see example 1).

DELETE FROM table ... ;

LOCK TABLE table IN ROW EXCLUSIVE MODE; (For notes on usage of LOCK TABLE see Example 2).

SHARE (S)
SHARE permits concurrent queries but prohibits updates to the locked table.

SHARE ROW EXCLUSIVE (SRX)
SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows.

EXCLUSIVE (E)
EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.

I am giving below the compatibility matrix of the different locking modes:








compatible RS RX S SRXX
RS yesyesyesyesno
RXyes yes no nono
S yes no yes no no
SRX yes no no no no
X no no no no no


Lets try out an example where we will update the salary column of the EMP table for a particular employee and use the V$LOCK view to check the corresponding locks it acquires:

Example 1.1:


SESSION 1:

SQL> update emp set salary=salary+100 where empno=1;

1 row updated.

SQL> select a.sid,a.type,a.lmode,a.request from v$lock a,v$session b where a.sid=b.sid
and b.username='SCOTT';

SID TY LMODE REQUEST
---------- -- ---------- ----------
162 TX 6 0
162 TM 3 0

So It acquires an EXCLUSIVE row-level(TX) lock to the row it updates and a ROW-EXCLUSIVE table-level(TM) lock on the EMP table.

The session has not committed yet.

Session 2:

Now lets say from another session, the user wants to drop the table EMP which would require an EXCLUSIVE LOCK on the EMP table.

SQL> DROP TABLE EMP;
DROP TABLE EMP
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified.

It throws an error because the an EXCLUSIVE lock is denied on EMP as another session has already acquired a ROW-EXCLUSIVE(RX) lock on EMP and RX is not compatible with EXCLUSIVE(E) mode(see the compatibility matrix).

To release the locks, session 1 simply needs to commit or rollback.


EXAMPLE 1.2:


Now the session 1 has not yet committed/Rolled back and is still holding the locks.

Session 1:

SQL> select a.sid,a.type,a.lmode,a.request from v$lock a,v$session b where a.sid=b.sid
and b.username='SCOTT';

SID TY LMODE REQUEST
---------- -- ---------- ----------
162 TX 6 0
162 TM 3 0

Session 2:

The user wants to update the salary of the particular employee with employee id 2.

SQL> update emp set salary=salary+100 where empno=2;

1 row updated.
The transaction is allowed.
Both the session have not yet committed/Rolled back. Now lets find out the locks they are holding.

SQL> select a.sid,a.type,a.lmode,a.request from v$lock a,v$session b where a.sid=b.sid
2 and b.username='SCOTT';

SID TY LMODE REQUEST
---------- -- ---------- ----------
149 TX 6 0
149 TM 3 0
162 TX 6 0
162 TM 3 0

As we can see,So both the sessions are holding RX locks on the table level
and EXCLUSIVE locks at row level (but at different rows). As RX is compatible
with RX and their EXCLUSIVE locks are at different rows , so both their TX and
TM locks are compatible and hence the transactions are allowed.


Lock table Statement :

A LOCK TABLE statement manually overrides default locking.
When a LOCK TABLE statement is issued on a view, the underlying base tables are
locked. The following statement acquires exclusive table locks for the
EMP table on behalf of the containing transaction:

Example 2.1: 


SQL>LOCK TABLE EMP IN EXCLUSIVE MODE;

You can specify several tables or views to lock in the same mode; however, only a
single lock mode can be specified for each LOCK TABLE statement.

Lets see what the V$LOCK view has to say about the lock held by the session

SQL>select a.sid,a.type,a.lmode,a.request from v$lock a,v$session b where a.sid=b.sid and
b.username='SCOTT'


SID TY LMODE REQUEST
---------- -- ---------- ----------
162 TM 6 0

LMODE 6 is EXCLUSIVE lock.


EXAMPLE 2.2:


Lets give another example where the table is locked is SHARE ROW EXCLUSIVE (SRX) mode.

SQL> LOCK TABLE EMP IN SHARE ROW EXCLUSIVE MODE;

Table(s) Locked.

SQL> select a.sid,a.type,a.lmode,a.request from v$lock a,v$session b where a.sid=b.sid and
2 b.username='SCOTT';

SID TY LMODE REQUEST
---------- -- ---------- ----------
162 TM 5 0

LMODE 5 is SRX lock mode.


NOWAIT CLAUSE:

You can also indicate if you do or do not want to wait to acquire the lock. If you
specify the NOWAIT option, then you only acquire the table lock if it is immediately
available. Otherwise an error is returned to notify that the lock is not available at this
time. In this case, you can attempt to lock the resource at a later time. If NOWAIT is
omitted, then the transaction does not proceed until the requested table lock is
acquired.



Example 3:


Session 1:

SQL> Lock table EMP IN SHARE ROW EXCLUSIVE MODE;

Session 2:

SQL> LOCK TABLE EMP IN SHARE MODE NOWAIT;
LOCK TABLE EMP IN SHARE MODE NOWAIT
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Note: ALTER TABLE and DROP TABLE commands have implicitly NOWAIT keyword specified (see example 1.1).