Alexander Farber <alexander.farber@gmail.com> wrote:
> The cronjob gives me now occasionally:
>
> /* reset and then update medals count */
> update pref_users set medals = 0;
> psql:/home/afarber/bin/clean-database.sql:63: ERROR: deadlock detected
> DETAIL: Process 31072 waits for ShareLock on transaction 124735679; blocked by process 30368.
> Process 30368 waits for ShareLock on transaction 124735675; blocked by process 31072.
> HINT: See server log for query details.
> Any ideas please how to workaround?
Yeah, try this:
update pref_users set medals = 0 where medals <> 0;
:-)
That should significantly reduce the frequency of deadlocks;
however, IMO any application using a relational database should be
prepared to retry database transactions which fail with a
serialization error, and a deadlock is one form of that. The
standard SQLSTATE to look for is '40001' and in PostgreSQL you
should also check for '40P01'.
-Kevin
