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
