ORACLE: conditional insert statement with row lock

Posted on 2011-04-28
Last Modified: 2012-05-11
I want to insert a row in an Oracle table (table A) ONLY IF a field in a row in another table (table B) has a null value. I must make sure that the row in table B is not altered by another user before the insert into table A is committed.

Thanks in advance,

Question by:henrikthiil
    LVL 73

    Expert Comment

    in a before insert trigger on each row in A, lock the corresponding row in B.

    When your transaction commits or rollsback, the A and B locks will be released

    Note, this, as with any locking, can inhibit scalability.  Since locks, by their vary nature block others from doing work.  Thus creating waits
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    I'm curious about the exact requirement.  I understand it but just not sure for the reason.

    For example:
    You lock the row in tableB.
    I update the column for that row to a not null value.
    --I wait because of the lock
    You do your insert.
    You commit (releases my lock).
    I commit.

    You did an insert, and the column is now updated.  Did the lock do something special inside your app?

    Author Comment

    @sdstuber: this is for a project that is part of an exam and unfortunately we are not allowed to use triggers (or anything else stored on the DB server).

    @slightwv: the app is an auctioning system. Table B is a table with items on auction (it includes a buyerID field), and table A contains bids. When a seller accepts a bid, table B's buyerID field (which initially has a null value) is given the value of the buyerID field in the corresponding row in table A containing the bid. I must make sure a seller is not allowed to accept a bid (and so close the auction) the exact moment another bid for the same item is being made.
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    Sorry but we cannot provide direct answers to homework/exam questions.

    Walk through my example.

    My update will eventually take after you release the lock.

    To use actual data, what is the ending value for the 'winner':

    user1: select * from tableB where auction_id=1 and buyer is null for update;
    user2: update tableB set buyer='slightwv' where auction_id=1 and buyer is null;  --waits for locks to be released
    user1: insert into tableA (winner) values('henrikthiil');
    user1: commit;
    user2: commit;

    How does the design account for that scenerio?
    LVL 73

    Accepted Solution

    >>> we are not allowed to use triggers (or anything else stored on the DB server).

    yikes, your exam is flawed.
    Data rules should be in the database.

    however, the basic idea is still the same.  You need some sort of blocking mechanism.
    use whatever is most appropriate for the tools and languages you are allowed to use within the context of your exam.


    Author Comment

    @sdstuber: I agree not being allowed to use triggers and stored procedures (or their equivalents) is a silly constraint.

    @slightwv: don't worry, I am not cheating on an exam. We are working on a (somewhat) large (three month) software project that we must hand in in lieu of an exam, and I am just trying to solve a problem that's a tiny part of the whole thing. :-)

    The idea was to deny update of the auctions table if a bid was being inserted during the update, but we have now found another solution.
    LVL 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    >>The idea was to deny update of the auctions table if a bid was being inserted during the update

    I would still like to know about the scenarioI presented.  The update might likely still happen.

    >>but we have now found another solution

    Can you share?

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now