Re: SELECT blocks UPDATE

The Postgres version is 9.3.9.

The actual output of the lock query is (I added *locktype* and *mode*
columns from the *pg_locks* table)

*blocked_pid*: 7574
*blocked_statement*: UPDATE "TRANSLATION" SET fk_assignment_queue_item =
1009184 WHERE id IN (47049861)
*blocked_locktype*: transactionid
*blocked_mode*: ShareLock
*blocked_duration*: 00:35:01.81106
*blocking_pid*: 7569
*blocking_statement*: select tmtranslat0_.id as id164_0_, tmtranslat1_.id as
id101_1_, tmlanguage2_.id as id73_2_, tmtranslat0_.status as status164_0_,
...
*blocking_locktype*: transactionid
*blocking_mode*: ExclusiveLock
*blocking_duration*: 00:35:03.017109

User names are irelevant, so I omitted that. Also the *blocking_statement*
is actually cut off even before the FROM clause, but there is only one
SELECT query issued at that moment which matches the start:

select from "TRANSLATION" tmtranslat0_left outer join "TRANSLATION_UNIT"
tmtranslat1_ on tmtranslat0_.fk_id_translation_unit = tmtranslat1_.idleft
outer join "LANGUAGE" tmlanguage2_ on tmtranslat0_.fk_id_language =
tmlanguage2_.idwhere tmtranslat0_.id in (47049860, 47049861, 47049862)order
by tmtranslat0_.id asc

I also suspected a SELECT FOR UPDATE query, but it's not the case. Also, I
don't use these at all in the application.

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.

As for other context, I fail to see how this situation is special or
different from any other...Is there any pattern I should be looking for?

--
View this message in context: http://postgresql.nabble.com/SELECT-blocks-UPDATE-tp5862040p5862091.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.