Обсуждение: SELECT blocks UPDATE

Hello,

if I am reading the documentation on explicit locking 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;

is this

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

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.

---

View this message in context: SELECT blocks UPDATE
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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@aklaver.com

twoflower <standa.kurik@gmail.com> writes:
> 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.

Pure SELECT, I would think not.  But is it really a SELECT FOR UPDATE?
That locks individual rows (not the whole table) so it can conflict
against an UPDATE on the same row(s).

> 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;

Hmm.  In any remotely modern version of PG, a pure SELECT transaction
wouldn't even *have* a transactionid.  So either the SELECT is a
SELECT FOR UPDATE, or it's part of a transaction that's done data
changes in the past.  In that case the blockage could have something to do
with previously-acquired locks.

It's also possible that you're misreading the output of pg_locks.

> 1) How is it possible that these two statements block?
> 2) What can I do about it?

EINSUFFICIENTDATA.  You need to tell us more about the context,
and show us the actual pg_locks query output.  It might also be
relevant just which PG version this is.

            regards, tom lane

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.

Further observation: Now I managed to get rid of the blocking. I am not sure
if you are familiar with the NHibernate ORM, but it has a concept of a
stateful and stateless sessions. Session holds a connection to the database
and transaction is created on a particular session. In this case, 'begin
transaction' and the SELECT statement was executed in the context of the
stateful session, while the UPDATE was executed on a stateless session. I am
not sure how this situation manifests on Postgres but since the 'blocked'
and 'blocking' lock apparently belong to the same transaction, it does not
look like it should matter, except it does.



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

On 08/13/2015 02:14 PM, twoflower wrote:
> Further observation: Now I managed to get rid of the blocking. I am not sure
> if you are familiar with the NHibernate ORM, but it has a concept of a
> stateful and stateless sessions. Session holds a connection to the database
> and transaction is created on a particular session. In this case, 'begin
> transaction' and the SELECT statement was executed in the context of the
> stateful session, while the UPDATE was executed on a stateless session. I am
> not sure how this situation manifests on Postgres but since the 'blocked'
> and 'blocking' lock apparently belong to the same transaction, it does not
> look like it should matter, except it does.

I would crank up the Postgres logging:

http://www.postgresql.org/docs/9.4/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

At least set log_statement to all, to see what is actually being sent to
the server from the ORM during the above.

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


--
Adrian Klaver
adrian.klaver@aklaver.com

twoflower <standa.kurik@gmail.com> writes:
> 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.

No, you're misunderstanding the meaning of the column.  virtualtransaction
and pid are what identify the session that is holding/awaiting a lock.
A transactionid is something that a lock can be taken on, and if you have
a session that is awaiting such a lock then it's waiting for the session
doing that transaction to end its transaction.

The use of such locks in Postgres is pretty narrow, though.  AFAIR this
would only happen in the context of a session waiting to acquire a row
lock on a row that it's trying to update/delete/lock for update.

            regards, tom lane