Re: 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.

regards, tom lane