0

Consider an api that performs writes to multiple tables:

BEGIN TRANSACTION;
  UPDATE ... TABLE1 ... VALUES ...;
  UPDATE ... TABLE2 ... VALUES ...;
  UPDATE ... TABLE3 ... VALUES ...;
  UPDATE ... TABLE4 ... VALUES ...;
  UPDATE ... TABLE5 ... VALUES ...;
COMMIT;
...

How does the database manage concurrency and locking in for concurrent api requests that perform this query on the database.

My point is, since we try to update 5 tables in a transaction, can this happen?:

API_REQUEST1 -> acquire lock for the row A1 for TABLE1

API_REQUEST2 -> acquire lock for the row B1 for TABLE2 
API_REQUEST1 -> acquire lock for the row B1 for TABLE2 // API_REQUEST1 does get to access this row and waits
API_REQUEST2 -> acquire lock for the row A1 for TABLE1 // API_REQUEST2 does get to access this row and waits

API_REQUEST1 -> acquire lock for the row C1 for TABLE3
API_REQUEST1 -> acquire lock for the row D1 for TABLE4

API_REQUEST2 -> acquire lock for the row E1 for TABLE5
... // and similarly forms a deadlockish situation
  

or does each api_request get processed exclusively, like:

//API_REQUEST2 arrived a bit late, so it waits
API_REQUEST1 -> acquire lock for the row A1 for TABLE1
API_REQUEST1 -> acquire lock for the row B1 for TABLE1
API_REQUEST1 -> acquire lock for the row C1 for TABLE1
API_REQUEST1 -> acquire lock for the row D1 for TABLE1
API_REQUEST1 -> acquire lock for the row E1 for TABLE1
2
  • Row B1 for TABLE2 and row B1 for TABLE1 are different rows, so why would API_REQUEST1 block at that point?
    – jjanes
    Oct 12, 2022 at 0:35
  • thanks for pointing out, updated my question
    – juztcode
    Oct 12, 2022 at 6:16

1 Answer 1

2

The locks process at the row level. Your example would deadlock after the 4th line, and PostgreSQL would randomly abort one transaction to allow the other to complete.

To avoid this, ensure your queries lock the rows in the same order.

Instead of

API_REQUEST1 -> TABLE1:A1 then TABLE2:B1
API_REQUEST2 -> TABLE2:B1 then TABLE1:A1

do

API_REQUEST1 -> TABLE1:A1 then TABLE2:B1
API_REQUEST2 -> TABLE1:A1 then TABLE2:B1

This way, API_REQUEST2 blocks on the first row and waits for API_REQUEST1 to complete.

7
  • however in parallel requests driven by api, ensuring this would be difficult
    – juztcode
    Oct 12, 2022 at 4:11
  • It's not a problem with easy solutions. The manual explains the other tools available. Also can you address jjanes's comment, editing the question if needed? I may have misinterpreted the data in your question.
    – dwhitemv
    Oct 12, 2022 at 5:03
  • I actually corrected the question, my intention was to show a deadlock
    – juztcode
    Oct 12, 2022 at 6:16
  • 1
    The behavior is independent of the transaction isolation level, otherwise good answer. Oct 12, 2022 at 9:50
  • @juztcode "requests are driven by API": that is no excuse. You use the API so you control which statements are issued. Lock objects in the same order always to avoid deadlocks. Oct 12, 2022 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.