Re: Deadlock problem

> $query = "SELECT a.* FROM advert AS a, $service AS c WHERE a.id =
> c.advert AND a.approved AND c.payed AND ".$query." AND NOT c.paused AND
> c.start <= CURRENT_DATE ORDER BY random() LIMIT $limit";

Looks like you take the results of this query and do work on each row.
If LIMIT > 1, then you could get a deadlock.

SESSION 1: BEGIN;
SESSION 1: SELECT ...
SESSION 1: UPDATE ... WHERE 3
SESSION 2: BEGIN;
SESSION 2: SELECT ...
SESSION 2: UPDATE ... WHERE 4
SESSION 1: UPDATE ... WHERE 4
SESSION 2: UPDATE ... WHERE 3
<deadlock>

However, removing the PHP transaction would have eliminated that
possibility. The same could happen if you have 2 services in mixed
order. Fixing this can be done by putting the current select into a
subquery and ordering the results.

SELECT * FROM (<current query>) as tbl ORDER BY <columns>;

Another thing which may help is to lock the rows returned from the inner
select using FOR UPDATE.

> maybe it is not deadlock, but what else it is ? (hundred processes got
> stuck)

Does PostgreSQL tell you that a deadlock occurred and kill off an
offending transaction (after about 10 seconds or so -- check the logs)?
If not, it's probably not a deadlock in the database. But you may find
that a PHP process is not quiting for some reason and is holding a locks
in an idle and open transaction.

Are you using Persistent connections in PHP for the database?

--
Rod Taylor <rbt(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc