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.

Answer 1

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