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

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“