Thread: Postgres Escalating Lock based on Blocked Stmts

This email and the attachments may contain information which is privileged and/or confidential and is intended for the business and/or confidential use of the recipient only. Such information may be protected by applicable State and/or Federal laws from disclosure or unauthorized use. If you are not the intended recipient, you are hereby notified that any disclosure is strictly prohibited. If you have received this email in error, please contact the sender immediately.

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