Re: row locking and inserts

On Sun, 25 Mar 2007 07:54:13 -0700, pp 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. 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,

You could get an Oracle DB fairly easily - download the free Oracle Express edition (available for most common Linux and Windows). The SQL and PL/SQL engines are identical to the for-fee versions.

Please explain why you want to lock these rows in more detail. I'm asking for the business reason, not the technical reason. (It is better to develop a new solution than fix a solution with broken assumptions. Experience in SQL Server, MySQL, DB2, Java's pseudoDB stuff like EJB-QL, and so on often results in broken assumptions in Oracle.)

A reason - I've seen many developers want to avoid queries locking rows that the user may want to update. However ... in Oracle queries will never block updates.

When rows exist, and you specifically want to lock the rows from other updaters, your 'For Update' will work ... until you commit.

However, you can never lock rows that do not exist, since a lock is really a serialization or queue to an existing resource.

On the other hand, if the rows don't exist and you have not committed, no one else can see them and you should not normally have a locking issue.

Another reason - you use a lot of COMMITs. Generally that raises eyebrows in good Oracle programming. COMMITs in Oracle demark the end of a transaction. In other systems, a commit is often simply an action needed to release locks.

An alternative to the lack of real rows is to use the DBMS_LOCK as a serialization mechanism. That is well documented at http://tahiti.oracle.com and Dan probably has demos in Morgan's Library at http://psoug.org

-- 
Hans Forbrich   (mailto: Fuzzy.GreyBeard_at_gmail.com)   
*** Feel free to correct me when I'm wrong!
*** Top posting [replies] guarantees I won't respond.