Re: Record locking

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 04 Jan 1999 22:12:43 GMT
Message-ID: <369f36ce.30080874@192.86.155.100>

A copy of this was sent to suisum_at_ecn.ab.ca () (if that email address didn't require changing) On 4 Jan 99 21:36:21 GMT, you wrote:

>I read the following from the Oracle Certified Professional DBA
>Certification Exam Guide. I think it may be an error:
>
>Chapter 3 - Creating the Oracle Database (P. 139) - Locks
>
><QUOTE>
>TIP: An update statement acquires a special row-level lock called a
>"row-exclusive" lock, which means that for the period of time the update
>statement is executing, no other user in the database can view OR change
>the data in the row. Another update statement, the select for update
>statement, acquires a more lenient lock called the "share row" lock. This
>lock means that for the period of time the update statement is changing
>the data in the rows of the table, no other user may change that row, but
>users may look at the data in the row as it changes.
><UNQUOTE>
>
>I sign on as a user to update a row without commit. Then sign on as
>another to select the same row. I can see it. The record locking is only
>occured when the second user want to update the same row.
>
>Anyone can clarify this?

I would say the documentation is in error if that is a direct quote. First of all -- simple selects do not block for updates -- the update that updates a row will not prevent a select from seeing (reading it). The "select for update" and "update" statements get the same exact kind of locks at the row level (at the table level, a little different but more on that in a second).

So, when they say

>TIP: An update statement acquires a special row-level lock called a
>"row-exclusive" lock, which means that for the period of time the update
>statement is executing, no other user in the database can view OR change
>the data in the row.

they are definitely wrong, here is a quote from the server concepts manual that boils it down pretty effectively:

<quote>
- Readers of data do not wait for writers of the same data rows. - Writers of data do not wait for readers of the same data rows (unless SELECT... FOR UPDATE is used, which specifically requests a lock for the reader).
- Writers only wait for other writers if they attempt to update the same rows at the same time.

Note: Readers of data may have to wait for writers of the same data blocks in some very special cases of pending distributed transactions.
</quote>

It is true that the locks held by a "SELECT ... FOR UPDATE" are a little less restrictive then an "UPDATE" but -- and this is an important but -- only at that table level. A select for update on a row will prevent an update or select for update of that row by another session. An update on a row will likewise prevent an update or select for update of that row by another session. At the table level however, a SELECT ... FOR UPDATE will prevent another session from locking the entire table in exclusive mode. An update will prevent another session from locking the entire table in exclusive or share mode.

At the row level an update and select for update are pretty much the same. At the table level, an update is slightly more restrictive -- but it is SO rare to ever issue any of:

LOCK TABLE table IN SHARE MODE;
LOCK TABLE table IN SHARE EXCLUSIVE MODE; LOCK TABLE table IN EXCLUSIVE MODE;

that is in reality doesn't really matter.

Also, I would take exception to their statement:

<quote>
>lock means that for the period of time the update statement is changing
>the data in the rows of the table, no other user may change that row, but
>users may look at the data in the row as it changes.
</quote>

That implies (to me) a DIRTY read. "but user may look at the data in the row as it changes". Oracle doesn't do dirty reads.

Also, they say:

<quote>
>.... Another update statement, the select for update
>statement, acquires a more lenient lock called the "share row" lock. This
>lock means that for the period of time the update statement is changing
</quote>

which is really nonsense.

"Another update statement, the select for update statement". That doesn't make sense. SELECT is *not* UPDATE. select for update is not another update statement -- its a select that locks data.
"this lock means that for the period of time the update statement is changing..." is nonsense as well. select ... for update doesn't update anything. You have to eventually issue an UPDATE statement in which case the table lock you have is promoted from row share to row exclusive anyway (that is, if you are going to change the data, you will update and you will get a row exclusive lock anyway -- select for update doesn't help you at all)

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jan 04 1999 - 16:12:43 CST

Re: Record locking - UPDATE