Why is Snapshot Isolation not enough?
concurrencyfreaks.blogspot.com
Why is Snapshot Isolation not enough?
1–10 of 35 posts
Re: Why is Snapshot Isolation not enough?
#2Related: "A Critique of Snapshot Isolation" - https://news.ycombinator.com/item?id=40516582
Re: Why is Snapshot Isolation not enough?
#3Amazon Redshift very recently has made this the default choice.
Re: Why is Snapshot Isolation not enough?
#4It’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 that can't be solved by putting a ring buffer in front of a single thread that updates the values, doesn't need locks and is cache coherent.
Re: Why is Snapshot Isolation not enough?
#5Amazon Redshift very recently has made this the default choice.
Mssql on azure also has READ_COMMITTED_SNAPSHOT (server level setting, default ON on Azure SQL Database, default OFF on SQL Server >). When ON, it transparently changes your explicilty specified isolation level in query/transaction of "READ COMMITTED" to "READ COMMITTED SNAPSHIT" (genuine typo made here, decided to not correct) instead. Good luck fixing your prod incident when locally you're using mssql docker image (default OFF).
If you want to say "just use their edge mssql container image", yeah man – mssql/azure sql databse/mssql docker all support JSON_PATH_EXISTS, edge sql docker doesn't so that's it for better compatiblity.
What a mess.
Re: Why is Snapshot Isolation not enough?
#6It’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, just make sure you’re not trying to fly an airplane or prevent the detonation of nuclear weapons
My gripe with this kind of argument is that today, you aren't. You can write application-side duct tape to deal with any kind of wonky database situation, but the whole point of having a database with strong and easy-to-reason guarantees is that it makes future development easier.
Re: Why is Snapshot Isolation not enough?
#7This is so well explained. Cockroachdb implements serializable by default and it is a great experience being able to write sql with that in mind.
Re: Why is Snapshot Isolation not enough?
#8It’s the ABA problem that also comes up with normal multithreading when trying to go lock free. Lock free data structures are hard. SI is giving you the same guarantees (your update is atomic, but things might have changed between when you read and when you write). If you can handle this extra complexity, all good, but it’s generally something that needs to be carefully abstracted. Nobody wants to be thinking this hard every time they have to interact with a DB.
Re: Why is Snapshot Isolation not enough?
#9Amazon Redshift very recently has made this the default choice.
Mssql on azure also has READ_COMMITTED_SNAPSHOT (server level setting, default ON on Azure SQL Database, default OFF on SQL Server >). When ON, it transparently changes your explicilty specified isolation level in query/transaction of "READ COMMITTED" to "READ COMMITTED SNAPSHIT" (genuine typo made here, decided to not correct) instead. Good luck fixing your prod incident when locally you're using mssql docker image…
Microsoft loves to make their support matrix look like a random tapestry.
Re: Why is Snapshot Isolation not enough?
#10It’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…
OP provides even more simple examples than double linked lists. It made me realize how pg default isolation level was actually quite a nice footgun and reread the doc about the various isolation levels much more carefully.