A View on Oracle Locks

Reliability of a database has been determined by the degree to which it strives to achieve atomicity, consistency, isolation and durability, what we can call simply as ACID properties. Oracle is a robust and highly reliable database which has these ACID properties. Locks play a vital role in achieving couple of these properties. Oracle defined lock as Mode and type. Mode defines the way in which a session locks a row or set of rows in a table. Type can be defined as the transaction type which makes this lock happened.

Modes of Lock
There are six lock modes in oracle out of which the sixth lock is NULL lock.They are

Lock Mode	Abbreviation
Row Shared	SS
Row Exclusive	RX
Share	S
Share Row Exclusive	SRX/SSX
Exclusive	X

Lock types
There are three lock types like TM .TX and UL. They are classified according to the transaction which is locking the table. TX denotes a transactional lock which occurs when any transaction changes data from a table. TM is a DML lock which occurs when ever an object is changed. UL is a user defined lock.

Locks are strong contender which affects the performance of database, which cab be diagnosed easily.

Data Dictionary Views
Oracle uses any one of the above locks modes according to the transaction type to achieve the Isolation of ACID. Oracle provides couple of data dictionary views to identify almost all details about locks like who is locking an object, which sessions are waiting for the same object and session details. File and the block where the lock has been made can also be easily identified from the data dictionary views.
• V$lock
• V$locked_object

We will take an example where we will create a lock and we will try to gather the lock details

ses1>create table test_lock(
2 id number,
3 name varchar(200));
Table created.

ses1>insert into test_lock values(1,'apple');
1 row created.
ses1>insert into test_lock values(2,'orange');
1 row created.
ses1>insert into test_lock values(3,'banana');
1 row created.
ses1>insert into test_lock values(4,'mango');
1 row created.
ses1>commit;
Commit complete.

ses1>column id format 99
ses1>column name format a10
ses1>select * from test_lock for update;

ID NAME
--- ----------
1 apple
2 orange
3 banana
4 mango

Session 2 created for updating a single row from the same table

SQL> set sqlprompt ses2>
ses2>
ses2>column id format 99
ses2>column name format a10
ses2>select * from test_lock where id=1 for update;

ID NAME
--- ----------
1 apple
Session 3 for studying the locks due to session 1 and 2

SQL> set sqlprompt ses3>
ses3>column addr format a18
ses3>column kaddr format a18
ses3>column sid format 999
ses3>column ty format a3
ses3>column id1 format 99999999
ses3>column id2 format 99999999
ses3>column lmode format 9999
ses3>column request format 999
ses3>column request format 999
ses3>column block format 9
ses3>set linesize 300
ses3>Select *from v$lock where type in ('TX','TM');

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK------------------ ------------------ ---- -- --------- --------- ----- ------- ---------- -----070000000BBB72B0 070000000BBB7428 48 TX 393237 8560 6 0 2478 1070000000BB6C7E8 070000000BB6C810 48 TM 45405 0 3 0 2478 0070000000BB6C8A8 070000000BB6C8D0 54 TM 45405 0 3 0 2478 0070000000B3C3D90 070000000B3C3DB0 54 TX 393237 8560 0 6 2478 0070000000BBADD18 070000000BBADE90 75 TX 262182 9758 6 0 7460 0070000000BB6C728 070000000BB6C750 75 TM 45400 0 3 0 7460 0070000000BBE3FB0 070000000BBE4128 77 TX 589867 7411 6 0 5773 0070000000BB6C668 070000000BB6C690 77 TM 43850 0 3 0 5773 0

8 rows selected.

LMODE and REQUEST

The LMODE and REQUEST columns both use the same numbering for lock modes, in order of increasing exclusivity: from 0 for no lock, to 6 for exclusive lock. A session must obtain an exclusive TX lock in order to change data; LMODE will be 6. If it can't obtain an exclusive lock because some of the rows it wants to change are locked by another session, then it will request a TX in exclusive mode; LMODE will be 0 since it does not have the lock, and REQUEST will be 6.
You can see this interaction in the rows we selected earlier from v$lock:

SID
This is session id you can join with the V$session and v$sql for finding the session details and sql which is causing the lock.
ses3>

select q.SQL_TEXT
from v$session s,
v$sql q
where s.sid= 54 and
s.SQL_ADDRESS=q.ADDRESS;

SQL_TEXT
------------------------
select * from test_lock where id=1 for update

CTIME
Time since current mode was acquired
ID1 and ID2
The ID1 and ID2 columns point to the rollback segment and transaction table entries for that transaction.
For DML lock the column ID1 shows the object being modified

ses3>select object_name from dba_objects do where do.object_id=45405;

OBJECT_NAME
---------------------------
TEST_LOCK

Block
This column shows whether this lock is blocking some other session

Identifying Locked Table and its Row
We can identify the sid from v$lock table and the corresponding sid in v$session table will gives us the
• object ID,
• the relative file number,
• the block in the datafile,
• the row in the block that the session is waiting on

We can calculate the extended rowid from the above four parameters using the procedure rowid_create in DBMS_ROWID package.

ses3>column object_name format a15
ses3>column row_id format a60
ses3>column query format a 60
ses3>column query format a60
ses3>select do.object_name,
        dbms_rowid.rowid_create(1,
        ROW_WAIT_OBJ#,
        ROW_WAIT_FILE#,
        ROW_WAIT_BLOCK#,
        ROW_WAIT_ROW#) row_id,
        'select * from ' do.object_name ' where rowid = '''
        dbms_rowid.rowid_create(1,
        ROW_WAIT_OBJ#,
        ROW_WAIT_FILE#,
        ROW_WAIT_BLOCK#,
        ROW_WAIT_ROW#) '''' query
from v$session s,
       dba_objects do
where s.sid = 54 and
         s.ROW_WAIT_OBJ# = do.object_id;

OBJECT_NAME              ROW_ID                                                          QUERY
--------------- ------------------------- --------------------------------------------------------
TEST_LOCK AAALFdAALAAAlQ1AAA    select * from TEST_LOCK where rowid = 'AAALFdAALAAAlQ1AAA'

The third column of this query will give the query which will show the column which got locked by the session

ses3>select * from TEST_LOCK where rowid = 'AAALFdAALAAAlQ1AAA';
ID NAME
--- -------
1 apple

So this way we can find out the

• Lock
• Type of lock and locking user
• object which is locked
• SQL which is locking
• Row which is locked

It would be good if some one reading this blog add some points I missed out :)

I am happy to share my step by step methods in diagnosing oracle database performance.