PostgreSQL Performance – Why COUNT Obtains an ExclusiveLock

performancepostgresqlpostgresql-performance

And what does this mean for other CRUD operations going against that table?

I have this query (actually I'm trying to write it 9 ways from Sunday to get it to return in less than an hour):

SELECT COUNT(*)
FROM (SELECT DISTINCT listing_id 
      FROM apps where subscription_id = 1298) AS temp

It churns and churns. When I look at a process list for the server, I find eight records. One for the table and six for the indexes. And apparently one for the statement itself.

It says Locked Object and the object name, and AccessShareLock next to the table and indexes. Then there is something that says LockedTransaction and next to it it says ExclusiveLock.

What does this ExclusiveLock mean, and will it keep other CRUD operations from running against that table?

Best Answer

Every transaction is born owning an ExclusiveLock on itself.

For a read-only transaction, this will only block things like CREATE INDEX CONCURRENTLY. For DML transactions, this lock will be used to block other transactions trying to update or delete the same row, or insert conflicting rows.

Short answer

The following rows will be locked for update in the three cases:

a row with "ID" = :ID in table "A" and all rows in table "B"
a row with "ID" = :ID in table "B" and all rows in table "A"
a row with "ID" = :ID1 in table "A" and a row with "ID" = :ID2 in table "B"

The results are as one can expect after reading this statement in the documentation:

If a locking clause is applied to a view or sub-query, it affects all tables used in the view or sub-query.

How to test similar cases

Install the extension pgrowlocks..

create extension if not exists pgrowlocks;

Run two instances of psql. In the first one start a transaction and execute a query.

psql #1:

begin;

select *
from "AB"
where "AID" = 3 and "BID" = 103
for update;

You can see locked rows in the other instance of psql.

psql #2:

select * from pgrowlocks('"B"');

 locked_row | locker | multi |  xids   |     modes      |  pids
------------+--------+-------+---------+----------------+--------
 (0,4)      |  88577 | f     | {88577} | {"For Update"} | {8068}
(1 row)

Do not forget to close the transaction at the end (before a next test).

psql #1:

rollback;

The first column of the above result is ctid. You can select the original row with the query:

select *
from "B"
where ctid = '(0,4)';

Best Answer

Related Solutions

Postgresql – Why does PostgreSQL require a database connection

PostgreSQL Views – How SELECT FOR UPDATE Behaves with Views

Short answer

How to test similar cases

Related Question