Обсуждение: Can a SELECT block?

Hi all.

I'm trying to track down a deadlock problem caused by some automatically
generated SQL code. It seems I'm deadlocking on a table that is actually
quite rarely modified. I'm unsure, but it appears that maybe something is
blocking on a SELECT call.

Under what situations can a SELECT block?

I'm assuming that a simple INSERT probably cannot block anywhere.

Under what situations, aside from two UPDATEs to the same row, can an UPDATE
block?

I'd appreciate any time. To give some details, it appears that my
application server (Orion) is holding connections to the database, some of
which are not in transactions, and some of which are "primed" for
connections (meaning that a BEGIN is always issued, and waits till
transactional work needs to be done, at which point it issues a COMMIT and
then another BEGIN and waits again). It seems to always issue the command
"SET TRANSACTION ISOLATION LEVEL READ COMMITTED" after each BEGIN.

I'd appreciate any help that anyone can give.


Thanks.


Gerald.

"Gerald Gutierrez" <gutz@kalador.com> writes:
> Under what situations can a SELECT block?

If the table is locked with an exclusive lock.  See
http://www.postgresql.org/devel-corner/docs/postgres/locking-tables.html

> I'd appreciate any time. To give some details, it appears that my
> application server (Orion) is holding connections to the database, some of
> which are not in transactions, and some of which are "primed" for
> connections (meaning that a BEGIN is always issued, and waits till
> transactional work needs to be done, at which point it issues a COMMIT and
> then another BEGIN and waits again). It seems to always issue the command
> "SET TRANSACTION ISOLATION LEVEL READ COMMITTED" after each BEGIN.

This would be somewhat annoying for VACUUM, which can only clean out
tuples that went away before the oldest open transaction started.
But offhand I think it does not matter for anything else.
        regards, tom lane