Re: Frequent 'deadlock detected' in 7.4 ... or just my bad

On Mon, 5 Apr 2004, Tom Lane wrote:

> "Marc G. Fournier" <scrappy(at)postgresql(dot)org> 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(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664