1

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?

ImaginaryHuman072889
  • 4,953
  • 7
  • 19
  • 51
  • 2
    `SELECT ... FOR UPDATE` – Lukasz Szozda Aug 16 '18 at 17:19
  • @LukaszSzozda Wow, simpler than I expected. Is the "lock" then automatically lifted when the change is committed? Also what does Oracle do if another process attempts to access this? By the way if you post this as an answer, I will accept it. – ImaginaryHuman072889 Aug 16 '18 at 17:23
  • @LukaszSzozda I just tried this. It prevents the database from being updated but doesn't prevent it from being read. This won't work for me. – ImaginaryHuman072889 Aug 16 '18 at 17:59
  • Let process #2 also `SELECT FOR UPDATE` - it'll fail because process #1 already did that, so it (#2) will have to wait until lock is released. – Littlefoot Aug 16 '18 at 18:51
  • @Littlefoot Ah, thank you for clarification. So `SELECT FOR UPDATE` will fail if another process already did that type of select statement, but a normal `SELECT` statement will always work? – ImaginaryHuman072889 Aug 16 '18 at 19:01
  • It's easy to test it - open two SQL*Plus session and run SELECT FOR UPDATE in both of them. – Littlefoot Aug 16 '18 at 19:17

2 Answers2

1

You could use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Window 1:

SQL> SET TRANSACTION ISOLATION
  2  LEVEL SERIALIZABLE;

Transaction set.

Window 2:

SQL> SET TRANSACTION ISOLATION
  2  LEVEL SERIALIZABLE;

Transaction set.

Window 1:

SQL> select * from test;

no rows selected

SQL> insert into test values (1);

1 row created.

SQL> commit;

Commit complete.

Window 2:

SQL> select * from test;

no rows selected

Even though you inserted a row and committed it in Window 1 it isn't seen in Window 2. ISOLATION LEVEL SERIALIZABLE is risky because it could lead to a lot of undesirable locking.

Bobby Durrett
  • 1,223
  • 12
  • 19
1

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

eifla001
  • 1,137
  • 8
  • 8