X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id EE708D1ED9B; Mon, 5 Apr 2004 15:13:21 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 06202-09; Mon, 5 Apr 2004 12:13:49 -0300 (ADT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 91F0BD1ED6A; Mon, 5 Apr 2004 12:13:18 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i35FDtQK011571; Mon, 5 Apr 2004 11:13:55 -0400 (EDT) To: "Marc G. Fournier" Cc: pgsql-admin@postgresql.org Subject: Re: Frequent 'deadlock detected' in 7.4 ... or just my bad code? In-reply-to: <20040405114031.N79409@ganymede.hub.org> References: <20040405114031.N79409@ganymede.hub.org> Comments: In-reply-to "Marc G. Fournier" message dated "Mon, 05 Apr 2004 11:53:05 -0300" Date: Mon, 05 Apr 2004 11:13:54 -0400 Message-ID: <11570.1081178034@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/45 X-Sequence-Number: 13018 "Marc G. Fournier" writes: > Now, the scripts are wrap'd in a BEGIN/END ... if a file fails to be > loaded, I want the whole thing to rollback ... the deadlock itself, I'm > presuming, is because two servers are trying to update the same > $ip_id/$port/$company_id record, at the same time ... Actually, the problem is more likely that two servers try to update two different rows in opposite orders. It's not possible to deadlock when only one lock is involved. You could work around this by ensuring that all sessions update rows in a consistent order; for instance, at the beginning of a transaction sort your intended updates by primary key and then apply in that order. regards, tom lane