I know PostgreSQL provides multiple lock modes for concurrency control. When I read through their documents, I cannot see why the
ACCESS EXCLUSIVE lock is necessary in any case. The only difference between an
EXCLUSIVE lock and
ACCESS EXCLUSIVE lock seems to be that
ACCESS EXCLUSIVE lock disallows reads from the table.
But isn't it a good thing that people are able to read? In what case should you prevent users from reading? In the documentation I see that usually
DROP TABLE, etc. will use
ACCESS EXCLUSIVE lock, but why? I was thinking that maybe when you drop a table the data is already partially gone when you are trying to read so the result will be corrupted. But then I realize since everything in Postgres is transactional, a
DROP TABLE should not take effect until commit. So my guess should be wrong.
I am still confused. Why is
ACCESS EXCLUSIVE lock needed again when you compared it to
EXCLUSIVE lock? My best guess is that replacing
ACCESS EXCLUSIVE lock with
EXCLUSIVE lock is not going to break PostgreSQL. It is just sometimes people do not want others to read. Did I miss something?