Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why is Snapshot Isolation not enough?

concurrencyfreaks.blogspot.com

Post reply on HN
Print view View original

21–30 of 35 posts

Re: Why is Snapshot Isolation not enough?

#21

Post by plorkyeran »

magicalhippo wrote: post #15

There's a typo, clearly the second transaction is removing C and not D as stated. > The first transaction modifies fields in records A and C, while the second transaction modifies fields in records B and D. > Under Snapshot Isolation both transactions will commit, which means that the doubly linked list will be left in an inconsistent state That's not how the DB we use works[1]. Both transactions will run, but as soo…

> Both transactions will run, but as soon as either touches a row the other has already modified […]

The whole point of this scenario is that this never happens. The two writes touch unrelated sets of rows. There’s no point where they both try to modify the same row and a conflict is observed.

Re: Why is Snapshot Isolation not enough?

#22

Post by MrBuddyCasino »

sixtram wrote: post #20

I always liked Jim Gray's marbles example to demonstrate the problem with snapshot isolation, so much easy to understand: https://techcommunity.microsoft.com/t5/sql-server-blog/seria...

This should be the standard way of explaining tx isolation, very intuitive!

Re: Why is Snapshot Isolation not enough?

#23

Post by jlokier »

sam_bishop wrote: post #12
_3u10 wrote: post #4

It’s enough, just make sure you’re not trying to fly an airplane or prevent the detonation of nuclear weapons using a database, it’s the wrong tool for the job. If you’re storing doubly linked lists in a DB you’re doing it wrong. Updating doubly linked lists can be done at about 200 million ops/sec, single threaded, not sure why you need multiple threads updating the list at the same time, exactly what are you doing…

> If you’re storing doubly linked lists in a DB you’re doing it wrong. Assuming that the database uses B+ trees (like most do), then the database records themselves are very likely to be in a doubly linked list. Not every doubly linked list is the kind you see in an introductory data structures class.

Yes, this. Because of this, I saw substantial data corruption that had built up over years in live medical databases: The on-disk B+trees had corrupted internal links due to incorrect handling of concurrent updates and rollbacks. This made some queries return incorrect results.

The developers had been writing messy heuristic workarounds for database low-level corruption in application code for over a decade, instead of figuring out the cause and fixing that.

For example, application code had special routines to attempt to detect and ignore duplicate records in some queries (but not others), in response to customer bug reports, but of course that didn't fix missing records or make the data correct. It just patched over an observed symptom. It also didn't fix all the places it could happen, so much of the application still had occasional flaky behaviour, but not consistently enough to be reported in detail and get the (bad) workarounds to hide it.

Correctly implemented transactions are very helpful for database internals and on-disk structures too, not just for the API level presented to applications.

Re: Why is Snapshot Isolation not enough?

#24

Post by magicalhippo »

plorkyeran wrote: post #21
magicalhippo wrote: post #15

There's a typo, clearly the second transaction is removing C and not D as stated. > The first transaction modifies fields in records A and C, while the second transaction modifies fields in records B and D. > Under Snapshot Isolation both transactions will commit, which means that the doubly linked list will be left in an inconsistent state That's not how the DB we use works[1]. Both transactions will run, but as soo…

> Both transactions will run, but as soon as either touches a row the other has already modified […] The whole point of this scenario is that this never happens. The two writes touch unrelated sets of rows. There’s no point where they both try to modify the same row and a conflict is observed.

Ah, implied assumptions strike again.

I was thinking so DB that when I read "remove", I assumed that. Of course you're right, if you're not actually removing B and C, there's no conflict.

If you actually remove B and C in the transaction, ie "deallocating" them either by deleting rows or updating prev/next/data to null, you'll get the conflict I mentioned.

Re: Why is Snapshot Isolation not enough?

#25

Post by projektfu »

paulddraper wrote: post #18
_3u10 wrote: post #4

It’s enough, just make sure you’re not trying to fly an airplane or prevent the detonation of nuclear weapons using a database, it’s the wrong tool for the job. If you’re storing doubly linked lists in a DB you’re doing it wrong. Updating doubly linked lists can be done at about 200 million ops/sec, single threaded, not sure why you need multiple threads updating the list at the same time, exactly what are you doing…

> It’s enough Surrender consistency only in the gravest of necessity. No one likes their data corrupted.

So don't use it for writes. Snapshot is good for analysis. Let's say you want to show the user a report of their sales by category, with some additional detail. You want to load those things as of the same cut off time because one of the detail queries could be different from the summary query if you didn't. This is what people expect in their results.

If you really need a query for modifying data that depends on itself, you should read it exclusively, i.e. serializable.

