On 2/25/15 10:49 AM, Cenkar, Maciej wrote: > Given PostgreSQL 9.3.5 what is locking strategy when executing query > such as: > > UPDATE table SET some_col = some_val WHERE id IN (SELECT id FROM > expensive_query_with_joins). > > Is this starting to lock rows after it executed sub-select or is it > locking whole table and then executing select?
This statement will lock rows in the update table as they are returned from the subquery and modified - only a share lock will be held on the entire table from the beginning (that just keeps people from modifying the table while you are using it). If the subquery contains a group by, order by, or some other clause that requires all the returned rows to be examined as a whole then the row locks will happen very consecutively, otherwise they could happen over a longer period of time and the locks will be held for longer. > Is there any advantage in precomputing ids from nested select to run only > > UPDATE table SET some_col = some_val WHERE id IN (precomputed_values)? If your subquery is very expensive but returns a reasonable number of rows, then putting the results in a temp table and then updating from the temp table may allow locks to be held a shorter amount of time. If your subquery contains a group by, order by, etc. as mentioned above then I wouldn't bother. One other thing to mention - since the order of updates cannot be guaranteed I wouldn't run more than one update like this at the same time or you might get deadlocks. -- - David Steele da...@pgmasters.net
signature.asc
Description: OpenPGP digital signature
