I am an IT professional with 10 years of professional experience,I have good proficiency on Oracle technologies, and at last 2 years of my career to study Real Application Clusters,data guard and participate actively on Oracle community ,If you want to hire me on Contract or to quote on project basis contact me at khurrampc@hotmail.com.

Friday, March 6, 2009

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.

I will post with others mode in next blog

6 comments:

nayyares said...

nice post!

i really enjoyed it.

Thanks

Khurram Siddiqui said...

thanks for appreciation @nayyers

abc said...

good job

Aree said...

It is very good. I learned on it.
Thanks and write more on other topics as well.

daspeac said...

I have heard about another way of fix sql .mdf. Besides, you can visit my blogs at: http://daspeac.livejournal.com/ or http://daspeac.blogspot.com/ where I’m trying to share my experience with regard to data corruption issues.

Anonymous said...

thanks
very informative:)

Followers

About Me

My photo
Melbourne, Victoria, Australia