Re: newbie row locking question

From: John P. Higgins <jh33378_at_deere.com>
Date: Tue, 20 Oct 1998 20:04:59 -0500
Message-ID: <362D333B.11954F0@deere.com>

By default, Oracle locks the rows whenever you execute an insert, update or delete SQL statement.

Inserts are not a problem, no one else can see them until you commit.

If your application requires that no one else be allowed to update or delete a set of rows between the time you read them and the time you commit or rollback, you need to include the FOR UPDATE option in your select. This applies a row exclusive lock until you commit. No one else can lock the same rows FOR UPDATE until you commit or rollback.

Note that other selects could read the same rows between your select and your commit if they do not use the FOR UPDATE clause. Since your transaction didn't complete first, these other selects will not see your inserts, updates or deletes.
 
 

Jim Haran wrote:

Hello all,
    As a recent newcomer to the Oracle world, there are a few questions
I have. I have not come across documentation that covers row locking. In
the old world (xBase), I would lock a record as soon as the user
accessed it. As such, if another user attempted to edit the same
receord, the lock would be detected and a dirty read at best would be
allowed.
    Do I need to explicitly lock rows in my apps? If not how does one
control possible concurrent updates to the same row? I'm sure this is
covered somewhere in the documentation but I have'nt found it. Thanks in
advance for any help.

Jim

  Received on Tue Oct 20 1998 - 20:04:59 CDT