SQL: Incorrect by Construction

This document relies strongly upon Transact-SQL:

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.

I've encountered this dozens of times. It's not intuitive, but this implicitly locks the row from concurrent reads, where as SELECTing first won't:

  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.

If you need concurrency design your system for concurrency.

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)

SQL is intended as a declarative query language. That it is not the correct tool for imperative processing of updates feels expected? And mostly fine?

Fair that things often grow beyond their original intent.

This assumes you don't do any sort of caching or use distributed systems that can cache the data and choose to hold off to write it all to the DB. The cached system can show both users the in-process transactions as well.

Wonder what the [check-constraints] part meant or if it's a placeholder.

[dead]