Oracle row level locking

We all know that Oracle provides row level lock for database transaction. I’d like to blog how it is done in details.

Let us start with creating a test table:

SQL> create table test (name varchar2(100)) tablespace users;

Table created.

SQL> insert into test values ('Mike');

1 row created.

SQL> insert into test values ('Steve');

1 row created.

SQL> insert into test values ('Tom');

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid, name from test;

ROWID NAME

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

AAANzsAAEAAAAPHAAA Mike

AAANzsAAEAAAAPHAAB Steve

AAANzsAAEAAAAPHAAC Tom

SQL> select dbms_rowid.rowid_relative_fno('AAANzsAAEAAAAPHAAA') file_no from dual;

FILE_NO

----------

4

SQL> select dbms_rowid.rowid_block_number('AAANzsAAEAAAAPHAAA') block_no from dual;

BLOCK_NO

----------

967

SQL> alter system dump datafile 4 block 967;

System altered.

Now let us do some updates on the test table

SQL> update test set name = 'Ed' where name = 'Mike';

1 row updated.

The update QSL should start a transaction and place row level lock on the updated row. To see the locks, we query V$LOCK and V$TRANSACTION:

SQL> select ADDR, sid, type, id1, id2, lmode from v$lock where sid = 149;

ADDR SID TY ID1 ID2 LMODE

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

0000000087A8D950 149 TM 56556 0 3

0000000087AAD388 149 TX 655364 1172 6

SQL> select addr, xidusn, xidslot, xidsqn from v$transaction;

ADDR XIDUSN XIDSLOT XIDSQN

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

0000000087AAD388 10 4 1172

From V$LOCK, we get table level DML lock TM with lock mode 3, which is row exclusive lock. ID1 56556 is the object id of the locked table, which is “TEST”. Other lock related to the update session is transaction lock TX with ID1 655364, which is decimal value of undo segment number and slot number, and ID2 1172, which is transaction sequence number. Convert 655364 to hex is 0xA0004 and it means undo segment 10 (0xA = 10), slot 4.

From V$TRANSACTION with same ADDR (0000000087AAD388), we got XIDUSN (Undo Segment Number) 10, XIDSLOT (Undo segment slot) 4, and XIDSQN (transaction sequence number) 1172. All matches well with V$LOCK.

Now we do another update and exam the V$LOCK and V$TRQANSACTION again.

SQL> update test set name = 'David' where name = 'Steve';

1 row updated.

SQL> select ADDR, sid, type, id1, id2, lmode from v$lock where sid = 149;

ADDR SID TY ID1 ID2 LMODE

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

0000000087A8D950 149 TM 56556 0 3

0000000087AAD388 149 TX 655364 1172 6

SQL> select addr, xidusn, xidslot, xidsqn from v$transaction;

ADDR XIDUSN XIDSLOT XIDSQN

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

0000000087AAD388 10 4 1172

Well, we still find only one TM table lock with row exclusive lock mode for this session even we have two row level locks, one for each row. In fact, TM lock with row exclusive lock mode means that there is a shared lock on the table and some rows in the table have exclusive lock. Oracle does not provide a system interface to view the row level locking. To view the Oracle row level locking, we have to go deep into Oracle data block.

Do the data block dump again and we find the interesting output:

0067F39D0 00C07F43 0000000B 00000000 022CC506 [C.............,.]

0067F39E0 64450201 0301002C 2C6D6F54 44050102 [..Ed,...Tom,...D]

0067F39F0 64697661 0401002C 656B694D C6900601 [avid,...Mike....]

……

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.02f.00000493 0x0080033d.0396.36 C--- 0 scn 0x0000.002dba46

0x02 0x000a.004.00000494 0x00800342.0396.16 ---- 2 fsc 0x0000.00000000

-----

tab 0, row 0, @0x1f7a

tl: 6 fb: --H-FL-- lb: 0x2 cc: 1

col 0: [ 2] 45 64

tab 0, row 1, @0x1f87

tl: 9 fb: --H-FL-- lb: 0x2 cc: 1

col 0: [ 5] 44 61 76 69 64

tab 0, row 2, @0x1f80

tl: 7 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 3] 54 6f 6d

