We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

ORACLE: conditional insert statement with row lock

Medium Priority
616 Views
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,

Henry
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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

Commented:
@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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
@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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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?
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.