Can Oracle database transactions help in this scenario?

Question

I am working with an Oracle database (11g Release 2). Imagine multiple connections doing the following simultaneously:

1. Start transaction
2. Check if a specific value exists in a table of unique values
3. If the value does not exist, insert it
4. Commit transaction

It seems to me that the only way to prevent conflicts is to block connections from performing the above 4-step sequence while any other connection is currently performing the 4-step sequence.

Can transactions achieve this kind of broad locking/blocking in Oracle?

Thanks in advance for your answers and advice on how to best deal with this scenario.

Answer 1

Add a unique check constraint, and implement an exception handler to get the next sequence and try again.

This is assuming you're using pl/sql.

An alternative would be using an Oracle sequence, with cache size 1. This will also ensure no gaps in the sequence 2. SELECT * FROM table_name FOR UPDATE to block all reads from other sessions...