Re: q: diffrence between row_shared and row_exclusive lock ?

"sg" <s4v4g3_at_europe.com> wrote in message news:3D3B3C80.8010206_at_europe.com...
> Hi Howard
>
>
> I have some doubts about row shared locks as well, from your post
>
> ========================================================================
> ....then they share the table lock taken previous, but get their own row
> exclusive lock on row 9783.
> ========================================================================
>
> Do you mean the meaning of *share* is sharing the table lock....???? Or
> it is really that the locked row can be shared.... well I am not very
> clear because I dont really understand what's the meaning of share here.
> I have always thought that a row is always locked exclusively. i.e if
> one is trying to updating a row no matter with pure update or select...
> for update the row is locked and noone else can lock that row
>
>

Hmmm. I thought I'd made it clear that it's the table lock that is shared. If there is a row exclusive lock, then it's exclusive, and exclusively for the row being locked, and exclusively for the session whose DML statement took out the lock.

I'm clearly missing the point here, but I don't understand what your difficulty is.

If I lock a row (that's an exclusive row-level lock), I *also* lock the table, because I don't want you doing something daft like dropping a table while I'm in the middle of updating it. So the table lock prevents things like dropping, truncating, dropping a column, index rebuilds and so on. But it would be a sad database if such a TABLE lock, taken by me as part of doing a single-row update, were to lock you out from seeing other rows (select * from emp where empid=something_not_my_update's_empid), or were to prevent you from updating a row which *my* update is NOT currently locking. So that table lock is shareable: the moment you start doing DML on a another row in the table, you'll get your own exclusive row-level lock (of course), but you'll also be able to share the existing table lock with me. Now we're both protected from a third party attempting to do some dodgy DDL whilst our updates are pending.

When my update is committed, the exclusive row level lock I held is removed, and I lose my hold on the shared table lock. But you retain yours.

So I think we agree... routine updates, inserts and deletes always take out exclusive locks.

I think the problem is the statement that rows can *only* be locked exclusively. If I do a 'select for update' on a row, I lock the row exclusively *for the purposes of *you* doing DML on that row*. You are locked out from changing that row, in other words. But it's a shareable lock in the sense that it doesn't stop you *selecting* that row. The lock doesn't prevent you from *seeing* that row, in other words.

A regular DML *is* exclusive, because you can't see the row I'm updating -you have to see a read-consistent image of that row, generated by the use of rollback and so on.

Does that make things any clearer??!

Regards
HJR
>
>
> Howard J. Rogers wrote:
>
> > "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 Sun Jul 21 2002 - 18:22:23 CDT