0

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?

  • Beside the point, but have you tried SELECT COUNT(DISTINCT listing_id) FROM apps... – Lennart Mar 10 at 4:24
  • Yes. That one takes 1,818 seconds. The one above takes 1,788 seconds, and then one that I wrote that joins it to another table and includes a correlated subquery actually returned in 93 seconds: select count(*) from listings where subscription_id = 1298 and listing_status = 'Active' and exists (select id from apps where apps.subscription_id = listings.subscription_id and apps.listing_id = listings.id) – AKWF Mar 10 at 4:57
  • An exclusive lock is typically taken by DDL statements. Maybe you forgot to commit an ALTER TABLE statement from a different session? – a_horse_with_no_name Mar 10 at 7:48
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.