Обсуждение: Postgres Escalating Lock based on Blocked Stmts
We have an interesting locking scenario that seems like a bug but I’d like you guys to weigh in…
To reproduce the scenario:
- Start with a table in a schema that executes a Read Committed Select that may take some time.
- 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).
- 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.
- 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.
Current version of Postgres that we are using is v11.2
Eric Alders
Senior Software Architect
Zelis
7007 College Blvd, Suite 650
Overland Park, KS 66211
Confidentiality Notice
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