[HN Gopher] Transaction Isolation in Postgres
___________________________________________________________________
Transaction Isolation in Postgres
Author : jerrinot
Score : 100 points
Date : 2023-12-18 16:13 UTC (6 hours ago)
(HTM) web link (www.thenile.dev)
(TXT) w3m dump (www.thenile.dev)
| nuttingd wrote:
| One 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.
| brasetvik wrote:
| 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?
| vore wrote:
| 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.
| bob1029 wrote:
| There has been some recent improvement to locking behavior:
|
| https://learn.microsoft.com/en-us/sql/relational-
| databases/p...
| Fire-Dragon-DoL wrote:
| Oh that's super nasty, is it mentioned somewhere in the doc?
|
| Is it the same for repeatable read?
| gwen-shapira wrote:
| 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....
| franckpachot wrote:
| 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
| gwen-shapira wrote:
| 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.
| nuttingd wrote:
| 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.
| magicalhippo wrote:
| 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.
| Exuma wrote:
| This 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.
| gwen-shapira wrote:
| 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.
| rabee3 wrote:
| 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.
| masfuerte wrote:
| What is "the pre"?
| itunpredictable wrote:
| I came here to ask this
| gwen-shapira wrote:
| It should read "code" instead of "pre".
|
| I'm pushing a fix right now.
| watters wrote:
| > 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.
| TheNewsIsHere wrote:
| 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.
___________________________________________________________________
(page generated 2023-12-18 23:00 UTC)