Row Lock is Synonymous with TX Lock, and is a Lock on a Single Row of a Table – Verify the Documentation Challenge

10 06 2011

June 10, 2011

I found an interesting quote in the Oracle Database documentation library:

Row Locks (TX)
A row lock, also called a TX lock, is a lock on a single row of a table. A transaction acquires a row lock for each row modified by one of the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT … FOR UPDATE. The row lock exists until the transaction commits or rolls back.

When a transaction obtains a row lock for a row, the transaction also acquires a table lock for the table in which the row resides. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.

Table Locks (TM)
A transaction automatically acquires a table lock (TM lock) when a table is modified with the following statements: INSERT, UPDATE, DELETE, MERGE, and SELECT … FOR UPDATE. These DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction. You can explicitly obtain a table lock using the LOCK TABLE statement, as described in ‘Manual Data Locking‘.”

It appears that the above quote was changed a bit and included in section 5-11 of the alpha copy of the book that I am reading:

“Oracle uses two types of locks to prevent destructive behavior: exclusive and share locks… Row locks, indicated by the symbol TX, lock just a single row of table for each row that’ll be modified by a DML statement such as INSERT, UPDATE, and DELETE. This is true also for a MERGE or a SELECT … FOR UPDATE statement. The transaction that includes one of these statements grabs an exclusive row lock as well as a row share table lock.”

Something to think about: If a row lock is also called a TX lock, and it is a lock on a single row in a table, does that mean that one would expect to see 1,000 of these TX locks for a single session if that session modifies 1,000 rows in a single table?  Or, might we expect to see only a single transaction lock (TX) for the session that modified 1,000 rows in a single table – that would indicate that a row lock is not synonymous with a TX lock.  For that matter, what about the other types of TX enqueues?

Maybe it works as Tom Kyte states here:

“the TX is not a row lock, TM is the ‘row lock’ actually.”

The above is further explained in this exchange on the OTN forums between Tom Kyte and Mark Bobak.

Some additional clarification from the latest version of Tom Kyte’s book (also found in his much older “Expert One-On-One Oracle” book):

“Whereas we get only one TX lock per transaction, we can get as many TM locks as the objects we modify.”

Combining the above quote with the quote from the documentation, does that mean that a session can only lock a single row per transaction?  :-)

So, is the documentation correct?  If not, how would you re-word what appears in the documentation?  Keep in mind the sub-title of this blog:

Stop, Think, … Understand


Actions

Information

5 responses

10 06 2011
Kyle Hailey

The Tom Kyte discussion give some nice alternative perspectives.
I think part of the problem is explaining things in way that readers can grasp onto efficiently. If ideas are explained in full detial then it can sound like incomprehensible fog.

I like Mark Bobak’s summary in the discussion:

“A TX lock is the transaction itself, and can be interpreted as a row-level lock, in the sense that it is the lock structure that is used to “protect” uncommitted changes to rows.”

– Kyle Hailey

13 06 2011
Charles Hooper

Kyle,

Thank you for your perspective.

I suppose that too much detail is just about as bad as not nearly enough detail when explaining concepts. I also like Mark Bobak’s summary. I wonder if an enhancement could be made to his summary. I had in mind something along the lines of “The lock structure which other sessions queue upon when those sessions attempt to modify uncommited changes made by other sessions.” (that might have been suggested in the OTN thread) Then we might also have to address those TX locks that do not result in “enq: TX – row lock contention” wait events: “enq: TX – allocate ITL entry”, “enq: TX – index contention”, and “enq: TX – contention”.

Tom Kyte’s comment does have the apparent intended effect – makes the reader stop and think for a couple of minutes.

11 06 2011
Paresh

I vote for Mark Bobak’s position especially with Mark’s statement in Kyle’s response above and Mark’s demo that we can disable TM – DML locks on a table and still have pseudo row level ‘locks’ in form of TX lock. Think of it as one lock protecting multiple rows (and ITL list etc.) just like many latches protect multiple data structures each e.g. latch that protects LRU buffer chain (the analogy is not perfect but to give an idea).

Paresh

11 06 2011
Paresh

There is just one gotcha with about conceptual understanding (my response above) as per demo by Tom. In his demo Tom explains that if TX was a true row lock, session B should be able to proceed when session A rolls back to save point.

I still will vote for Mark’s position ;) (better of the 2 ‘evils’).

Paresh

13 06 2011
Charles Hooper

Paresh,

Thanks for providing your point of view. I need to work through the supplied test cases – it might be interesting to see if the results of the test cases are different for the latest release of Oracle Database.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 140 other followers

%d bloggers like this: