What is "the pre"?
Transaction Isolation in Postgres
11–20 of 27 posts
Re: Transaction Isolation in Postgres
#12One 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?
I have read the docs plenty of times, but it never stuck for me until I read the (free!) PostgreSQL 14 Internals ebook: https://postgrespro.com/community/books/internals
Quoted from Page 70:
If you use the Serializable level, it must be observed by all transactions of the application. When combined with other levels, Serializable behaves as Repeatable Read without any notice. So if you decide to use the Serializable level, it makes read sense to modify the default_transaction_isolation parameter value accordingly -- even though someone can still overwrite it by explicitly setting a different level.
I had a real "WTF?" moment when I read this the first time.
Re: Transaction Isolation in Postgres
#13This 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.
this! never sticks for long, and I like the way this article flows in explaining isolation levels. definitely bookmarking it to get back to it later when needed.
Re: Transaction Isolation in Postgres
#14One 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…
We use Sybase SQLAnywhere at work, which also implements SERIALIZABLE using locks. Naive me thought that meant a lock on the table, but no, it locks all the rows... Not great for a table with many rows!
We were essentially trying to avoid inserting the same value twice, so we ditched SERIALIZABLE and instead added a unique index along with a retrying loop on the client side.
Re: Transaction Isolation in Postgres
#15> For reasons that should be obvious to anyone with a bank account, you really really want both updates to happen, or neither. This is what atomicity guarantees - that the entire transaction will either succeed or fail as a single unit.
So, I understand why this example feels particularly illustrative of the value of transactions, many-if-not-most financial "transactions" can't practically rely on this kind of atomicity for the kind of financial operation depicted.
While it may seem like a small thing, I think authors would do everyone a favor to stop using the "banking transactions, obvs" example.
Re: Transaction Isolation in Postgres
#16What is "the pre"?
I came here to ask this
Re: Transaction Isolation in Postgres
#17Re: Transaction Isolation in Postgres
#18One 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?
There has been some recent improvement to locking behavior:
https://learn.microsoft.com/en-us/sql/relational-databases/p...
Re: Transaction Isolation in Postgres
#19[deleted]
Re: Transaction Isolation in Postgres
#20> For reasons that should be obvious to anyone with a bank account, you really really want both updates to happen, or neither. This is what atomicity guarantees - that the entire transaction will either succeed or fail as a single unit. So, I understand why this example feels particularly illustrative of the value of transactions, many-if-not-most financial "transactions" can't practically rely on this kind of atomic…
I suppose this is a good example if your reader knows how banking systems work.
A better direct example in the same line of reasoning would be double-entry accounting where you would want both the credit and debit entry to either fail or succeed.
Most people probably don’t know that their bank account _is_ a double-entry account to their banking institution.
I can’t noodle a way to make the banking example more intuitive for an audience absent explaining how double-entry accounting works and that banks mostly obscure that from the customer. That’s not really knowledge you can assume from a software developer or sysadmin.