Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Let's say that I have a long running query SELECT and this query holds a AccessShareLock on a table. While this query is being processed a new query which needs a AccessExclusiveLock on the table REINDEXDB is made. I suppose this query must wait the other query to finish, becuase they have conflicting locks. And while my REINDEXDB query is waiting for the SELECT query to finish, a second SELECT query is made. Now my question is

Which query will be serviced first? REINDEXDB or SELECT? Select query has been made later but it doesn't have a conflicting lock with the ongoing select query. So will they be processed in parallel? Or because REINDEXDB query has been made earlier will the second select query wait for REINDEXDB to finish?

share|improve this question

3 Answers 3

up vote 3 down vote accepted

I can't talk to postgresql directly. However, most rdbms in this situation would process the requests in the order they were received. That means a query can only execute if there is no other query that was submitted earlier and is either holding or waiting for a lock that is not compatible with the newly requested lock.

The reason is, that otherwise a query requesting an exclusive lock might never get executed ("starvation") if there is a high enough number/frequency of read requests coming in that keep sneaking by the waiting query.

share|improve this answer

As testing can easily confirm, the second SELECT has to wait for the REINDEX. Sebastian Meine's answer offers an explanation.

share|improve this answer

You can avoid the AccessExclusiveLock on the table when you know which index needs to be repaired. In this case you can use

CREATE INDEX CONCURRENTLY

which doesn't lock the table. Let's say your index is called my_index, then you go like this:

CREATE INDEX CONCURRENTLY my_index_new ON ...;
DROP INDEX my_index;
ALTER INDEX my_index_new RENAME TO my_index;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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