Skip site navigation (1) Skip section navigation (2)

SELECT FOR UPDATE with ORDER BY to avoid row-level deadlock?

From: Panther <panther-x(at)freemail(dot)hu>
To: pgsql-general(at)postgresql(dot)org
Subject: SELECT FOR UPDATE with ORDER BY to avoid row-level deadlock?
Date: 2007-01-30 06:07:16
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general

My problem is that if I try to update more than one row in a table like
> UPDATE mytable SET something = 84 WHERE not_unique_col = 41;
in two concurrent transactions, it can result in a deadlock if the two 
UPDATEs visit the rows in a different order.
The same applies, if I try to
> SELECT * FROM mytable WHERE not_unique_col = 41 FOR UPDATE;

But what if I try like
> SELECT * FROM mytable
> WHERE not_unique_col = 41 ORDER BY pri_key ASC FOR UPDATE;
and do the UPDATE after this? It should never lead to a deadlock, 
assuming the rows selected FOR UPDATE are locked in the order as 
they are returned.
But is that true? Are the rows selected FOR UPDATE locked in the same 
order as they are returned (as specified in ORDER BY)?

I'm not quite sure (though I tested it on a small table and it looked 
fine), because I (or should I say Google) could not find even one page 
on where this row-level deadlock situation had been 
solved... I could only find Tom Lane's post, where he admitted that this 
can lead to a deadlock:
I don't believe that no one thought of this solution before, so there 
must be something wrong with it... :)


Új év - új állás? Mérnöki, értékesítői, asszisztensi, 
pénzügyi és IT állások a!,1,6022,135079,205796/click.prm


pgsql-general by date

Next:From: A. KretschmerDate: 2007-01-30 06:32:11
Subject: Re: SQL to get a table columns comments?
Previous:From: Michael FuhrDate: 2007-01-30 06:02:49
Subject: Re: encode, lower and 0x8a

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group