From: | Rod Taylor <rbt(at)rbt(dot)ca> |
---|---|
To: | Tzvetan Tzankov <ce(at)noxis(dot)net> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Deadlock problem |
Date: | 2003-04-23 18:28:19 |
Message-ID: | 1051122498.38778.64.camel@jester |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> $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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2003-04-23 18:30:32 | Re: Invoice Numbers |
Previous Message | Josh Berkus | 2003-04-23 18:23:38 | Why doesn't EXPLAIN ANALYZE show UPDATE step? |