Re: q: diffrence between row_shared and row_exclusive lock ?

"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
>

The select for update takes out a SHARED lock on the table (because others are allowed to read the table, even the rows you've chosen to lock). It also takes out an exclusive row-level lock on the rows you chose to select from.

For example:

select * from emp where empno=7369 for update;

...would take a share lock on EMP, and a row exclusive lock on row 7369.

If someone else then does

update emp set sal=900 where empno=9783;

...then they share the table lock taken previous, but get their own row exclusive lock on row 9783.

What you can't do is have two row exclusive locks on the same *row*.

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

Different rows being updated, by any chance? If there are different where clauses used, both sessions will share the table lock, and have different row exclusive locks.

Regards
HJR
>
> 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 Sat Jul 20 2002 - 23:50:39 CDT