Hi,
We're using "Oracle Database 12c Enterprise Edition Release 12.1.0.2.0"
We have a process that creates and runs a merge statement to update one row in a specific table.
I know you can force a locked row using a function from this link.
https://www.oratable.com/how-to-lock-a-row-in-oracle/
It looks like to do this you just run a select statement over a bunch of rows and include a FOR UPDATE at the end and the rows will be locked until you issue a commit or a rollback.
Would I be able to add this FOR UPDATE with a merge statement?
Also would I be able to add FOR UPDATE SKIP LOCKED to the merge statement as well?
That's all the info you really need . You can stop reading here if you want.
Additional info if you need it.
That row which is being updated can be identified by a seq_id. When the process completes, another table is updated that has a row for that seq_id and as well as the status for the outcome of that transaction. So if table A, with its row identified by seq_id, is successfully updated with the merge statement than table B has its row with its corresponding seq_id updated with its column for status flag changed from 'I' to 'P" . ("I" be be inserted, "P" processed).
The reason being, we have another process that runs if the db goes down. It runs through and sets the flag in table B from 'I" to "R" ("R" for reprocess. ; I didn't write this code, don't ask me why they did it this way.) This makes sure if any row was in the middle of being reprocessed and didn't commit it is then just rerun.
However the first job at the top begins running again and starts updating the table A with data and table B with results. The concern is can we check to see that a process with a status "I" is running at the same time we're setting all the status "I" rows to "R" and exclude that seq_id that hasn't been committed yet but is currently running so as not to set that status flag to "R" and have that one seq_id be run again unnecessarily?