Transaction Semantics, Optimistic Concurrency, Validation Failures, and Retry Logic

Learning outcomes

Removing locks does not remove transaction conflicts. In-Memory OLTP uses optimistic multiversion concurrency: transactions generally proceed without blocking each other on memory-optimized rows, then SQL Server detects write conflicts and validates isolation guarantees. Under contention, the visible symptom often changes from “wait for a lock” to “one transaction fails and must retry.” An application that ignores that semantic shift can convert a blocking problem into user-visible errors or duplicate business actions.

1. Optimistic concurrency changes who waits and who retries

Disk-based READ COMMITTED transactions commonly take locks and may wait when another transaction owns an incompatible lock. Memory-optimized tables use row versions and optimistic validation. Readers can often proceed without blocking writers, but conflicting updates or isolation violations can invalidate a transaction. In-memory transactions therefore cannot deadlock each other in the ordinary lock-cycle sense; Microsoft explicitly notes that error 1205 cannot arise from a memory-optimized table alone. Cross-container transactions can still interact with disk objects, so the surrounding application can encounter blocking elsewhere.

SNAPSHOT is the least demanding native isolation level. REPEATABLE READ validates that rows previously read were not changed before commit. SERIALIZABLE additionally validates scanned ranges so a phantom insert can invalidate the transaction. Unique and foreign-key constraint validation can also produce related failures.

USE master;GOIF DB_ID(N'ServiceHubXtpLab') IS NULLBEGIN    CREATE DATABASE ServiceHubXtpLab;END;GOALTER DATABASE ServiceHubXtpLab SET RECOVERY SIMPLE;ALTER DATABASE ServiceHubXtpLab SET COMPATIBILITY_LEVEL = 170;GOUSE ServiceHubXtpLab;GOIF NOT EXISTS (SELECT 1 FROM sys.filegroups WHERE type = 'FX')BEGIN    ALTER DATABASE ServiceHubXtpLab      ADD FILEGROUP ServiceHubXtpFG CONTAINS MEMORY_OPTIMIZED_DATA;    DECLARE @base nvarchar(4000) = CONVERT(nvarchar(4000), SERVERPROPERTY('InstanceDefaultDataPath'));    IF @base IS NULL        THROW 50001, 'InstanceDefaultDataPath is unavailable. Supply a writable SQL Server data path manually.', 1;    DECLARE @folder nvarchar(4000) = @base + N'ServiceHubXtpContainer';    DECLARE @sql nvarchar(max) =      N'ALTER DATABASE ServiceHubXtpLab ADD FILE '      + N'(NAME=N''ServiceHubXtpContainer'', FILENAME=N'''      + REPLACE(@folder,'''','''''')      + N''') TO FILEGROUP ServiceHubXtpFG;';    EXEC sys.sp_executesql @sql;END;GOUSE ServiceHubXtpLab;GOIF OBJECT_ID(N'dbo.InventoryMO') IS NULLBEGIN  CREATE TABLE dbo.InventoryMO  (    sku varchar(20) NOT NULL,    available_qty int NOT NULL,    last_token uniqueidentifier NULL,    CONSTRAINT PK_InventoryMO PRIMARY KEY NONCLUSTERED HASH(sku)      WITH (BUCKET_COUNT=1024)  ) WITH (MEMORY_OPTIMIZED=ON,DURABILITY=SCHEMA_AND_DATA);END;GODELETE dbo.InventoryMO WHERE sku='PART-42';INSERT dbo.InventoryMO(sku,available_qty,last_token)VALUES('PART-42',10,NULL);GO

2. First failure: explicit READ COMMITTED is not the same contract

A learner may start an explicit transaction at ordinary READ COMMITTED and access a memory-optimized table as if it were a disk table. For explicit/implicit transactions, that lower isolation is not valid for memory-optimized access without elevation. SQL Server returns error 41368. This is not a transient write conflict and should not be hidden by a retry loop.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;GOBEGIN TRY  BEGIN TRAN;  SELECT sku,available_qty  FROM dbo.InventoryMO; -- expected: 41368 in this explicit transaction  COMMIT;END TRYBEGIN CATCH  IF XACT_STATE()<>0 ROLLBACK;  SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;END CATCH;GOBEGIN TRAN;SELECT sku,available_qtyFROM dbo.InventoryMO WITH (SNAPSHOT)WHERE sku='PART-42';COMMIT;GO

