In what order does postgresql handle queries which have conflicting locks?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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;