[fishstamp82]

RE: "All it knows is that you're trying to write a 6. If someone else wrote a 6 or a 7 in the meantime, then your transaction may have 'meant' (+0) or (-1)."

This is not how it works at all. This is called dirty writes and is by default prevented by ACID compliant databases, no matter the isolation level. The second transaction commit will be rejected by the transaction manager.

Even if you start a transaction from your application, it does not change this still.

[belter]

http://www.bailis.org/blog/when-is-acid-acid-rarely/

[fishstamp82]

Postgres as an example is ACID compliant if you want it to be. All those databases that have full serialization possible do utilize RC by default which is enough to prevent dirty writes and was my original point.

Thanks for the link still, it was valuable!

[mrkeen]

I have no problem with ACID the concept. It's a great ideal to strive towards. I'm sure your favourite RDBMS does a fine job of it. If you send it a single SQL string, it will probably behave well no matter how many other callers are sending it SQL strings (as long as the statements are grouped appropriately with BEGIN/COMMIT).

I'm just pointing out two ways in which you can make your system non-ACID.

1) Leave it on the default isolation level (READ_COMMITTED):

You have ten accounts, which sum to $100. You know your code cannot create or destroy money, only move it around. If no other thread is currently moving money, you will always see it sum to $100. However, if another thread moves money (e.g. from account 9 to account 1) while your summation is in progress, you will undercount the money. Perfectly legal in READ_COMMITTED. You made a clean read of account 1, kept going, and by the time you reach account 9, you READ_ what the other thread _COMMITTED. Nothing dirty about it, you under-reported money for no other reason than your transactions being less-than-Isolated. You can then take that SUM and cleanly write it elsewhere. Not dirty, just wrong.

2) Use an ORM like LINQ. (Assume FULL ISOLATION - even though you probably don't have it)

If you were to withdraw money from the largest account, split it into two parts, and deposit it into two random accounts, you could do it ACID-compliantly with this SQL snippet:

    SELECT @bigBalance = Max(Balance) FROM MyAccounts
    SELECT @part1 = @bigBalance / 2;
    SELECT @part2 = @bigBalance - @part1;
    ..
    -- Only showing one of the deposits for brevity
    UPDATE MyAccounts
    SET Balance = Balance + @part1
    WHERE Id IN (
        SELECT TOP 1 Id
        FROM MyAccounts
        ORDER BY NewId()
    );

Under a single thread it will preserve money. Under multiple threads it will preserve money (as long as BEGIN and COMMIT are included ofc.). Perfectly ACID. But who wants to write SQL? Here's a snippet from the equivalent C#/EF/LINQ program:

    // Split the balance in two
    var onePart = maxAccount.Balance / 2;
    var otherPart = maxAccount.Balance - onePart;

    // Move one half
    maxAccount.Balance -= onePart;
    recipient1.Balance += onePart;

    // Move the other half
    maxAccount.Balance -= otherPart;
    recipient2.Balance += otherPart;

Now the RDBMS couldn't manage this transactionally even if it wanted to. By the final lines, 'otherPart' is no longer "half of the balance of the biggest account", it's a number like 1144 or 1845. The RDBMS thinks it's just writing a constant and can't connect it back to its READ site:

    info: 1/31/2026 17:30:57.906 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command) 
        Executed DbCommand (7ms) [Parameters=[@p1='a49f1b75-4510-4375-35f5-08de60e61cdd', @p0='1845'], CommandType='Text', CommandTimeout='30']
        SET NOCOUNT ON;
        UPDATE [MyAccounts] SET [Balance] = @p0
        WHERE [Id] = @p1;
        SELECT @@ROWCOUNT;

[fishstamp82]

For example 1) Let's be clear about what we are doing.

If you are running in RC isolation, and perform a select sum() from table, you are reading values committed by other threads BEFORE the select statement began, you are not getting other threads committed values during the select, you are not breaking ACID.

If you are suggesting that running a simple BEGIN; select sum() from table; COMMIT is breaking acid in a default RC level, you are wrong and should best avoid commenting on isolation levels in RDBMS online, to not confuse people further.

If you are however suggesting that we are breaking ACID if we do app side stupidity such as:

value1=BEGIN; SELECT value from table where id=1;commit value2=......

sum = value1+value2....+value10

Then yes obviously its not acid but nobody in their right minds should be doing that. Even juniors quickly learn that this is incorrect code.

If you are suggesting we do repeatable reads in RC then yes its obviously not ACID but your example does not mention repeatable summations only a single one.