Re: Lock Table

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 08 Sep 2005 17:52:14 -0700
Message-ID: <1126227089.557020@yasure>

xhoster_at_gmail.com wrote:
> DA Morgan <damorgan_at_psoug.org> wrote:
>

>>xhoster_at_gmail.com wrote:
>>
>>>DA Morgan <damorgan_at_psoug.org> wrote:
>>>
>>>>AnySQL (d.c.b.a) wrote:
>>>>
>>>>
>>>>>you can change you sql to select (1) from my_table for update, else
>>>>>oracle will never block reader.
>>>>>
>>>>
>>>>It is impossible, in Oracle, to block a reader? Where are you getting
>>>>your information?
>>>
>>>
>>>"Select...for update" reads things.  "Select...for update" blocks.
>>>Hence, it is possible to block a reader.
>>>
>>>Xho
>>
>>Can you write a simple demo that demonstrates blocking a reader? I can
>>see how to block an insert, update, or delete, but not a select.

>
>
>
> Session 1:
> SQL> create table foo ( bar varchar2(10));
> Table created.
> SQL> insert into foo values ('hi');
> 1 row created.
> SQL> commit;
> Commit complete.
> SQL> update foo set bar='bye' where bar='hi';
> 1 row updated.
>
> Session 2:
> SQL> select * from foo where bar='hi' for update;
> <At this point, the select has blocked>
>
> Sesssion 1:
> SQL> commit;
> Commit complete.
>
> Session 2: <immediately unblocks, giving:>
> no rows selected
>
> Xho

We have a misunderstanding here. You didn't block a SELECT you blocked an attempt to lock the record with FOR UPDATE. That is not what I thought you were stating.

I thought you were saying that you could do a SELECT FOR UPDATE and block another session from SELECT.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Thu Sep 08 2005 - 19:52:14 CDT