0

I see in Postgres docs:

Two transactions cannot hold locks of conflicting modes on the same table at the same time

During maintenance (schema migration) we saw cases when we can't ALTER TABLE because of hanging SELECT in transaction (pg_locks + pg_stat_activity).

In order to proceed migration we revoke CONNECT from "rogue" users and kicked all their sessions.

Is that correct that:

BEGIN;
SELECT ...;

will block ALTER TABLE until COMMIT or ROLLBACK?

  • 1
    Totally, that's what the docs tell you, too. And the first lock will be there until it's released (of these locks that means end of the transaction), the 'stronger' ones won't kick the weaker ones. – dezso Oct 24 at 8:44
  • Thanks for stronger/weaker. That's what I misunderstood without prior experience. – gavenkoa Oct 24 at 9:08
2

You got it right.

This is how PostgreSQL prevents conflicts: you cannot drop or modify a table while someone is using it.

0

yes , in Mysql SELECT itself is considered a transaction. so if one transaction is on a table , you can not get meta data lock for alter.

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.