Re: Postgres Escalating Lock based on Blocked Stmts

On 15/06/2021 18:47, Eric Alders wrote:
> We have an interesting locking scenario that seems like a bug but I’d
> like you guys to weigh in…
>
> To reproduce the scenario:
>
> 1. Start with a table in a schema that executes a Read Committed Select
> that may take some time.
> 2. Issue a DROP SCHEMA stmt to the schema where the SELECT is taking
> place. (Because the DROP requires Exclusive lock on the schema it
> blocks as expected until the SELECT is complete).
> 3. Issue a second SELECT query on the same schema table. In this
> scenario the stmt is blocked by the DROP Schema stmt which is still
> blocked by the original SELECT. This seems odd as Postgres has
> elevated the lock based on a blocked DROP stmt instead of the
> current executing SELECT stmt. In fact, every stmt is blocked to
> anything trying to read from the schema.

Yeah, that is expected. The SELECTs queue up behind the DROP. If we let
the SELECTs "jump the queue", the DROP might never have a chance to
acquire the exclusive lock. You could imagine a different system with a
different tradeoff, but that's the way it works in PostgreSQL.

> 4. If you manually cancel the DROP stmt to allow the second SELECT to
> continue the second SELECT query hangs and does not return. A manual
> restart of the Postgres server must happen to fix the issue.

That is unexpected. I could not reproduce that on my laptop with
PostgreSQL v11.12. The second SELECT gets unblocked and runs as soon as
the DROP is terminated.

> Current version of Postgres that we are using is v11.2

The latest v11 minor version is v11.12, so you should upgrade. I don't
know if there has been any fixes that might explain the issue you're
seeing, but that should be the first thing to try, and there has been a
ton of other fixes in any case.

- Heikki