Re: SELECT blocks UPDATE

twoflower <standa(dot)kurik(at)gmail(dot)com> writes:
> Tom Lane-2 wrote
>> So either the SELECT is a SELECT FOR UPDATE, or it's part of a transaction
>> that's done datachanges in the past.

> If these are the only two explanations, it must be the latter then. What I
> still don't understand - these two statements are part of the same
> transaction (because the lock query joins on the lock's transaction id), so
> it looks like a transaction blocking itself. As I think about it now, it
> does not even make sense to me /why/ the lock query joins on the
> lock.transactionid - I would expect two locks will mostly conflict with each
> other when they are executed within /different/ transactions.

No, you're misunderstanding the meaning of the column. virtualtransaction
and pid are what identify the session that is holding/awaiting a lock.
A transactionid is something that a lock can be taken on, and if you have
a session that is awaiting such a lock then it's waiting for the session
doing that transaction to end its transaction.

The use of such locks in Postgres is pretty narrow, though. AFAIR this
would only happen in the context of a session waiting to acquire a row
lock on a row that it's trying to update/delete/lock for update.

regards, tom lane