Обсуждение: [GENERAL] SELECT and RowExclusiveLock
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".
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
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