From: Rod Taylor <rbt@rbt.ca>
To: Tzvetan Tzankov <ce@noxis.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Deadlock problem
Date: 23 Apr 2003 14:28:19 -0400
Message-ID: <1051122498.38778.64.camel@jester> (raw)
In-Reply-To: <b7of5s$1pr7$1@news.hub.org>
References: <b7of5s$1pr7$1@news.hub.org>
> $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@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.ascAttachments:
signature.asc (application/pgp-signature, 186B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rbt@rbt.ca, ce@noxis.net
Subject: Re: Deadlock problem
In-Reply-To: <1051122498.38778.64.camel@jester>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox