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

> Warren Vanichuk <pyber@street-light.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