Oracle Lock

Oracle Locks

User must request a resource (i.e row,table,index etc) before using it and must release that resource after using it.
Without following above approach there would be collision among user requests which result data to be in inconsistent.To avoid data inconsistency a lock mechanisim adopted within different database,Every database has its own locking mechanism.
Oracle uses two modes in a multiuser database either its on table or row

Exclusive lock mode
Exclusive lock mode never allowed the associates resource from being shared.This lock mode is obtained to modify data.Only one exclusive lock can be placed on a resource (such as a row or a table).

i.e

From one session i acquire the exclusive lock on resource (emp table)

Session a

SQL> lock table emp in exclusive mode nowait

  2  /

Table(s) Locked.

Note : The optional keyword NOWAIT tells Oracle not to wait for a table if it has been locked by another user.

From another session i try to acquire the exclusive lock on same resource (emp table) as "session a" above already acquired.

Session b

SQL> lock table emp in exclusive mode nowait

  2  /

lock table emp in exclusive mode nowait

           *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

Even you cannot get shared lock over that table which has already exclusive mode lock

SQL> lock table emp in row share mode nowait

  2  /

lock table emp in row share mode nowait

           *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

Exclusive lock can be acquired at table level as well at row level.But oralce automatically acquire exclusive lock at row level which is the beauty of oracle to create data consistent as well maintain concurrency.

Share lock mode
Share lock mode allows the associated resource to be shared subject to cirumstances.

i.e

From one session i acquire the share lock on resource (emp table)

Session c

SQL> lock table emp in row share mode nowait

  2  /

Table(s) Locked.

From another session i try to acquire the same resource exclusive lock on same resoruces (emp table) which has already above taken by "session c".

Session d

SQL> lock table emp in row share mode nowait

  2  /

Table(s) Locked.

Share locks dont allow to acquire exclusive lock over the associates resource.

i.e

From one session i acquire the share lock on resource (emp table)

Session e

SQL> lock table emp in row share mode nowait

  2  /

Table(s) Locked.

From another session i try to acquire the same exclusive lock on same resoruces (emp table) which has already taken by above "session e".

Session f

SQL> lock table emp in exclusive mode nowait

  2  /

lock table emp in exclusive mode nowait

*

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

Now you are aware about locking mode.

Locks Types

DML 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.

Here is an exceprt from SQL to verify that DML acquires lock at both row and table level

Session 1

SQL> update emp

  2     set sal=sal+1

  3   where empno=7566

  4  /

1 row updated.

Session 2

SQL> update emp

  2     set sal=sal+1

  3   where empno=7566

  4  /

     .

     .

hanged

Session 3

SQL> drop table emp

  2  /

drop table emp

           *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified

Above excerpt from SQL gives now a little fog but uptil now you might be clear that DML acquires lock at both row and table level ,to be more clear about this statment that "DML acquires lock at both row and table level" we need to explore oracle dynamic view v$lock.

SQL> conn sys/sys@prod as sysdba

Connected.

SQL> desc v$lock

 Name       Null?    Type

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

 ADDR                RAW(4)

 KADDR               RAW(4)

 SID                 NUMBER

 TYPE                VARCHAR2(2)

 ID1                 NUMBER

 ID2                 NUMBER

 LMODE               NUMBER

 REQUEST             NUMBER

 CTIME               NUMBER

 BLOCK               NUMBER

From this view you just need to understand the columns TYPE (Lock type),LMODE (Lock mode) and REQUEST

As we have already seen that there are two types of DML locks

Row Level (TX)
Table Level (TM)
Type will show you the locks type either its (TX or TM)

Lock Mode (LMODE)

Lock mode tell you strength of lock ,oracle placed numbered for each lock mode (0 to 6) according to its strength.The higher the number the more its stronger.Remember follows lock mode can be at row as well table level.
6 - Exclusive (X)
5 - Shared Row Exclusive (SRX)
4 - Share (S)
3 - Row Exclusive (RX)
2 - Row Share (RS)
1 - null (NULL)
0 - none
Lets explore only 6 and 3 right now for eliminating little fog that "DML acquires lock at both row and table level",After that we will explore others in next blog.

6 - Exclusive (X) The most restrictive locking mode; permits queries on the locked table but prohibits any DML by any other users. Above statment says that you cannot acquire that lock on that same resource (Table,Rows) which is already acquired by another session. Like if you have table level exclusive lock then no other session can get any lock on that table,same if you have exclusive lock on rows then no other session can acquire lock on that rows ,yes you can get any other lock other then that rows which have already exclusive lock. This mode is automatically obtained when using the DROP TABLE statement.

3 - Row Exclusive (RX) A row exclusive table lock generally indicates that the transaction holding the lock has made one or more updates to rows in the table. A row exclusive table lock is acquired automatically for a table modified by the following types of statements.

INSERT INTO table ... ;

UPDATE table ... ;

DELETE FROM table ... ;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

Permitted Operations : A row exclusive table lock held by a transaction allows other transactions to query, insert, update,delete, or lock rows concurrently in the same table. Therefore, row exclusive table locks allow multiple transactions to obtain simultaneous row exclusive and row share table locks for the same table. Prohibited Operations : A row exclusive table lock held by a transaction prevents other transactions from manually locking the table for exclusive reading or writing. Therefore, other transactions cannot concurrently lock the table using the following statements:

LOCK TABLE table IN SHARE MODE;

LOCK TABLE table IN SHARE EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

Here is an excerpt from SQL to see both lock mode

SQL> update emp   

  2     set sal=sal+1   

  3   where empno=7566   

  4  /            

           

           

1 row updated.

SQL> show user

USER is "SYS"

SQL> select username

  2        ,v$lock.sid

  3        ,v$lock.type "lock type"

  4        ,lmode       "lock mode"

  5    from v$lock,v$session

  6   where v$lock.sid = v$session.sid

  7     and v$session.username='SCOTT'

  8   order by v$lock.sid

  9  /

USERNAME                              SID lo  lock mode

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

SCOTT                                 161 TX          6

SCOTT                                 161 TM          3

As you can see DML locks within session for an update statment acquired TX (Row Level) lock with lock mode 6 which is an exclusive (the most restrictive/aggresive lock).You are also seeing that this DML lock also acquire TM (Table Level) with lock mode 3 which is row exclusive this lock will prevent other session to drop that table.