Re: Deadlock issues (was: Re: [GENERAL] selecting a random record)

> Warren Vanichuk <pyber(at)street-light(dot)com> writes:
> > I have a smallish sized database that's getting alot of update transactions
> > to it. It's been running fine over the past several weeks, but suddenly I'm
> > starting to see :
>
> > NOTICE: Deadlock detected -- See the lock(l) manual page for a possible cause.
>
> You've probably added another function that updates the same rows in a
> different, conflicting order. For example, suppose you have two
> transactions, one of which does
>
> UPDATE table SET f1 = 42 WHERE id = 'foo';
>
> while the other is doing
>
> UPDATE table SET f1 = 23 WHERE id = 'bar';
>
> This is fine, but now suppose the first one does
>
> UPDATE table SET f2 = 11 WHERE id = 'bar';
>
> It's got to wait to see if the second one commits or not, to know which
> version of the 'bar' row is relevant and should be updated. Finally,
> suppose the second transaction does
>
> UPDATE table SET f2 = 22 WHERE id = 'foo';
>
> Now you have two transactions waiting for each other --- ie, deadlock.

Hrmm. There are only two functions that affect those tables. The
add_impressions does a :

update table set f1+1 where f3 = 'foo' and f4 = 'bar' and f5 = 'baz';

The add_click does a :

update table set f2+1 where f3 = 'foo' and f4 = 'bar' and f5 = 'baz';

All transactions are inside a BEGIN/COMMIT block.

You are saying that will be cause the issue that I have been seeing? If so,
how exactly does one work around this, given that I need real-time or near
real-time reporting of the data collected by these functions...

Sincerely, Warren