Re: Optimizing select count query which often takes over 10 seconds

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