undefined | Better HN

0 pointsghusbands2y ago0 comments

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.)

0 comments

_a_a_a_2y 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?

ghusbandsOP2y 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.

6345dhjdsf2y 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.

j / k navigate · click thread line to collapse