Re: row locking and inserts

"pp" <pedro.e.pinto_at_gmail.com> wrote:
> Hi there,
>
> I am working in an application in which I want to lock a set of rows
> in a table . I want to garantee that, while the lock is held, no other
> sessions can lock the same rows. I think this is a straightforward use
> of "select for update":
>
> session 1> begin transaction
> session 1> select * from table where field1=X for update
> session 1> n rows returned
> session 2> begin transaction
> session 2> select * from table where field1=X for update
> session 2> waiting...
> session 1> commit
> session 2> n rows returned
> session 3> commit
>
> The problem is that I also want this to work when no rows exist yet
> with field1 equal to X.
>
> session 1> begin transaction
> session 1> select * from table where field1 = X for update
> session 1> 0 rows returned
> session 2> select * from table where field1 = X for update
> session 2> Waiting (even though there are no actual rows to lock)
> session 1> insert into table (field1, field2) values (X, Y)
> session 1> commit
> session 2> 1 row returned
> session 2> commit
>
> I do not have a Oracle DB handy right now, so I can't tell if the
> above works. I suspect not.

No, it won't work.

> If so, is there a way to achieve this? I
> do not want to lock the entire table as other sessions should not have
> to wait if they are using different values for field1. In my
> particular case field1 is not a primary key, but I could use an
> auxiliary table, just for locking purposes, where field1 is a primary
> key.

Yep, that is exactly what you would need to do.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB