exclusiveness of multiple table locks for a single transaction

Question

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

Answer 1

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.