Re: SELECT blocks UPDATE

On 08/13/2015 06:39 AM, twoflower wrote:
> Hello,
>
> 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. 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;

What is the output of the above?

>
>
>
> is this
>
> *Blocking statement*: SELECT tmtranslat0_.id as id164_0_, tmtranslat1_.id as id101_1_, tmlanguage2_.id as id73_2_, ... FROM "TRANSLATION" ...

What is the entire statement for above?

Is it part of transaction?

>
> *Blocked statement*: UPDATE "TRANSLATION" SET fk_assignment_queue_item = 1000211 WHERE id IN (47032216)

>
>
>
> I don't remember ever having problems with things like this. I am not
> even issuing SQL queries in parallel from my application (the execution
> is single-threaded). Now my application is stuck on the UPDATE statement.
>
> 1) How is it possible that these two statements block?
> 2) What can I do about it?
>
> Thank you.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com