Re: Why is Snapshot Isolation not enough?

#26

Post by senderista »

projektfu wrote: post #25
paulddraper wrote: post #18

Earlier quoted context omitted.

> It’s enough Surrender consistency only in the gravest of necessity. No one likes their data corrupted.

So don't use it for writes. Snapshot is good for analysis. Let's say you want to show the user a report of their sales by category, with some additional detail. You want to load those things as of the same cut off time because one of the detail queries could be different from the summary query if you didn't. This is what people expect in their results. If you really need a query for modifying data that depends on its…

If you don't need your snapshot read-only queries to be linearizable, then you don't need MVCC for this: just take a consistent checkpoint every second or minute or hour. A simple way to implement low-frequency snapshots is what Redis made famous: fork the server process so all future updates in the parent are to CoW pages and read the snapshot from the child. (HyPer did this originally but I'm not sure they still do.)

I think this approach makes more sense than MVCC when linearizable abort-free read-only transactions aren't really necessary: you can avoid a lot of complexity and a lot of overhead by not retaining old versions.

Re: Why is Snapshot Isolation not enough?

#27

Post by nurple »

rvdginste wrote: post #19

I really like to work with snapshot isolation and rarely have any problems with it, but you do have to be aware what guarantees it gives and does not give. In the example given in the article, there would not be a problem if the links on the removed node would be reset (which is cleaner imho) as then an update conflict would be triggered. When using snapshot isolation, I sometimes implement ‘dummy’ updates (using a v…

This is a lot like how couchdb handles MVCC. There is a `_rev` field that represents some specific mutation of a document, old revisions stay available until compaction, and you will receive an error if you attempt to write to a document with a different revision than you read.

Snapshot isolation, after all, is basically a method for implementing MVCC. I guess it's no big surprise that this isolation level is problematic for people that don't implement the other half.

Re: Why is Snapshot Isolation not enough?

#28

Post by nop_slide »

sixtram wrote: post #20

I always liked Jim Gray's marbles example to demonstrate the problem with snapshot isolation, so much easy to understand: https://techcommunity.microsoft.com/t5/sql-server-blog/seria...

Great link!

On a related note, a difference in Postgres is that both Repeatable Read and the higher Serializable Isolation levels are both using Snapshot Isolation under the hood.

I've been trying to grok this stuff for a few years and it's good to keep in mind that the implementations of various isolation levels between vendors can vary significantly.

I've found the resources on Jepsen very helpful as well if you want to go down a rabbit hole :)

https://www.postgresql.org/docs/current/transaction-iso.html

https://jepsen.io/consistency

https://jepsen.io/consistency/models/snapshot-isolation

Re: Why is Snapshot Isolation not enough?

#29

Post by unnah »

senderista wrote: post #26
projektfu wrote: post #25

Earlier quoted context omitted.

So don't use it for writes. Snapshot is good for analysis. Let's say you want to show the user a report of their sales by category, with some additional detail. You want to load those things as of the same cut off time because one of the detail queries could be different from the summary query if you didn't. This is what people expect in their results. If you really need a query for modifying data that depends on its…

If you don't need your snapshot read-only queries to be linearizable, then you don't need MVCC for this: just take a consistent checkpoint every second or minute or hour. A simple way to implement low-frequency snapshots is what Redis made famous: fork the server process so all future updates in the parent are to CoW pages and read the snapshot from the child. (HyPer did this originally but I'm not sure they still do…

But those Redis snapshots are only used for regular back-ups, so completely unlike snapshot isolation in a relational database, right?

Re: Why is Snapshot Isolation not enough?

#30

Post by denton-scratch »

_3u10 wrote: post #4

It’s enough, just make sure you’re not trying to fly an airplane or prevent the detonation of nuclear weapons using a database, it’s the wrong tool for the job. If you’re storing doubly linked lists in a DB you’re doing it wrong. Updating doubly linked lists can be done at about 200 million ops/sec, single threaded, not sure why you need multiple threads updating the list at the same time, exactly what are you doing…

> If you’re storing doubly linked lists in a DB you’re doing it wrong.

This was my reaction on finding that TFA's key example is a doubly-linked list. I've never implemented any kind of linked list in a database; nor have I ever come across someone else's schema that involved linked lists. The kinds of operation you do on linked lists (traverse, insert, append, delete) all involve sequences of row accesses that can't be (conveniently?) described in SQL, so they have to be expressed as multiple distinct accesses.

More generally, I'm suspicious of a schema in which a table contains a "foreign" key to itself. Foreign keys are keys to other tables.

I haven't given it any thought; but could there have been a better example? Or is updating a doubly-linked list the best illustration of why SI is dangerous?

Post reply on HN