Re: q: diffrence between row_shared and row

From: Ted Chyn <tedchyn_at_yahoo.com>
Date: 21 Jul 2002 09:54:40 -0700
Message-ID: <44a19320.0207210854.2e923d2c@posting.google.com>

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:<jqt_8.40123$Hj3.120006_at_newsfeeds.bigpond.com>...
> Hi Ted,
>
> OK, we need to separate "Row Level" locks from "Table Level" locks. A row
> can be either be locked or shared. If locked, no other session may lock *the
> same row*. If shared, then it may be locked by any session. When performing
> such an operation (such as DML, or select for update, or update of primary
> key, or delete cascade), these operations *also* lock the *TABLE* in one of
> a number of different modes (as previously mentioned). It's these table
> locks that have varying degrees of restriction and can be implemented by
> manually locking a table.
>

#########################################################################

> For example, in one session issue a 'lock table emp in row share mode'. This
> locks the *whole table* in share row mode (which is the least restrictive
> lock). In another session, issue another 'lock table emp in row share mode'
> and it *will* work. But issue a 'lock table emp in exclusive mode' and this
> will *NOT* work. This other session is locked out (until the commit) because
> a row share table lock locks out exclusive table locks. In fact. exclusive
> table locks is the only lock that a row share lock prevents. Get it ?

#######################################################################
richard, If row share locked out row exlusive,
         then how do you explain following 
         1. case #1
         where one sid has tm lmode =2 and another sid can obtain another
         tm lock lmode=3 on the same table.
         2. case #2
         where one sid has tm lmode=3 and another sid can obtain another
         tm lock lmode=3 on the same table.

thnx ted 
            
              
#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

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.

>
> Another example. In one session issue a 'lock table emp in exclusive mode'.
> This locks the *whole table* in exclusive mode (which is the most
> restrictive lock). In another session, issue *any* other table lock, say
> 'lock table emp in row share mode'. This will *NOT* work because the session
> with the exclusive table lock is preventing the second session from
> obtaining such a lock. Now do you get it ?
>
> Oracle implements two locks (row and table) for the following reason. Row
> level locks to support data consistency and prevent two rows from being
> updated concurrently. Table level locks to prevent DDL operations (such as
> drop table, alter table etc.) from being performed in the middle of a
> transaction.
>
> If you view it from a whole table perspective it starts to make sense. And
> this is what I was meaning when saying such *manual* table locks are very
> rarely necessary. Oracle manages to pick the appropriate lock (and it's
> associated restriction) in the vast majority of cases.
>
> There are row locks and there are table locks. You can manually apply an
> appropriate *table* lock IF you wish.
>
> I hope this all helps with your understanding.
>
> Regards
>
> Richard
>
>
> "Ted Chyn" <tedchyn_at_yahoo.com> wrote in message
> news:44a19320.0207202037.430ff971_at_posting.google.com...
> > sybrand, here is my test.
> >
> > 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
> >
> >
> >
> > 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 Sun Jul 21 2002 - 11:54:40 CDT

Re: q: diffrence between row_shared and row_exclusive lock ?