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_.id
left outer join "LANGUAGE" tmlanguage2_ on tmtranslat0_.fk_id_language = tmlanguage2_.id
where 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 data changes 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:
Re: SELECT blocks UPDATE Sent from the
PostgreSQL - general mailing list archive at Nabble.com.