thank you very much Howard, this does indeed make sense
Howard J. Rogers wrote:
> "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 Mon Jul 22 2002 - 16:21:22 CDT
