row locking and inserts

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

Thanks in advance,
-pp Received on Sun Mar 25 2007 - 09:54:13 CDT