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

Re: row-level deadlock problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Kamil Kaczkowski <kamil(at)kamil(dot)eisp(dot)pl>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: row-level deadlock problem
Date: 2004-11-27 00:59:56
Message-ID: 20866.1101517196@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Kamil Kaczkowski <kamil(at)kamil(dot)eisp(dot)pl> writes:
>> Another possible explanation is if the UPDATE
>> command can update more than one row --- in that case different backends
>> might happen to reach the target rows in different orders.

> Yes, this UPDATE changes several rows, I didn't know this can be a
> problem.

> My understanding was that row-level lock at UPDATE statement is somehow
> atomic and it locks all rows matched at once.

Nope.

> But what's the solution? How can I force UPDATEs to lock rows in the same
> order? There's no ORDER BY clause for UPDATE.

Change things so you don't need to update more than one row per query,
perhaps?  The lack of any primary key on that table was already pretty
disturbing from a database-theory point of view.  Maybe you should
rethink the table layout.

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Ennio-SrDate: 2004-11-27 01:36:05
Subject: Re: Can you help solve restore problem?
Previous:From: Adrian KlaverDate: 2004-11-27 00:59:51
Subject: Re: pg_dump and languages

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