Re: q: diffrence between row_shared and row_exclusive lock ?

Ted,

I am sorry, this is not true. In

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/a76965/c23cnsis.htm#2937

you can read:

<quote>
The combination of multiversion concurrency control and row-level locking means that users contend for data only when accessing the same rows, specifically:

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.

A transaction acquires an exclusive DML lock for each individual row modified by one of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.
</quote>

A "select ... for update" acquires an exclusive row lock, not a share lock. A row can always be read even if it is locked in exclusive modes. There is no way you can prevent reading of a row by locking.

Martin

Ted Chyn wrote:
>
> sybrand,
> thnx for the answer.
> row share: others can read
> rows exclusive: access is completely blocked
>
> when I tested this in sqlplus whether lmode =2(row share) or 3(row
> share exclusive) does not make any difference. In both cases updater
> does not block reader. If it is so,
> there must be something else differs between the two.
>
> ted
>
> Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:<tm6ijugh8nc66adje9qeehb2kn1e847283_at_4ax.com>...
> > On 19 Jul 2002 23:17:36 -0700, tedchyn_at_yahoo.com (Ted Chyn) wrote:
> >
> > >all,
> > >
> > >row shared lock lmode=2
> > >row exclusive lock lmode=3
> > >
> > >row shared is more restrictive than row exclusive.
> > >row shared can be generated with select ... for update of
> > >row exclusive can be generated with lock table in row exclusive mode
> > >or update statement.
> > >
> > >
> > >
> > >Can any one give me an working example explaining what are difference
> > >between these two type locks - in terms why one is more restritive
> > >than the other?
> > >
> > >thnx in advance
> > >ted chyn
> >
> > row share: others can read
> > rows exclusive: access is completely blocked
> >
> > Hth
> >
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
Received on Thu Jul 25 2002 - 09:42:51 CDT