X-Original-To: pgsql-sql@postgresql.org
Received: from spampd.localdomain (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 59697475F16
	for <pgsql-sql@postgresql.org>; Wed, 23 Apr 2003 14:28:23 -0400 (EDT)
Received: from nat.inquent.com (unknown [216.208.117.7])
	by postgresql.org (Postfix) with ESMTP id 7B24F474E42
	for <pgsql-sql@postgresql.org>; Wed, 23 Apr 2003 14:28:21 -0400 (EDT)
Received: from [127.0.0.1] (localhost [127.0.0.1])
	by nat.inquent.com (8.12.8p1/8.12.8) with ESMTP id h3NISJDl045736;
	Wed, 23 Apr 2003 14:28:20 -0400 (EDT) (envelope-from rbt@rbt.ca)
Subject: Re: Deadlock problem
From: Rod Taylor <rbt@rbt.ca>
To: Tzvetan Tzankov <ce@noxis.net>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <b7of5s$1pr7$1@news.hub.org>
References: <b7of5s$1pr7$1@news.hub.org>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-OWo/JbXBAfemRm4aNmos"
Organization: 
Message-Id: <1051122498.38778.64.camel@jester>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.2.4 
Date: 23 Apr 2003 14:28:19 -0400
X-Spam-Status: No, hits=-38.6 required=5.0
	tests=BAYES_01,IN_REP_TO,PGP_SIGNATURE_2,QUOTED_EMAIL_TEXT,
	REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_XIMIAN
	autolearn=ham	version=2.50
X-Spam-Level: 
X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp)
X-Archive-Number: 200304/347
X-Sequence-Number: 13048

--=-OWo/JbXBAfemRm4aNmos
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable


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


Looks like you take the results of this query and do work on each row.=20
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=20
> stuck)

Does PostgreSQL tell you that a deadlock occurred and kill off an
offending transaction (after about 10 seconds or so -- check the logs)?=20
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?

--=20
Rod Taylor <rbt@rbt.ca>

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

--=-OWo/JbXBAfemRm4aNmos
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (FreeBSD)

iD8DBQA+pttC6DETLow6vwwRAuFyAJ9lf4ZNmrjYRzhaGql5C8MgG/70EwCfRyaV
NPqy/OVTpXFNg6GITOmloig=
=yn1c
-----END PGP SIGNATURE-----

--=-OWo/JbXBAfemRm4aNmos--