FAQ On Transaction Locks

Where I work, we are migrating an application from an old, proprietary hierarchal data base to Oracle. Naturally, the question of how locking differs between the two has come up. So, I would like to post here a little discussion of the minimum people need to know about Oracle. And, to keep matters simple, I am primarily going to discuss what Oracle calls a DML TX lock, or simply a TX lock. It is the famous Oracle row lock. Oracle has many other locks, but these are the locks which control the modification of data, and are what most people think of, when they think of "locks".

The first thing to understand about Oracle and locks, is that, Oracle does not have locks. This is wrong, of course; if nothing else, my opening paragraph contradicts it. However, for someone used to what other database systems call "locks", it is closer to the truth than saying that Oracle does things differently.

Such a bold statement demands some evidence. Here, then, is how Oracle handles data locking. At the top end of every data block there is an area known as the "Block Overhead." Part of this area is known as the Interested Transaction List. Every transaction which is modifying data in a block enters its transaction id in this list. Another part of the Block Overhead is known as the row directory. Every row which is modified has a pointer set to indicate which transaction has modified it. When another transaction comes along, it inspects the ITL and the row directory. If the row it wishes to modify is held by an active transaction, it waits. Otherwise, it enters its transaction id in the ITL and modifies the row. Simple, really.

Actually, this paragraph has probably raised more questions than it has answered. Rather than deal with them in an organized fashion, I will deal with them in an FAQ type of format.

1) So why do you say these are not "locks"?
It's because of what is missing from other database systems. There is no lock manager. It is not even possible to get a list of all locks currently held in the instance. Locks do not affect anyone who just wants to read the data. Waiters do not time out. Locks are never escalated. Very different.

2) This must be horribly inefficient.
Well, no. Consider the first modifier. Besides modifying data (which must be done in any event; this transaction is committed to modifying data), the transaction must inspect and modify two items in the data block. This data block is already in memory, or else the data could never get modified in the first place. So, there is very little additional overhead here.

Now consider the second modifier. Most of the time, it will not encounter a locked row (unless the application was written to have one small table which everyone constantly updates. In this case, every database system will have a problem.) If it does encounter a locked row, it will have done very little extra work to find out that it is locked. It will then wait for the lock to be released, which means that it is not finding any other locked rows.

3) And if it does?
It waits again. But the chances are that the commit or rollback which released the blocking row also released any future rows the second transaction intends to modify.

4) But it must take a lot of work to clean up all those block headers after a commit?
Remember what happens during a commit? A new scn is generated, and a commit record is written to disk, along with the contents of the redo buffers. No mention of cleaning up block headers.

5) So when do they get cleaned up?
The next request for the block (read or write) examines the block header. If there is an inactive transaction in the ITL, the block header gets cleaned up at this point.

6) So a block header could stay "dirty" for hours?
If not longer. And who cares? The only way anyone would know would be to read the block but then it would get cleaned out and the problem would go away.

7) Must be hard, having to read all those data blocks just to find out all the locked rows.
Which is why there is no table listing all locked rows.

8) I see there is a view named V$LOCK. What good is it.?
It has information about transactions which have locks. If you update a row, you will find an entry regarding your transaction in V$LOCK. If you update many rows in a block, you will still have just one row. If someone else comes along and tries to update one of your rows, that will create another row in V$LOCK.

9) I have a hanging update statement. If V$LOCK won't tell me if someone else has locked my row and is blocking me, what will?
The best way is to go to the V$SESSION table, and look at these columns for your session:

ROW_WAIT_OBJ#: If the session is not involved in a locking event, this column will have a value of '-1'. If it is, then it will contain the data object id of the blocking table, and the name (and owner) of the table can be found by querying DBA_OBJECTS (or ALL_OBJECTS, if you do not have permissions on DBA_OBJECTS. Unfortunately, the converse is not true: ROW_WAIT_OBJ# can differ from '-1' and the session not be blocked by a row lock.

ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#: Assuming that you have located the name of the table containing the locked row, you can then view the row with this query:

select * from name-of-table-containing-the-locked-row
where rowid = dbms_rowid.rowid_create(1, row_wait_obj#,
row_wait_file#, row_wait_block#, row_wait_row#)

This query may surprise those used to other databases. The row is locked; how can we select it? Remember, Oracle readers are never blocked by updaters.

If you are running Oracle 10g, you have an additional clue, as the V$SESSION view there includes the BLOCKING_SESSION column. If your session is blocked by a row lock, this column will contain the SID of the session which is blocking you.

10) So what good is V$LOCK?
V$LOCK is very good for identifying whether or not there are blocked sessions present. If you are a DBA with an interest in an instance, and little interest in a specific application, it can be a useful view. If you are interested in resolving a specific question about a specific session, V$SESSION is of more use.