deadlock detected, only selects (not select-for-update)

hi,

i got the following error-message:

ERROR: deadlock detected
DETAIL: Process 32618 waits for ShareLock on transaction 1137032034;
blocked by process 16136.
Process 16136 waits for ShareLock on transaction 1137045910;
blocked by process 32618.

(postgres 7.4 here)

i checked the mailing-list-archives, and by such problems people always
have inserts/updates or at least select-for-updates.

the strange thing is, that the code that apparently produces this error
message does not have any of these.. it's mostly like:

(please note, that i cannot reproduce this problem, and also, i am not
logging all the sql commands. so i cannot be 100% sure that it was
caused by the following code, but from other tests, these are the
commands that are executed when the error happens)

BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
now a lot of selects, but nothing complicated, all of the form of
"SELECT x FROM y WHERE a=b", with some simple joins, and SELECT DISTINCT
and ORDER BY.
END;

can any of these commands cause the mentioned deadlock?

(of course, there are other processes that write to the database at this
time, but the one that reported the deadlock was this one)

as i said, i cannot reproduce it, so it's still possible, that when the
error happened, for some strange reason, also some other sql-commands
were executed, but i do not think so.

thanks,
gabor

Gábor Farkas <gabor(at)nekomancer(dot)net> writes:

> BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> now a lot of selects, but nothing complicated, all of the form of "SELECT x
> FROM y WHERE a=b", with some simple joins, and SELECT DISTINCT and ORDER BY.
> END;
>
> can any of these commands cause the mentioned deadlock?

No, a plain select without a "FOR SHARE" or "FOR UPDATE" can't cause a
deadlock.

One thing to be aware of is that In 7.4 foreign keys can cause spurious
deadlocks. Two updates against different records in a table will lock the
referenced keys. If they lock the referenced keys in different orders then
it's possible for them to deaadlock. This is addressed in more recent versions
of PostgreSQL.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com