How to fix Error 11036 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 11036 – Optimistic Concurrency Violation

Why This Error Happens

Error 11036 occurs when a rowset configured for optimistic concurrency detects that an underlying column value was modified by another transaction or process after the row was originally fetched or last resynchronized. This typically happens in client applications using OLE DB, ODBC, or ADO cursors where row versioning (timestamp/rowversion columns) is used to detect conflicts before allowing an update or delete. The error is a built-in concurrency safeguard rather than a corruption issue, but it can also surface due to stale cached result sets, improper cursor refresh logic, or missing rowversion/timestamp column synchronization in the client application.

Step-by-Step Resolution Guide

Step 1: Verify the Table’s Concurrency Column Configuration

  • Confirm the table has a rowversion (formerly timestamp) column that SQL Server uses to track row changes.
  • Run the following query to check: SELECT name, system_type_name FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM YourTableName', NULL, 0);
  • If missing, add one: ALTER TABLE YourTableName ADD RowVersionCol ROWVERSION;

Step 2: Refresh the Client-Side Cursor Before Update

  • Inspect the application or ETL job code (ADO, ODBC, OLE DB) for cursor types set to adOptimistic or SQL_CONCUR_ROWVER.
  • Force a Resync or re-fetch of the row immediately before attempting the update operation to pull the latest row version.
  • In ADO: call Recordset.Resync adAffectAll, adResyncAllValues prior to .Update.

Step 3: Check for Concurrent Transactions or Triggers Modifying the Row

  • Query active sessions using sp_who2 or SELECT * FROM sys.dm_exec_requests; to identify overlapping transactions touching the same table.
  • Review any AFTER UPDATE or INSTEAD OF triggers on the table that may silently modify the row (e.g., updating an audit column), which changes the rowversion unexpectedly.
  • Disable or adjust trigger logic if it’s causing unintended rowversion increments: DISABLE TRIGGER TriggerName ON YourTableName;

Step 4: Adjust Application Retry Logic

  • Implement a retry pattern in the client application that catches error 11036, re-fetches the current row, reapplies the business logic, and resubmits the update.
  • In connection string or driver settings on Windows Server 2025/Windows 11, ensure the OLE DB provider (e.g., MSOLEDBSQL19) is updated to the latest version to avoid known cursor-sync bugs.

Alternative or Verification Steps

  • To verify the fix, run the update operation again through the application and confirm no 11036 error is raised in SQL Server Profiler or Extended Events (filter on error_reported with error number 11036).
  • As an alternative, switch the cursor concurrency model from optimistic to adLockBatchOptimistic or pessimistic locking (adLockPessimistic) if conflicts remain frequent, though this will reduce concurrency performance.