Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

select for update and insert if not exist atomically

Hello,
I have concurrent connections to an oracle db.
On each connection, concurrently, the connected process need to :
1- select for update a register based on a key ( is the primary table key). If there are not rows with this key, insert a register with default values on this key.
2- make some operations
3 - after that, update the row and commit.
We implement this doing a 'select for update...' and if not rows were returned, perform the 'insert...'
We have the problem that if two applications concurrently made de 'select for update...' on an non existent key, both will try to perform the insert. One will continue and the other will fail on the insert when the first one commits, and this has to be catched and reprocessed.
We are looking if there is a way to do this by the db in one sentence, that look for the key, return the data if found, and if not, insert it with the given values and return it. All in a single sentence executed atomically.
We'd like to on a single sentence that perform 'select for update, and if not exist insert with default values and return it', and that if a second connection execute on the same key, just wait for the lock to be freed and do not generate an error ( this second one should return the inserted and updated row by the first execution)
One possibility we found is before starting the transaction, perform an insert.. ..where not exist../commit, but this implies to always try to insert when the need of insertion is unlikely.
Thanks

This post has been answered by Ahmed AbdelFattah on Sep 5 2021
Jump to Answer
Comments
Post Details
Added on Sep 4 2021
12 comments
8,933 views