Re: q: diffrence between row_shared and row_exclusive lock ?

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Mon, 22 Jul 2002 11:46:22 +1000
Message-ID: <MmJ_8.40670$Hj3.122037@newsfeeds.bigpond.com>

Hi Ted,

You need to check the doco more carefully. See my comments imbedded.

"Ted Chyn" <tedchyn_at_yahoo.com> wrote in message news:44a19320.0207210854.2e923d2c_at_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.

OK, a Row Share Lock (SS mode 2) *DOES NOT* lock out a Row Exclusive lock (SX mode 3) as previously stated. A Row Share lock *ONLY* locks out an Exclusive Table Lock (X mode 6) as well as use row locks to lock the individual rows if the lock was obtained with a select for update.

You *are* permitted to perform DML on the same table and obtain a Row Exclusive *table* lock but you are not permitted to lock the individual rows if they've been previously locked.

That's the difference between table and row locks.

>

> 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

>

>

Same scenario as above. You are permitted to lock the *table* in Row Share Mode 2 *and* lock the *table* in Row Exclusive Mode !! You just can't lock the individual rows concurrently.

> #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.

 Let me summarise the doco for you.

Row Share Table Locks (SS mode 2) locks out Exclusive Table locks (X mode 5).

Row Exclusive Table Locks (SX mode 3) locks out Share Table locks (S mode 4) and Share Row Exclusive Table locks (SSX mode 5) and Exclusive Table locks (X mode 6).

Share Table Locks (S mode 4) locks out Row Exclusive Table locks (SX mode 3) and Share Row Exclusive Table locks (SSX mode 5) and Exclusive Table locks (X mode 6).

Shared Row Exclusive locks (SSX mode 5) locks out Row Exclusive Locks (SX mode 3) and Share Table locks (S mode 4) and Share Row Exclusive Table locks (SSX mode 5) and Exclusive Table locks (X mode 6).

Exclusive Table locks (X mode 6) locks out all other table locks (mode 2, 3, 4, 5 and 6).

An individual row can be either in be shared mode (and have no locks) or in locked mode (Row Exclusive TX) in which case no other process can share this row level lock. Note DML, select for update, etc. take *two* types of locks, row locks on the corresponding rows being processed and corresponding table locks depending on the operation.

Phew !!

Please re-read the doco on locks and be clear on the distinction between row level and table level locks and what are the restrictions on each type of lock.

I hope this helps.

Regards

Richard

>
>
>
>
>

> >
> > 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 - 20:46:22 CDT