Re: diffrence between row_shared and row_exclusive lock ?

richard,

#1

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST -------- -------- ---------- -- ---------- ---------- ---------- ----------

     CTIME BLOCK
---------- ----------

6BBA6284 6BBA6390          9 TX     458773       1083          6          0
       187          0

6BB4E074 6BB4E088          9 TM      30139          0          2          0
       187          0

6BB4E0F8 6BB4E10C         10 TM      30139          0          3          0


In #1 sid 9 (select .. for update). tm lock lmode=2
      another session (sid=10) I can lock the same table with row exclusive
      (lmode =3). in both cases id1 = 30139
         

#2

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST -------- -------- ---------- -- ---------- ---------- ---------- ----------

     CTIME BLOCK
---------- ----------

6BB4E074 6BB4E088          9 TM      30139          0          3          0
        65          0

6BB4E0F8 6BB4E10C         10 TM      30139          0          3          0
        30          0

In #2 sid=9 lock table(30139) in row exclusive mode (lmode=3)
      sid=10 can lock the same table(30139) with row exclusive mode(lmode=3)

What you say is true in most literature and I am confused because my testing can not prove it. I'm confused.

thnx ted

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<Ko8_8.39130$Hj3.118745_at_newsfeeds.bigpond.com>...
> 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 - 23:36:06 CDT