• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 604
  • Last Modified:

ORACLE: conditional insert statement with row lock

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
0
henrikthiil
Asked:
henrikthiil
  • 3
  • 2
  • 2
2 Solutions
 
sdstuberCommented:
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
0
 
slightwv (䄆 Netminder) 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?
0
 
henrikthiilAuthor 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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
slightwv (䄆 Netminder) Commented:
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?
0
 
sdstuberCommented:
>>> 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.



0
 
henrikthiilAuthor 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.
0
 
slightwv (䄆 Netminder) 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?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now