The second section is Interested Transaction List (Itl) and has two entries, 01 and 02. The Flag for it is ---- and that means a active transaction. Lck is 2, means two row level locking in this block. The Xid (Transaction ID) is 0x000a.004.00000494 and it means undo segment A (Undo segment 10 in hex), slot 4 and sequence 0x494, which is 1172 in decimal. So Itl entry 2 is the transaction lock for the two updates. In the third section for detailed information on each row, you can see “lb: 0x2” for row 0 and row 1. “lb” here stands for “lock byte” and the value 0x2 is for Itl entry 2. That is our row level locking. It is actually under TX lock, not TM lock.

Oracle actually stores the row level locking at row heading highlighted in yellow in section 1 of the dump output.

Row 0 heading is 022C 0201, the block dump is from Linux system with small endian format. To put the heading in right sequence, it is 2C 02 01 02 and the “02” in green highlight is the row level locking byte. Similarly, the row 1 heading is 2C 02 01 05. The heading for the third row is 2C 00 01 03, which is highlighted in blue in the dump section 1 and the lock byte is “00”, which means not locking.

So far so good. We do a commit and another data block dump. I expect the lock bytes for row 0 and row one to be changed to 0.

SQL> commit;

Commit complete.

And of course the TM lock and TX lock for this session are release. The data block dump after commit is following:

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.02f.00000493 0x0080033d.0396.36 C--- 0 scn 0x0000.002dba46

0x02 0x000a.004.00000494 0x00800342.0396.16 --U- 2 fsc 0x0000.002dc9b8

……

tab 0, row 0, @0x1f7a

tl: 6 fb: --H-FL-- lb: 0x2 cc: 1

col 0: [ 2] 45 64

tab 0, row 1, @0x1f87

tl: 9 fb: --H-FL-- lb: 0x2 cc: 1

col 0: [ 5] 44 61 76 69 64

tab 0, row 2, @0x1f80

tl: 7 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 3] 54 6f 6d

Well, the lock byte for row 0 and row 1 do not change at all after commit. The only thing changed for this data block is the flag for Itl entry 2, besides block scn and checksum. Flag “--U-“ marks the transaction inactive and consider all row level locks under this Itl is release. This is why Oracle can do commit operation very fast. The row lock cleanup is piggy packed with next DML for the block and the Itl flag changes to “C---“ and lock byte changes to 0 after the clean up.

SQL> update test set name = 'Adam' where name = 'Ed';

1 row updated.

SQL> rollback;

Rollback complete.

SQL> alter system dump datafile 4 block 967;

System altered.

And the dump file is:

Itl Xid Uba Flag Lck Scn/Fsc

0x01 0x000a.02f.00000493 0x0080033d.0396.36 C--- 0 scn 0x0000.002dba46

0x02 0x000a.004.00000494 0x00800342.0396.16 C--- 0 scn 0x0000.002dc9b8

……

tab 0, row 0, @0x1f6c

tl: 6 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 2] 45 64

tab 0, row 1, @0x1f87

tl: 9 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 5] 44 61 76 69 64

tab 0, row 2, @0x1f80

tl: 7 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 3] 54 6f 6d

To understand Oracle row level locking implementation is not just for fun, but has practical use as well. The row locking implementation for Oracle is at row heading level and do not have system wide structure to track them, so it is very scalable and make RAC row level locking relatively simple. Oracle do not have latch directly to protect row level locking, so if a process finds the row is locked by another session, it escalate row level locking to transaction lock and wait the transaction to be done. For rare case, such implementation blocks a session unnecessary.

Here is an example that was demonstrated by Scott Martin at http://www.tlingua.com/new/articles/Chapter1.html.

Let us start with session1:

SQL> update test set name = 'Adam' where name = 'Ed';

1 row updated.

SQL> savepoint a;

Savepoint created.

SQL> update test set name = 'Mike' where name = 'David';

1 row updated.

Now open session 2 and update row 2:

SQL> update test set name = 'Dave' where name = 'David';

Session is blocked by session 1 and waits the row lock.

From session 1, do rollback of update the row 2.

SQL> rollback to savepoint a;

Rollback complete.

However, session 1 is still waiting the row locking. Do a block dump and lock byte for row 2 is 0x0, so no row locking on the row session 2 is updating. In fact, session 2 is not waiting on row locking any more, it is waiting on session 1 transaction.

Block dump is

tab 0, row 1, @0x1f53

tl: 9 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 5] 44 61 76 69 64

Let us start session 3 to update the same row:

session 3

SQL> update test set name = 'Magic' where name = 'David';

1 row updated.

The row is updated and session 2 is still blocked. Rollback session 1 and session 2 is waiting on session 3. Finally, the session 3 is rollback and session2’s update is completed.