3 ms·
> comparisons using clear sub-optimal interactive SQL transactions for financial workloads, like locking rows rather than using condition checks at commit time
by n_u 1y ago
> comparisons using clear sub-optimal interactive SQL transactions for financial workloads, like locking rows rather than using condition checks at commit time
Could you give an example of how the same transaction could be written poorly with "locking rows" and then more optimally with "using condition checks at commit time"?
- dangoodmanUT 1y agosubtract 50 if balance >= 50 is optimistic (the value at commit can be different than at read) vs. "lock this row so it can't change while I subtract 50 from 50,000,000,000,000..." you get the point
- n_u 1y agoI'm more confused now. Those two have different logic? In the second example the balance can go negative. Also aren't both of those are going to have to lock the row to modify it? Even if you don't explicitly take out a lock on a row the DBMS will do its own concurrency control to provide transaction isolation, which will require a write lock for the row. Edit: Oh are you saying the "poorly written" case is an interactive transaction where the logic happens in the application code and involves multiple round trips while holding a lock? Instead of a transaction (or maybe even stored proc) where the logic happens in the DB, so less time holding the lock. Less contention. Ah ok. Yeah interactive transactions like that aren't great.