Transaction Isolation in Postgres
thenile.dev
Transaction Isolation in Postgres
1–10 of 27 posts
Re: Transaction Isolation in Postgres
#2One caveat to serializable transactions in Postgres is that ALL concurrent transactions must be running with the SERIALIZABLE isolation level to protect against serialization anomalies.
This is a bit jarring if you come from MSSQL, which implements the SERIALIZABLE isolation level using locks. In MSSQL, you can rest assured that a serializable transaction will not be affected by changes from other concurrent transactions, regardless of their isolation level.
In Postgres, you may have a set of transactions all participating in SERIALIZABLE isolation today, but tomorrow someone adds another script without the SERIALIZABLE isolation level, and now your protected paths are no longer isolated.
Re: Transaction Isolation in Postgres
#3One caveat to serializable transactions in Postgres is that ALL concurrent transactions must be running with the SERIALIZABLE isolation level to protect against serialization anomalies. This is a bit jarring if you come from MSSQL, which implements the SERIALIZABLE isolation level using locks. In MSSQL, you can rest assured that a serializable transaction will not be affected by changes from other concurrent transact…
Or from the other perspective of the trade-off: One caveat with MSSQL is that ALL concurrent transactions must pay the overhead if _some_ transactions need serializable guarantees?
Re: Transaction Isolation in Postgres
#4One caveat to serializable transactions in Postgres is that ALL concurrent transactions must be running with the SERIALIZABLE isolation level to protect against serialization anomalies. This is a bit jarring if you come from MSSQL, which implements the SERIALIZABLE isolation level using locks. In MSSQL, you can rest assured that a serializable transaction will not be affected by changes from other concurrent transact…
Oh that's super nasty, is it mentioned somewhere in the doc?
Is it the same for repeatable read?
Re: Transaction Isolation in Postgres
#5One caveat to serializable transactions in Postgres is that ALL concurrent transactions must be running with the SERIALIZABLE isolation level to protect against serialization anomalies. This is a bit jarring if you come from MSSQL, which implements the SERIALIZABLE isolation level using locks. In MSSQL, you can rest assured that a serializable transaction will not be affected by changes from other concurrent transact…
Oh that's super nasty, is it mentioned somewhere in the doc? Is it the same for repeatable read?
It is mentioned in the doc, but can be easy to mis-understand:
"If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization_failure error."
Note that it says nothing about the non-serializable transactions.
https://www.postgresql.org/docs/current/sql-set-transaction....
Re: Transaction Isolation in Postgres
#6This is literally one of those topics I have to come back and read time and time and time again every time I need it like 2 times a year. Maybe this article will finally make it stick.
Re: Transaction Isolation in Postgres
#7One caveat to serializable transactions in Postgres is that ALL concurrent transactions must be running with the SERIALIZABLE isolation level to protect against serialization anomalies. This is a bit jarring if you come from MSSQL, which implements the SERIALIZABLE isolation level using locks. In MSSQL, you can rest assured that a serializable transaction will not be affected by changes from other concurrent transact…
Or from the other perspective of the trade-off: One caveat with MSSQL is that ALL concurrent transactions must pay the overhead if _some_ transactions need serializable guarantees?
Only if they touch the same data. If they are touching disjoint sets of data then there is no overhead to be paid by non-SERIALIZABLE transactions.
Re: Transaction Isolation in Postgres
#8Earlier quoted context omitted.
Oh that's super nasty, is it mentioned somewhere in the doc? Is it the same for repeatable read?
It is mentioned in the doc, but can be easy to mis-understand: "If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization_failure error." Note that it says nothing about the non-serializable transactions. https://www.postgresql.or…
It is in the Wiki:
https://wiki.postgresql.org/wiki/Serializable
Any transaction which is run at a transaction isolation level other than SERIALIZABLE will not be affected by SSI. If you want to enforce business rules through SSI, all transactions should be run at the SERIALIZABLE transaction isolation level, and that should probably be set as the default.
I guess it is the same for all MVCC databases. They don't want to acquire a read lock just in case another transaction is in Serializable
Re: Transaction Isolation in Postgres
#9This is literally one of those topics I have to come back and read time and time and time again every time I need it like 2 times a year. Maybe this article will finally make it stick.
Possibly. I tried hard to have some memorable examples.
But I'll be honest: Every time I got review comments (or re-reviewed myself), it took a bit of time for my brain to warm up again into "transaction mode".
The big lesson may that concurrent transactions are pretty hard to reason about without external assistant like diagrams or test scenarios. I really like the system Postgres uses for transaction testing (AKA - deterministic simulation testing). Create scenario that match your business logic and then run them serially but with different ordering of statements and make sure the results are as you expect.
Re: Transaction Isolation in Postgres
#10Earlier quoted context omitted.
It is mentioned in the doc, but can be easy to mis-understand: "If a pattern of reads and writes among concurrent serializable transactions would create a situation which could not have occurred for any serial (one-at-a-time) execution of those transactions, one of them will be rolled back with a serialization_failure error." Note that it says nothing about the non-serializable transactions. https://www.postgresql.or…
It is in the Wiki: https://wiki.postgresql.org/wiki/Serializable Any transaction which is run at a transaction isolation level other than SERIALIZABLE will not be affected by SSI. If you want to enforce business rules through SSI, all transactions should be run at the SERIALIZABLE transaction isolation level, and that should probably be set as the default. I guess it is the same for all MVCC databases. They don't wan…
The OP mentioned that SQLServer does lock (but then, it doesn't use SSI - just SI).
The recommendation in PG docs to use a combination of SERIALIZABLE and READ ONLY transactions seems like a good one for read-heavy systems.