| > The problem for novices is that a program that behaves correctly looks a lot like a correct program. Until one day it doesn’t. > And because you’re in production and getting random spurious failures, the panicked (but common) reaction is to wrap every shared resource in a synchronized block. Yep yep - that's the Java + Threads model. It's (relatively) harder to take single-threaded logic and make it behave in a multi-threaded setting. Compared to the SQL model, where it's (relatively) easier to take single-threaded logic, wrap it in BEGIN/END TRANSACTION, and have it perform exactly as expected. OK I get you now. In saying that SQL concurrency was easy and Java concurrency was hard I didn't think about what would happen if you tried to write a mixed Java/SQL transaction; I didn't realise there was a bunch of Java running between your SQL statements. So what would my fix be? Get rid of the Java and replace it with SQL. > Note that you can end up with deadlocks and transaction failures on any level stricter than READ COMMITTED, so the application needs to be able to deal with both of these. That's cool - transactions proceed completely or not at all. About the "not transactional" thing, I was applying (a => b) => (^b => ^a). That is, since transactions are isolated, and you demonstrated code that wasn't isolated, I can conclude that it wasn't a transaction. Maybe I need to adjust my thinking a bit: assumption i) Atomicity says "The series of operations cannot be separated with only some of them being executed".
assumption ii) Isolation says "Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially."
assumption iii) I use transactions because they're atomic and isolated.
A *SELECT balance* was run, passing its value out to the real world before the commit succeeded. This breaks assumptions i and iii.
"That is only one of the possible ways for transactions to work" breaks assumption ii and iii.
So, I can only conclude I should not use transactions.
|