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 A98CED1C9C7; Mon, 5 Apr 2004 17:24:08 +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 72357-01; Mon, 5 Apr 2004 14:24:36 -0300 (ADT) Received: from ganymede.hub.org (u46n208.hfx.eastlink.ca [24.222.46.208]) by svr1.postgresql.org (Postfix) with ESMTP id 5A62ED1C995; Mon, 5 Apr 2004 14:24:05 -0300 (ADT) Received: by ganymede.hub.org (Postfix, from userid 1000) id A84743F34F; Mon, 5 Apr 2004 14:24:54 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by ganymede.hub.org (Postfix) with ESMTP id 96F973F2DA; Mon, 5 Apr 2004 14:24:54 -0300 (ADT) Date: Mon, 5 Apr 2004 14:24:54 -0300 (ADT) From: "Marc G. Fournier" X-X-Sender: scrappy@ganymede.hub.org To: Tom Lane Cc: "Marc G. Fournier" , pgsql-admin@postgresql.org Subject: Re: Frequent 'deadlock detected' in 7.4 ... or just my bad In-Reply-To: <11570.1081178034@sss.pgh.pa.us> Message-ID: <20040405141728.C79409@ganymede.hub.org> References: <20040405114031.N79409@ganymede.hub.org> <11570.1081178034@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/47 X-Sequence-Number: 13020 On Mon, 5 Apr 2004, Tom Lane wrote: > "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. Actually, unless I'm mistaken about how hashes work in perl, the update order for all servers is the same ... basically what happens is: 1. a traffic table is read in, and loaded into a hash table that is ordered by company_id, ip_id and port: $traffic{$ip_rec{$ip}{'company_id'}}{$ip_id}{$port} += $bytes1 + $bytes2; 2. a foreach loop is run on that resultant list to do the updates to the database: foreach $company_id ( keys %traffic ) { foreach $ip_id ( keys %{$traffic{$company_id}} ) { foreach $port ( keys %{$traffic{$company_id}{$ip_id}} ) { and the updates are done based on those 3 values, plus the byte value of $traffic{$company_id}{$ip_id}{$port} ... Now, my first mistake may be that I'm mis-assuming that the hashes will be read in a sorted order ... ? If this is the case, though, then sort order shouldn't be an issue, as all servers would be sorted the same way ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664