The conclusion should be much narrower. For the same row double debit, true SNAPSHOT isolation, enabled and actually used, not just RCSI, turns the loser into an update-conflict/retry. The idiomatic SQL fix is a conditional debit UPDATE, row-count check, constraints, and one transaction for the transfer.

The deadlock example is also overstated. SQL Server detects the cycle, rolls back a victim, returns error 1205 [1], and the application is expected to retry. Microsoft has shitty defaults and the author turned it into a much broader claim ..

[1] - "Your transaction (process ID #...) was deadlocked on {lock | communication buffer | thread} resources with another process and has been chosen as the deadlock victim. Rerun your transaction"

"Deadlocks guide" - https://learn.microsoft.com/en-us/sql/relational-databases/s...

  BEGIN TRANSACTION;

  IF EXISTS (
    SELECT 1
    FROM account_balances WITH (UPDLOCK, HOLDLOCK)
    WHERE owner = 'alice'
      AND balance >= 10
  )
  BEGIN
    INSERT INTO account_ledger (owner, amount, memo)
    VALUES
        ('alice', -10, 'transfer to bob'),
        ('bob',    10, 'transfer from alice');
  END

  COMMIT TRANSACTION;

  BEGIN TRANSACTION;

  UPDATE accounts WITH (UPDLOCK)
  SET balance = balance - 10
  WHERE owner = 'alice'
  AND balance >= 10;

  IF @@ROWCOUNT = 1
  BEGIN
    UPDATE accounts
    SET balance = balance + 10
    WHERE owner = 'bob';
  END

  COMMIT TRANSACTION;

There's nothing "incorrect by construction".

The author claims the original snippet "looks completely reasonable". It absolutely does not, if you know anything about client-server databases.

  UPDATE accounts  
  SET balance = balance - 10  
  WHERE owner = 'alice' AND balance >= 10;

  INSERT INTO foo(num) (
    SELECT 1 WHERE NOT EXISTS (
      SELECT * FROM foo WHERE num = 1
    )
  );

Schools teach that databases are ACID, but in most cases they aren't by default, and enabling full ACID comes with other caveats and also a large performance hit.

??? This doesn't make sense. It's like saying "just implement it properly".

what about distributed clients? what about _different_ clients?

https://en.wikipedia.org/wiki/Transact-SQL

A more universal industry standard is SQL/PSM, which originated from Oracle PL/SQL:

https://en.wikipedia.org/wiki/SQL/PSM

Demonstrating the flaws in question in the PSM standard would be more useful.

> Let the user manage locks themselves, and make sure the correct locks are acquired before mutating a database object.

As demonstrated by the extended (corrected) version of the transaction, the user is controlling which locks get used. So how does this make it into the conclusion as a want, when it already is how it works?

I don't think it's SQL itself - it's the DB vendors ship weak isolation so people aren't hit by deadlocks, isn't it?

> Make transactions atomic by default

Not the issue, right? It's the weak isolation.

Preventing these kinds of concurrency issues is exactly why I built https://socketcluster.io years ago. Though it solves the problem at the app layer rather than the storage layer.

But not many developers care about these race conditions it seems.

It's not just an issue with SQL but a more general issue with many programming languages and approaches.

This is a great example because it shows how concurrent executions can lead to significant issues.

Have a transactions table with the payer and receiver and calculate the current balance using the transactions.

Each transaction must have a unique Id (pk)

> Let’s map out the transactions again:

> T1: Acquire a lock on Alice’s account

> T2: Acquire a lock on Bob’s account......

::loud sigh::

In MySQL, if you acquire a lock on Alice, then attempt acquire on Bob... then another session acquire Bob, then acquires Alice: The engine notices. Nothing bad happens. One of the threads gets marked as a deadlock and is rolled back, the other succeeds. So... yeah, not really a problem. MySql chooses at "random" which one lives.

Actually... hang on, pause, let me remind you what you learned in your undergrad CS 400 class: Acquire Locks in consistent order. You could sort by account id, or even their first names. Hell sort by their cats name. It doesn't matter, it just has to be deterministic, so: Alice comes before Bob. So doing this properly:

T1: Acquire a lock on Alice’s account with SELECT FOR UPDATE

T2: Acquire a lock on Alice’s account with SELECT FOR UPDATE... ::waits::

Annnnnnd, yeah, magic. Problem solved.

I don't find any of this too difficult and I really the only thing SQL engines do poorly is reporting on what locks are being held. Adding in XA Transactions can make things even more intuitive and gives you a nice building block if you need to coordinate sending messages to a broker or something else.

Oh and lastly, you should be using a ledger table for this; not storing balances as columns. Lock on both accounts, insert parallel rows into the ledger. And don't forget to use fixed point decimal datatypes.

I'll show myself to the door now. Thank you.

Fair that things often grow beyond their original intent.

You can even insert them in an unvalidated state, then validate them later. That way if you have two transactions that come one after another, it doesn't matter because you can process them sequentially anyway.

  set @balance = (....

still, anytime you use an isolation mode besides serializable you have to know the details and even with serializable if you aren't catching on failure in your app then you're sending your deadlock or timeout message to your end user.

I used to debug procedures with "sleep" tsql interspersed through code, this way I could deliberately overlap procedure executions and see how that went.

In my last job I had to support app developers who were happy with the "nolock" keyword even when marshalling info for updates. Shortly before I left that job in disgust I was at least able to have the developers quit using "nolock" everywhere, unfortunately their remediation to prevent use of the "nolock" keyword was to use "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" at the top of their code haha