Database Reference
In-Depth Information
TX (Transaction) Locks
A
TX
lock
is acquired when a transaction initiates its first change. The transaction is automatically initiated at this
point (you don't explicitly start a transaction in Oracle). The lock 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:
1.
Get in line again.
2.
Lock the list of locks.
3.
Search through the list and release all of your locks.
4.
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 right there, right then—at the location of the row, not in a big list somewhere
(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's 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 undo 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
transaction ID and, using the fact that it represents a transaction, it can quickly see if the transaction holding the lock
