SQL: Incorrect by Construction
20 points by chreke
20 points by chreke
The solution is to choose a strong enough isolation level. Yes, databases usually choose performance rather than strictly serializable transactions, but at least SQL allows you to adjust it to your preferences. And you don’t have to fart around with explicit locks! The main downside is that conflicting transactions are more likely to abort, so you might want a retry loop; but that can turn deadlocks into livelocks, so be careful.
Yes, SERIALIZABLE would solve a lot of these problems; however, the issue I have with SERIALIZABLE is that, like you said, it's basically optimistic concurrency so you have to rely on an outside retry mechanism.
Well, with Rust you know all the code upfront, with the SQL queries not that much. At any time a new unknown query may hit DB, that's why optimistic concurrency make sense.
At any time a new unknown query may hit DB, that's why optimistic concurrency make sense.
Everyone knows that databases need to be flexible to new and unknown queries. What I'm presupposing... maybe they shouldn't?
To elaborate, I've wanted a database that treated query plans (and, by implication, the queries themselves) as pre-compiled, deployable artifacts, which means that the database can schedule transactions deterministically (à la Calvin), so you can do better than optimistic concurrency and the planner can produce optimal plans at CI-time instead of trying to JIT a plan at runtime. chreke links to a literature review on this subject, but... I wish this was more popular.
If you were to go down this path, you'd be able to treat suboptimal queries, at worst, as a faied build. It means that up-to-date statistics would need to be part of the deployable artifact, but... that's a good thing. Being too flexible to user input is a bad thing!
Yeah, Calvin is really cool; I’m working on a sequel to this blog post with my solution to the problem, and Calvin was a big inspiration
Making statistics into source is not a bad idea. I've seen plenty of postgres servers screw up query planning badly because the managing team thought they were close to the limit. When in fact they had loads beyond what could both serve queries and vaccume.
Every single case would have performed better if they had just kept the old query plans.
This is a good point, and the problems I describe in the article stem from trying to interleave procedural and declarative code. Unfortunately, in most applications this is unavoidable, so we’re stuck with having to try to glue queries together with locks and transactions.
For the record, optimistic concurrency is a completely resonabel default; my broader issue is that if you want deterministic concurrency there is no mainstream SQL database that can provide it.
I have a nit, I believe the issues raised in the submission are with TSQL (and possibly other attempts to graft procedural programming onto SQL like PL/SQL), not SQL itself.
I mean, the distinction is hazy. Is COMMIT SQL? Well, yeah, but there are DB engines that expose a SQL interface but that don't have transactions.
This is a good point, but if you look at real-life applications they usually do the TSQL parts in application code instead, which only exacerbates the problem as there is more latency and more back-and-forth, which makes preemption and concurrency issues more likely.
If you want to write a money transfer procedure like the one in the post, I'm pretty sure you need something to stitch SQL queries together, so you will still have the same issues.
I noticed in your 2nd footnote you mention
Updating and checking the balance in a single UPDATE
It may help others, and myself, if you could show that. I understand it isn't the point of your article, however.
UPDATE accounts
SET balance = balance - 10
WHERE owner = 'alice' AND balance >= 10
RETURNING id;
If RETURNING comes back with zero rows, the precondition failed and you skip the credit to Bob. There's no TOCTOU window between the read and the write because they're the same statement. The row-level write lock that UPDATE takes when it locates a matching row also blocks any concurrent UPDATE on that row until the first one commits, so the second transaction re-evaluates its WHERE clause against the post-commit state and matches zero rows. This works at READ COMMITTED with no isolation level changes.
The thing this still doesn't give you is informative errors at the app layer. You only learn "no row affected," not whether the account is missing or just underfunded. A CTE or a follow-up SELECT can distinguish those cases, but at that point you're stitching statements together again, which is the broader point of the post.
Well said, but you almost certainly want to do this in a DB function anyway, and not at the app code level. You want something like select transfer_money('alice','bob',10); and have it do everything on the DB side and return any useful error messages if needed.
Thank you, but I was more curious of the "single update" statement case.
It looks like this method would still require two individual UPDATEs.
I believe I solved this myself, and maybe the author or yourself can confirm or deny it:
WITH upd(owner, balance, newbal, sanity) AS
( SELECT a.owner, a.balance,
CASE WHEN a.owner = N'bob' THEN a.balance + 10
WHEN a.owner = N'alice' THEN a.balance - 10
END,
SUM(1) OVER () -- ignore if you assume both accts always exist
FROM Scratch.Accounts a
WHERE a.owner in (N'bob', N'alice')
)
UPDATE upd
SET balance = newbal
WHERE sanity = 2; -- see above
note: the table has a CHECK ( [balance] >=0 ) on balance column.
edit: someone kindly pointed out technically, if one of the accounts doesn't exist, bad things happen.
UPDATE accounts
SET balance = balance + CASE owner
WHEN 'alice' THEN -10
WHEN 'bob' THEN 10
END
WHERE owner IN ('alice', 'bob')
AND (SELECT balance FROM accounts WHERE owner = 'alice') >= 10
AND (SELECT balance FROM accounts WHERE owner = 'bob') >= 0
RETURNING id, owner, balance;
And then just make sure 2 rows were returned, otherwise something went wrong
The neat thing about NoSQL DBs like CouchDB is that they don't pretend issues like this do not exist. The user is expected to handle them via mechanisms like CRDTs or document versioning.
The example in the article is short and isolated - makes it ideal for showing to SQL fanatics =)
I can't imagine someone being a "SQL fanatic" and not already knowing the introduction paragraph of concurrency 101.