I'm studying Oracle transaction behavior and trying to understand how to safely lock multiple rows across different tables before performing atomic updates.
Consider the following two transactional operations (There are three tables):
Operation A: Needs to lock and update a specific row in table A and a specific row in table B atomically.
Operation B: Needs to lock and update a specific row in table B and a specific row in table C atomically.
-- Operation A
SELECT * FROM A WHERE id = :idA FOR UPDATE;
SELECT * FROM B WHERE id = :idB FOR UPDATE;
UPDATE A SET ... WHERE id = :idA;
UPDATE B SET ... WHERE id = :idB;
COMMIT;
-- Operation B
SELECT * FROM B WHERE id = :idB FOR UPDATE;
SELECT * FROM C WHERE id = :idC FOR UPDATE;
UPDATE B SET ... WHERE id = :idB;
UPDATE C SET ... WHERE id = :idC;
COMMIT;
Is there any data anomaly risk (e.g., inconsistent updates) in terms of consistency if multiple sessions run these operations concurrently? Let's say, Session A has already locked row A.id = :idA
, but before it locks B.id = :idB
, Session B manages to lock and fully update both B.id = :idB
and C.id = :idC
and perform Operation B successfully. Could this lead to any form of inconsistency, assuming both sessions eventually commit successfully? (Guess the isolation level is always read committed. I think if the level is serializable, I don't need to worry about data anomaly)
Is there a more reliable or recommended pattern to ensure both updates happen atomically across multiple tables?
You should consider if there is any relation between those tables/records . and if so, just join the queries into a single query: that is . if there is ANY relation to them at all, you should be able to query BOTH tables in a single query .. and lock all rows .. both tables . in a single query:
for example .. something like this:
select *
from tabA a,
tabB b
where a.id = b.id
and a.id = 234
for update;
This avoids any chance of anything happening in between your separate steps.