Re: q: diffrence between row_shared and row_exclusive lock ?

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 ?

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 - 02:37:52 CDT