CODEWITHSUNDEEP
postgresqlpostgresql-9.6
Oto Shavadze
Oto Shavadze

Reputation: 42773

Lock one table at update and another in subquery, which one will be locked first?

I have a query like this:

UPDATE table1 SET 
col = 'some value' 
WHERE id = X
RETURNING col1, (SELECT col2 FROM table2 WHERE id = table1.table2_id FOR UPDATE);

So, this query will lock both tables, table1 and table2, right? But which one will be locked first?

Upvotes: 1

Views: 234

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246788

The execution plan for the query will probably look like this:

                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Update on laurenz.table1
   Output: table1.col1, (SubPlan 1)
   ->  Index Scan using table1_pkey on laurenz.table1
         Output: table1.id, table1.table2_id, 'some value'::text, table1.col1, table1.ctid
         Index Cond: (table1.id = 42)
   SubPlan 1
     ->  LockRows
           Output: table2.col2, table2.ctid
           ->  Index Scan using table2_pkey on laurenz.table2
                 Output: table2.col2, table2.ctid
                 Index Cond: (table2.id = table1.table2_id)

That suggests that the row in table1 is locked first.

Looking into the code, I see that ExecUpdate first calls EvalPlanQual, where the updated tuple is locked, and only after that calls ExecProcessReturning where the RETURNING clause is processed.

So yes, the row in table1 is locked first.

So far, I have treated row locks, but there are also the ROW EXCLUSIVE locks on the tables themselves:

The tables are all locked in InitPlan in execMain.c, and it seems to me that again table1 will be locked before table2 here.

Upvotes: 1

Related Questions