select query to wait for insertion of other record

Go To


In my application multiple requests simultaneously read record from one table and based on that insert new record in table.

I want to execute request serially so that the second request reads the latest value inserted by the first request.

I tried to achieve this using select for update query but it lock only row to be wait for update, as I can't update existing record it got same value as previous request got.

Is it possible using Oracle locking mechanism? How?

2012-04-04 02:15
by chetan
What do you need to know about the most recent record before you can insert the next one - eaolson 2012-04-04 02:18
I'd start by reading the Oracle Concepts manual. You'll learn about how the database works -- like writers not blocking readers. And that will help you either fix your problem pretty easily or rephrase your question in a way that makes the issue clear to the rest of us. For now, it sounds like you have multiple parallel query-and-update processes that you want to be distinct from each other. The solution might be as simple as not doing parallel, or adding a constraint to trap and reject duplicate inserts. Or something completely different, we can't tell - Jim Hudson 2012-04-04 14:11


Dude - that's what transactions are for!

Strong suggestion:

  1. Put your code into a PL/SQL stored procedure

  2. Wrap the select/insert in a "begin tran/commit"

  3. Don't even think about locks, if you can avoid it!

2012-04-04 02:20
by paulsm4
Beginning a transaction doesn't stop another session from inserting into a table - eaolson 2012-04-04 02:21