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_.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.