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.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
