In Oracle, is there a way to have a database row "locked" from reading while another process is reading from it? A problem I am experiencing is that sometimes if two processes try to access the same database row and update it at the same time, one of the updates can be lost.
Here is a basic time diagram of what is happening.
Process #1 Process #2
------------------ ------------------
Read from Database
Some processing... Read from database
Some processing... Some processing...
Update database Some processing...
Update database
In the example above, Process #1's updates are lost because Process #2 read from the database before Process #2 finished updating.
I have modified the code (which is in C++, but I'm not sure if that really matters for this question) to minimize the amount of processing that occurs between reading the database and writing to the database (e.g. Read from the database at the latest possible moment we can, do only the exact amount of processing we need to do, then update immediately), which has helped mitigate this problem but there is still no guarantee that it is a bulletproof fix.
I could modify the code to have Process #2 just tell Process #1 what to update the database with and have Process #1 handle all interaction with the database, but unfortunately in my situation there are dozens of processes and database tables I'm dealing with, so I'm not sure that a change like this is practical.
Is there anything that can be done in the database to prevent this problem from occurring?
You can use the FOR UPDATE clause in your query.
For more details about this clause, see this link: https://www.techonthenet.com/oracle/cursors/for_update.php
Check also this question below question for some sample code that uses FOR UPDATE.
Link: How to use Oracle DB sequences without losing the next sequence number in case of roll-back