Skip to Main Content
  • Questions
  • Does Oracle store transaction entries on a fixed size page?


Question and Answer

Tom Kyte

Thanks for the question, Duke.

Asked: March 17, 2009 - 5:23 pm UTC

Last updated: March 18, 2009 - 4:52 pm UTC


Viewed 1000+ times

You Asked

"If I had 10 transactions all wanting to update a row on the page but there was only enough free space on the page to hold 7 ITL slots then effectively I have a page (block) level lock because 3 transactions are waiting even if they want to update rows that are not being updated by anyone else. "

Plausible? Or -- as he says -- just highly unlikely.

and Tom said...

we call them ITL waits. Yes they can happen. The block would have to be so full as to not permit another 23 bytes to be allocated in the header (upto a maximum of 255 ITL slots)

However, lock escalation isn't the thing to poo-poo as he did.

There are two components to managing locks in DB2

a) there is the memory overhead
b) there is a list, a large, huge, big shared list of locks.

If you want to allocate 15,000,000 locks, you have to access this list (serially bear in mind) 15,000,000 times. When you commit - guess what else you have to do? You have to deallocate 15,000,000 locks. The "list of locks" doesn't scale, not because of MEMORY - but because of the fact you have to manage a ton of information - in a large shared data structure, which requires massive serialization (shared data structures do).


TX (Transaction) Locks

A TX lock is acquired when a transaction initiates its first change, and it is held until the transaction performs a COMMIT or ROLLBACK. It is used as a queuing mechanism so that other sessions can wait for the transaction to complete. Each and every row you modify or SELECT FOR UPDATE in a transaction will 'point' to an associated TX lock for that transaction. While this sounds expensive, it is not. To understand why this is, you need a conceptual understanding of where locks 'live' and how they are managed. In Oracle, locks are stored as an attribute of the data (see Chapter 10 for an overview of the Oracle block format). Oracle does not have a traditional lock manager that keeps a long list of every row that is locked in the system. Many other databases do it that way because, for them, locks are a scarce resource, the use of which needs to be monitored. The more locks are in use, the more these systems have to manage, so it is a concern in these systems if 'too many' locks are being used.

In a database with a traditional memory-based lock manager, the process of locking a row would resemble the following:

1. Find the address of the row you want to lock.
2. Get in line at the lock manager (which must be serialized, as it is a common in-memory structure).
3. Lock the list.
4. Search through the list to see if anyone else has locked this row.
5. Create a new entry in the list to establish the fact that you have locked the row.
6. Unlock the list.
Now that you have the row locked, you can modify it. Later, as you commit your changes you must continue the procedure as follows:
7. Get in line again.
8. Lock the list of locks.
9. Search through the list and release all of your locks.
10. Unlock the list.

As you can see, the more locks acquired, the more time spent on this operation, both before and after modifying the data. Oracle does not do it that way. Oracle┬┐s process looks like this:

1. Find the address of the row you want to lock.
2. Go to the row.
3. Lock the row (waiting for the transaction that has it locked to end if it is already locked, unless you are using the NOWAIT option).

That's it. Since the lock is stored as an attribute of the data, Oracle does not need a traditional lock manager. The transaction will simply go to the data and lock it (if it is not locked already). The interesting thing is that the data may appear locked when you get to it, even if it is not. When you lock rows of data in Oracle, the row points to a copy of the transaction ID that is stored with the block containing the data, and when the lock is released that transaction ID is left behind. This transaction ID is unique to your transaction and represents the rollback segment number, slot, and sequence number. You leave that on the block that contains your row to tell other sessions that you 'own' this data (not all of the data on the block┬┐just the one row you are modifying). When another session comes along, it sees the lock ID and, using the fact that it represents a transaction, it can quickly see if the transaction holding the lock is still active. If the lock is not active, the session is allowed access to the data.. If the lock is still active, that session will ask to be notified as soon as the lock is released. . Hence, you have a queuing mechanism: the session requesting the lock will be queued up waiting for that transaction to complete, and then it will get the data.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library