select query to wait for insertion of other record

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?

oracle

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:

Put your code into a PL/SQL stored procedure
Wrap the select/insert in a "begin tran/commit"
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