Re: SELECT blocks UPDATE

twoflower <standa(dot)kurik(at)gmail(dot)com> writes:
> if I am reading the documentation on explicit locking
> <http://www.postgresql.org/docs/current/interactive/explicit-locking.html#LOCKING-TABLES>
> correctly, SELECT should never conflict with UPDATE.

Pure SELECT, I would think not. But is it really a SELECT FOR UPDATE?
That locks individual rows (not the whole table) so it can conflict
against an UPDATE on the same row(s).

> However, what I am
> observing as a result of this monitoring query:

> SELECT bl.pid AS blocked_pid,
> a.usename AS blocked_user,
> ka.query AS blocking_statement,
> now() - ka.query_start AS blocking_duration,
> kl.pid AS blocking_pid,
> ka.usename AS blocking_user,
> a.query AS blocked_statement,
> now() - a.query_start AS blocked_duration
> FROM pg_catalog.pg_locks bl
> JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
> JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND
> kl.pid != bl.pid
> JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
> WHERE NOT bl.granted;

Hmm. In any remotely modern version of PG, a pure SELECT transaction
wouldn't even *have* a transactionid. So either the SELECT is a
SELECT FOR UPDATE, or it's part of a transaction that's done data
changes in the past. In that case the blockage could have something to do
with previously-acquired locks.

It's also possible that you're misreading the output of pg_locks.

> 1) How is it possible that these two statements block?
> 2) What can I do about it?

EINSUFFICIENTDATA. You need to tell us more about the context,
and show us the actual pg_locks query output. It might also be
relevant just which PG version this is.

regards, tom lane