SELECT and RowExclusiveLock

On 04/07/2017 01:25 PM, Tim Nelson wrote:

New to Postgres and I have never seen this condition. We are getting
test applications hanging on SELECT statements with a RowExclusiveLock.
How can a SELECT cause a RowExclusiveLock?

relname | pid | mode | granted
--------------------------+-------+------------------+---------
sales_transaction_detail | 392 | RowExclusiveLock | t
sales_transaction_detail | 19077 | RowExclusiveLock | t
sales_transaction_header | 32661 | RowExclusiveLock | t
sales_transaction_header | 392 | RowExclusiveLock | t
sales_transaction_header | 19077 | RowExclusiveLock | t

pid | age | usename |
query
-------+------------------+----------+----------------------------------------------------------------------------------
32661 | -07:42:39.289945 | postgres | UPDATE "sales_transaction_header"
SET "create_datetime" = '2017-04-07T02:20:39.4
19077 | -07:42:15.976288 | postgres | SELECT "price_benefit"."id",
"price_benefit"."create_datetime", "price_benefit".
392 | -07:01:44.121346 | postgres | SELECT "price_benefit"."id",
"price_benefit"."create_datetime", "price_benefit".

It would help to have:

1) Schema definitions for sales_transaction_detail and
sales_transaction_header

2) The complete queries.

3) And just for grins the Postgres version.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Apr 7, 2017 at 1:25 PM, Tim Nelson <timnels@gmail.com> wrote:

New to Postgres and I have never seen this condition. We are getting test
applications hanging on SELECT statements with a RowExclusiveLock. How can
a SELECT cause a RowExclusiveLock?

Two common ways:

SELECT ... FOR UPDATE;

SELECT function_that_performs_updates();

There are some more moving parts here, especially transactions, that may be
coming into play. Its hard to say more given the limited and partial
detail. Version and a more complete pg_stat_activity query would be
helpful.

David J.

Tim Nelson <timnels@gmail.com> writes:

New to Postgres and I have never seen this condition. We are getting test
applications hanging on SELECT statements with a RowExclusiveLock. How can
a SELECT cause a RowExclusiveLock?

relname | pid | mode | granted
--------------------------+-------+------------------+---------
sales_transaction_detail | 392 | RowExclusiveLock | t
sales_transaction_detail | 19077 | RowExclusiveLock | t
sales_transaction_header | 32661 | RowExclusiveLock | t
sales_transaction_header | 392 | RowExclusiveLock | t
sales_transaction_header | 19077 | RowExclusiveLock | t

Hm, all those entries are showing granted = t, implying that they are
not blocked. I think you are mis-querying pg_locks or mis-interpreting
the results.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general