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 B6630D1C4C3 for ; Mon, 5 Apr 2004 14:52:19 +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 96946-08 for ; Mon, 5 Apr 2004 11:52:47 -0300 (ADT) Received: from ganymede.hub.org (u46n208.hfx.eastlink.ca [24.222.46.208]) by svr1.postgresql.org (Postfix) with ESMTP id 82BBBD1B54E for ; Mon, 5 Apr 2004 11:52:17 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id C3F67456B0; Mon, 5 Apr 2004 11:53:05 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id B7B1E45696 for ; Mon, 5 Apr 2004 11:53:05 -0300 (ADT) Date: Mon, 5 Apr 2004 11:53:05 -0300 (ADT) From: "Marc G. Fournier" X-X-Sender: scrappy@ganymede.hub.org To: pgsql-admin@postgresql.org Subject: Frequent 'deadlock detected' in 7.4 ... or just my bad code? Message-ID: <20040405114031.N79409@ganymede.hub.org> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/43 X-Sequence-Number: 13016 G'day ... I've got a script that runs on all the servers that dump's IP traffic data to a 7.4 database ... they all run at the same time, but I'm starting to get the following on a reasonably regular basis: ERROR: deadlock detected at /usr/local/abin/ipaudit2ams.pl line 175. The code that is causing it, from the times I've been able to catch it, is a simple update to the same table: $upd->execute( $traffic{$company_id}{$ip_id}{$port}, $company_id, $ip_id, $date ) || die $upd->errstr; 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 ... Now, reading the DEADLOCKS section at: http://www.postgresql.org/docs/7.4/static/explicit-locking.html This is to be expected ... but, other then breaking the transaction itself into smaller chunks, or staggering the scripts run times ... is there something I'm overlooking to eliminate this? I could increase the deadlock timeout, as an option as well ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664