ORACLE SQL - Lock a table until a task finishes [Using EF Core]

user_11175348 0
Viewed 119

I have a table called TOKEN. TOKEN just contains 1 row with specific informations about an external web service. On the (possible multiple) client side I want to update the row in TOKEN (1x per week). This happens by calling another web services etc. In that time nobody should be able to read and write to table TOKEN. Eg.

  1. Lock TOKEN so no one can read and write
  2. Generate new informations by calling a web service (could go up to a few seconds) - client side!
  3. update the row in TOKEN
  4. Unlock the table again and let the other read and write (although write would not be needed for the next week anymore).

How could I achieve such a behaviour? If 2 users try to execute the new info. generation at the same time (while the other one is already and still executing point 2.), corrupted data could be the result. Any ideas?

entity-framework oracle sql c#
1 Answers

As far as I can tell, in Oracle you can't block readers from reading the table. Why wouldn't they be able to read "old" value, because it is valid until you "calculate" the new value? I don't know how to prevent them to do so. Truncate the table (so that it is empty)?

Anyway: one option is to

  • use a function (because you want to return new value to caller) ...
  • ... which is an autonomous transaction (otherwise you can't perform DML within) ...
  • ... that uses
    • SELECT ... FOR UPDATE to lock row for updates by other users
    • LOCK in case table is empty; you might not need it because you already have some data in the table

Here's a sample function; see if it helps.

FUNCTION f_token (par_id IN NUMBER)
   RETURN NUMBER
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
   l_dummy  VARCHAR2 (1);
   l_token  NUMBER := 1;                 -- suppose default token value is "1"
BEGIN
       -- FOR UPDATE will "lock" row for updates by other users
       SELECT 'x'
         INTO l_dummy
         FROM token b
        WHERE b.id = par_id
   FOR UPDATE OF b.token;

   -- here you'd call another web services etc. and acquire new value into L_TOKEN

   UPDATE token b
      SET b.token = l_token
    WHERE b.id = par_id;

   COMMIT;                                         -- commit releases the lock
   RETURN (l_token);
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      -- in case table is empty, lock it so that nobody can do anything with it, except you
      LOCK TABLE token IN EXCLUSIVE MODE;

      INSERT INTO token (id, token)
           VALUES (par_id, l_token);

      COMMIT;                                      -- commit releases the lock
      RETURN (l_token);
END f_token;
user_90979060
Related