Why Parallel update database with .net leads to consistent result-CodePudding

I am trying to run following code to show "inconsistency" when database is updated parallelly (with multiple threads), but when I run this code the value in the table is always consistence i.e. 500. how it is working ?

static void Main(string[] args)
    for (int i = 0; i < 500; i  ) {
        Thread t = new Thread(() => Update());
        t.Start();
    }

    await Task.Delay(100000);
}

public static void Update() {
    using (var conn = new SqlConnection("mssql-connection-string")) {
        conn.Execute("update [table] set counter = counter  1;");
    }
}

CodePudding user response：

The code you have won't result in race conditions as the row you want to UPDATE will be locked, incremented, and then the lock released; meaning that any other UPDATE statements that were attempted to be run at the same time will have to "wait" for the first UPDATE to complete.

If you want to simulate a race condition, assign the value to a variable first; that should give you the behaviour:

DECLARE @c int = (SELECT counter FROM dbo.[table]); --I assume table only has 1 row
UPDATE dbo.[table] SET Counter = @c   1;

This will mean that the value of counter will be assigned to the variable first, which does not lock the row from other processes reading it. As such multiple simultaneous threads may all read the row at the same time, and then UPDATE it to their respective 1 value. So, 5 threads might all read the value as 7, and UPDATE the value to 7 1 (8).

If, for some reason, you did need to assign the value(s) in the table to a variable first before you UPDATE them, then you would want to use a UPDLOCK hint in your query:

DECLARE @c int = (SELECT counter FROM dbo.[table] WITH (UPDLOCK)); --I assume table only has 1 row
UPDATE dbo.[table] SET Counter = @c   1;

You would likely need to also change the isolation level of the transaction to SERIALIZABLE.