An alternative database-wide choice is MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON, which automatically elevates qualifying lower-isolation access to SNAPSHOT. That setting changes application semantics database-wide, so do not enable it solely to silence one error. Verify all callers and record the change.

3. Reproduce an update conflict with two sessions

Use two query windows connected to ServiceHubXtpLab. Session A starts first and establishes its transaction snapshot. While A waits, Session B commits a change to the same SKU. When A later tries to update the now-changed row, the optimistic engine detects that its transaction began before the committed modification and one path fails with an update conflict (commonly 41302).

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;GOBEGIN TRAN;SELECT sku,available_qtyFROM dbo.InventoryMO WITH (SNAPSHOT)WHERE sku='PART-42';-- Leave this transaction open. Now run Session B.-- After B commits, run the UPDATE below in this same session.UPDATE dbo.InventoryMOSET available_qty=available_qty-1WHERE sku='PART-42';COMMIT;GO

UPDATE dbo.InventoryMOSET available_qty=available_qty-2WHERE sku='PART-42';SELECT sku,available_qtyFROM dbo.InventoryMOWHERE sku='PART-42';GO

The exact statement at which A surfaces the error can depend on the conflict path, but the semantic conclusion is stable: one transaction cannot silently overwrite a row version that invalidates its optimistic transaction. Reset the row to 10 before repeating the exercise.

SELECT * FROM sys.dm_xtp_transaction_stats;GOUPDATE dbo.InventoryMO SET available_qty=10,last_token=NULL WHERE sku='PART-42';GO

4. Error classification: retry only transient transaction conflicts

Important In-Memory OLTP errors include 41302 (write conflict), 41305 (repeatable-read validation failure), 41325 (serializable validation failure), and 41301 (dependency failure). These can be candidates for retry after rolling back the failed transaction. By contrast, errors such as 41368 indicate an isolation/configuration problem and should be fixed, not repeatedly resubmitted. Error 41823 signals that a Standard/Express memory-optimized-data quota has been reached; retrying without freeing capacity or changing design merely burns CPU.

A bounded retry must re-execute the whole transaction from fresh state. It should use backoff/jitter at the application layer and have a maximum attempt count. More importantly, the business operation must be idempotent or carry a unique request token so a client timeout followed by retry cannot charge, dispatch or publish twice.

DECLARE @token uniqueidentifier=NEWID();DECLARE @attempt int=0, @max_attempts int=4;WHILE @attempt < @max_attemptsBEGIN  SET @attempt += 1;  BEGIN TRY    BEGIN TRAN;    IF NOT EXISTS    (      SELECT 1 FROM dbo.InventoryMO WITH (SNAPSHOT)      WHERE sku='PART-42' AND last_token=@token    )    BEGIN      UPDATE dbo.InventoryMO WITH (SNAPSHOT)      SET available_qty=available_qty-1,          last_token=@token      WHERE sku='PART-42' AND available_qty>0;      IF @@ROWCOUNT<>1        THROW 50020,'No inventory available.',1;    END;    COMMIT;    BREAK;  END TRY  BEGIN CATCH    IF XACT_STATE()<>0 ROLLBACK;    IF ERROR_NUMBER() NOT IN (41301,41302,41305,41325)       OR @attempt >= @max_attempts      THROW;    WAITFOR DELAY '00:00:00.050'; -- lab only; use jitter/backoff in applications  END CATCH;END;GO

This skeleton demonstrates classification and bounded attempts, not a universal retry policy. Production clients should generate/persist the token at the business-request boundary, log attempt/error details, use jittered backoff, and surface repeated conflict as an operational signal rather than hiding severe contention forever.

5. Production judgment

Optimistic concurrency is attractive when conflicts are genuinely uncommon. If every transaction updates the same logical row, removing locks cannot create parallel truth: one version must still win. High 41302 rates are therefore evidence to redesign partitioning, counters, ownership or business workflow—not just raise retry counts. Keep transactions short because old active transactions retain row versions and can also delay garbage collection.

Before migration, define which errors are retryable, how many attempts the application allows, which business key guarantees idempotency, and what telemetry proves conflicts are under control. The next lesson connects this transaction model to capacity: row versions, hash arrays, checkpoint files and garbage collection all consume resources that must be measured continuously.

Authoritative references

• Transactions with memory-optimized tables
• Transaction isolation levels for memory-optimized tables
• MSSQLSERVER 41305
• MSSQLSERVER 41325
• In-Memory OLTP overview and usage scenarios