Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Venkat.

Asked: December 12, 2005 - 11:19 am UTC

Last updated: January 14, 2013 - 11:34 am UTC

Version: 9.2.0.5

Viewed 1000+ times

You Asked

Tom,
In your books and this post, you wrote that one of the major differences between Oracle and other databases is the locking mechanism. If Oracle does not maintain a list of locks (like most other databases do), how does Oracle know what is currently locked? Where does V$LOCK get its information from? Does Oracle look at every data block to find locks within the database at that point?

Thanks.


and Tom said...

v$lock shows what transactions have a lock on zero, one or more rows in a given table.


v$lock does not show me an entry for each row I have locked in that table, if I have 1,000 rows or 1 row or 10,000,000 rows locked, there will be one entry in v$lock denoting that I have "something", but not what.

In order to find out what rows I have locked, one would have to GO TO the row - locks are attributes of the data itself.

(In my books I've written about this in detail, also in other postings... From expert one on one Oracle and "Expert Oracle: Database Architecture"

<quote>
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.
</quote>


Rating

  (4 ratings)

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

Comments

Followup to How does Oracle determine locks

Venkat, December 12, 2005 - 3:57 pm UTC

Thanks for the answer, Tom. I do understand that locks are stored along with the rows. What I still don't get is HOW does Oracle know what locks there are, if a list is not maintained. Are you saying that Oracle maintains "something" that tells it there are 1 or more locks which it can find by going to the rows? So there may be 10,000 rows locked but it is indicated by a single entry somewhere?


Tom Kyte
December 12, 2005 - 4:22 pm UTC

Oracle doesn't need to know what locks there are. Why would it?

All it knows is "transaction A has modified table T and therefore, until transaction A commits/rollsback - there shall be a TX lock in V$LOCK signifying that fact."

It doesn't know what rows are locked in particular, it doesn't care.

where is the data ?

A reader, December 13, 2005 - 4:15 am UTC

you said the lock is with the data

...
   dbms_lock.allocate_unique(lockname => p_lockname,
                             lockhandle => p_lockhandle);   

   rr := dbms_lock.request(lockhandle => p_lockhandle,

...


Where is the data ?

Tom Kyte
December 13, 2005 - 9:29 am UTC

that creates a row in a table.


</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_lock2.htm#1001065 <code>

...
A lock name is associated with the returned lock ID for at least expiration_secs (defaults to 10 days) past the last call to ALLOCATE_UNIQUE with the given lock name. After this time, the row in the dbms_lock_allocated table for this lock name may be deleted in order to recover space. ALLOCATE_UNIQUE performs a commit.
.........


so, the lock is stored with the data ;)

A reader, December 13, 2005 - 9:54 am UTC


Oracle locks

Keith, January 07, 2013 - 3:48 pm UTC

Tom,

As you explained, locks are not in-memory structures and are attributes of the data itself instead, so Oracle has to go to the rows to find out what rows are locked.
How does Oracle handle the concurrent updates?
For instance, three processes are trying to update the same table; process1 updates records with rowid 1-100, process2 updates records with rowid 50-110, process3 updates records with rowid 80-120.
Can you please explain in detail how Oracle updates these records?
Tom Kyte
January 14, 2013 - 11:34 am UTC

each of the processes would use what is known as a consistent read to "find" the rows.

consistent reads are none blocking, point in time result sets.


So each process would read a record to see if they wanted to update it (see if it matched their where clause).

this would not block.

when they ascertained they wanted to update the row - they would attempt to lock it, if it was already locked - they would block until the blocker commits or rolls back.


once they unblock - they would do a current mode read on the row to ensure they still wanted to update that row (the other blocking session might have modified the row in a manner that makes it not match their where clause anymore for example)...


http://asktom.oracle.com/Misc/something-different-part-i-of-iii.html
http://asktom.oracle.com/Misc/part-ii-seeing-restart.html
http://asktom.oracle.com/Misc/part-iii-why-is-restart-important-to.html

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