Optimistic concurrency while in transaction

Question

In trying to fix data errors due to concurrency conflicts I realized I'm not completely sure how optimistic concurrency works in SQL Server. Assume READ_COMMITTED isolation level. A similar example:

BEGIN TRAN

SELECT * INTO #rows FROM SourceTable s WHERE s.New = 1

UPDATE d SET Property = 'HelloWorld' FROM DestinationTable d INNER JOIN #rows r ON r.Key = d.Key

UPDATE s SET Version = GenerateRandomVersion() FROM SourceTable s
INNER JOIN #rows r on r.Key = s.Key AND r.Version = s.Version

IF @@ROWCOUNT <> SELECT COUNT(*) FROM #rows
    RAISEERROR
END IF

COMMIT TRAN

Is this completely atomic / thread safe?

The ON clause on UPDATE s should prevent concurrent updates via the Version and ROWCOUNT check. But is that really true? What about the following, similar query?

BEGIN TRAN

SELECT * INTO #rows FROM SourceTable s WHERE s.New = 1

UPDATE s SET New = 0 AND Version = GenerateRandomVersion() FROM SourceTable s
INNER JOIN #rows r on r.Key = s.Key AND r.Version = s.Version

IF @@ROWCOUNT <> SELECT COUNT(*) FROM #rows
    RAISEERROR
END IF

UPDATE d SET Property = 'HelloWorld' FROM DestinationTable d INNER JOIN #rows r ON r.Key = d.Key

COMMIT TRAN

My worry here is that concurrent execution of the above script will reach the UPDATE s statement, get a @@ROWCOUNT that is transient / not actually committed to DB yet, so both threads / executions will continue past the IF statement and perform the important UPDATE d statement, which in this case is idempotent but not so in my original production case.

Does this answer your question? [SQL Server - is using @@ROWCOUNT safe in multithreaded applications?](https://stackoverflow.com/questions/8960510/sql-server-is-using-rowcount-safe-in-multithreaded-applications) — Paddy, Mar 18 '22 at 12:52
@Paddy No, that scenario is a bit more simplistic. I'm aware @@ROWCOUNT will return the correct value, but I'm unsure how it interacts with multiple statements, optimistic concurrency and transactions. — Max, Mar 18 '22 at 12:56
"Assume read_committed" → is "read committed snapshot" enabled for the database? You mention optimistic concurrency which implies that it is, but being explicit about it would be good. Also, in re: expected concurrency, what is your desired result? That is, if two processes attempt to update the same set of rows, what do you want to happen? — Ben Thul, Mar 18 '22 at 18:39

Paddy · Answer 1 · 2022-03-21T09:08:31.840

I think what you want to do is remove the very small race condition in your script by making it as set based as possible, e.g.

BEGIN TRAN

DECLARE @UpdatedSources Table (Key INT NOT NULL);

UPDATE s SET New = 0 
FROM SourceTable s
WHERE s.New = 1
OUTPUT Inserted.Key into @UpdatedSources

UPDATE d SET Property = 'HelloWorld' 
FROM DestinationTable d 
INNER JOIN @UpdatedSources r ON r.Key = d.Key

COMMIT TRAN

I think the 'version' column in your table is confusing things - you're trying to build atomicity into your table rather than just letting the DB transactions handle that. With the script above, the rows where New=1 will be locked until the transaction commits, so subsequent attempts will only find either 'actually' new rows or rows where new=0.

Update after comment

To demonstrate the locking on the table, if it is something you want to see, then you could try and initiate a deadlock. If you were to run this query concurrently with the first one, I think you may eventually deadlock, though depending on how quickly these run, you may struggle to see it:

BEGIN TRAN

SELECT *
FROM DestinationTable d 
INNER JOIN SourceTable ON r.Key = d.Key
WHERE s.New = 1

UPDATE s SET New = 0 
FROM SourceTable s
WHERE s.New = 1
    
COMMIT TRAN

Sure, that's a smaller example (thank you) but it still has a race condition. If you execute that twice concurrently, the UPDATE will OUTPUT all rows with New = 1 into UpdatedSources. This is before either transaction commits, so they both will get all rows into @UpdatedSources. This means that your second update for DestinationTable will happen twice. Or is this reasoning incorrect? — Max, Mar 18 '22 at 14:56
I don't think that is how that will work. As the first operation is an UPDATE and we are running within a transaction, then those rows will be locked until the transaction commits. — Paddy, Mar 21 '22 at 08:44

Optimistic concurrency while in transaction

1 Answers1