Re: diffrence between row_shared and row_exclusive lock ?

Hi Ted,

There are quite a number of different locks (RS, RX, S, SRX, X etc) held by Oracle automatically for different activates that may occur on a table that have varying degrees of restriction. It's possible to manually lock a whole table in a number of these modes although this is seldom necessary.

To answer your particular question, the row share table lock (RS) is the least restrictive of the various table locks and is used by Oracle automatically to lock the table when 'select .. for update' is performed (as well as the corresponding rows locks). It's the least restrictive because it allows all other table locks to simultaneously occur with the exception of the Exclusive (X) table lock.

Whereas the Row Exclusive table lock is slightly more restrictive and is held automatically by Oracle when DML is performed. Unlike the RS table lock, it prevents the manual locking of the table in share mode (S), in Shared Row Exclusive mode (SRX) and in Exclusive mode (X).

You basically use the RS lock if you want to guarantee no one else will use an Exclusive Table lock on you.

You basically use the X locks if you want nobody to anything on the table except yourself (and queries which are unaffected).

In most cases I've see manual table locks being held, it's because a SQL Server user hasn't read up on how Oracle's locking mechanisms work. I suggest a good read of the Concepts manual and the Administrations Guide to determine exactly what each of these locks restrict and why you may want to use them.

Cheers

Richard

"Ted Chyn" <tedchyn_at_yahoo.com> wrote in message news:44a19320.0207192217.78fe11eb_at_posting.google.com...
> 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
Received on Sat Jul 20 2002 - 02:42:33 CDT