Hacker News new | ask | show | jobs
by ghusbands 1003 days ago
> Most importantly, SQL databases made supporting highly consistent ACID transactions easy.

The default transaction isolation level for every major database is not ACID. Enabling the required serializability tends to make performance terrible, and so most don't.
2 comments
_a_a_a_ 1003 days ago
> Enabling the required serializability...

is trivial, no?

> ...tends to make performance terrible

I've heard this a lot but never seen any figures - anyone have any numbers/experience?

(edit: and most apps I've worked with didn't need serialisability, either because they were working with a snapshot of data or absolutely precise answers weren't needed)

link
ghusbands 1003 days ago
I've heard multiple accounts of people being taken by surprise by this and by how transaction isolation actually works in databases and not finding it at all easy to correct it. A famous one is https://blog.codinghorror.com/deadlocked/

(On your edit: The problem is not knowing when you're being hit by it. Even just maintaining a limit on total size of uploaded files or such, for example, is nontrivial under default isolation levels.)

link
_a_a_a_ 1003 days ago
On your first para I'm not remotely sympathetic. They are using a complex, sophisticated tool without understanding it. They got what they deserved. Particularly telling is this ridiculous quote "I'm a little disappointed that SQL Server treats our silly little web app like it's a banking application". Edit: so, they wanted even more lax integrity constraints by default? FFS

And, BTW, deadlocks can most definitely happen using the default Read Committed isolation level.

Per your 2nd para, I simply don't understand, can you clarify?

link
ghusbands 1003 days ago
You're expecting a very high level of expertise for a tool that so many use incidentally. Most don't actually know how transaction isolation works, especially as the fine detail varies between databases. It's incredibly common to believe that databases actually are ACID by default and that is repeated far and wide.

For the last, I was pointing out that people don't necessarily know when they hit race conditions or other transaction isolation faults; they're one of the hardest things to debug or notice. It would be easy to believe that a transaction that confirms a file update would not take a user over their quota and then updates the file would, per ACID, actually ensure the user doesn't exceed their quota. But the default transaction isolation levels don't provide that.

link
6345dhjdsf 1003 days ago
You're actually right because casting my mind back, I can remember exactly the same kind of thing happening in places I worked for. I specifically remember discovering a race condition because they didn't understand about locking/trans iso levels.

Nonetheless if you're going to base your company's product around a database or anything else for that matter, you really had better get yourself sorted out WRT your tools, or it can become horribly expensive to fix or even fatal to your company.

link
iudqnolq 1003 days ago
that's presumably why the author said "made supporting... easy", not "is"?
link
ghusbands 1003 days ago
Most people believe that databases are ACID by default, so it's worth bringing up when an article even subtly implies otherwise.
link
bhattid 1003 days ago
Damn, this is news to me. Seems I have some reading to do, thanks for pointing that out.
link