How to Implement Optimistic Concurrency Control in SQL Server?

Introduction

In real-world database applications, multiple users often try to update the same data at the same time. If not handled properly, this can lead to data conflicts, overwriting of values, or inconsistent records.

Optimistic Concurrency Control (OCC) is a technique used in SQL Server to handle such situations safely. Instead of locking data aggressively, it assumes conflicts are rare and checks for conflicts only when updating the data.

In this step-by-step guide, you will learn how to implement optimistic concurrency control in SQL Server using simple language and practical examples.

What is Optimistic Concurrency Control?

Optimistic concurrency control is a method where:

Multiple users can read and modify data
No locks are applied during reading
Conflicts are detected during update

Simple explanation:
Instead of blocking users, SQL Server allows changes and verifies later if someone else modified the same data.

Real-life example:
Imagine two users editing the same profile form. When one user saves changes, the system checks if the data has changed since it was loaded. If yes, it prevents overwrite.

Why Use Optimistic Concurrency in SQL Server?

Using optimistic concurrency helps in:

Improving performance (no heavy locking)
Supporting high-concurrency applications
Avoiding unnecessary blocking

Before using OCC:

Users block each other
Slow performance

After using OCC:

Better scalability
Faster operations

How Optimistic Concurrency Works

The idea is simple:

Read data along with a version value
Modify data
Before updating, check if version is unchanged
If unchanged → update
If changed → conflict

Step-by-Step Implementation in SQL Server

Step 1: Add a Version Column (RowVersion)

SQL Server provides a special data type called rowversion (or timestamp) to track changes.

Example:

CREATE TABLE Employees (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100),
    Salary DECIMAL(10,2),
    RowVer ROWVERSION
);

Simple understanding:
RowVer automatically changes whenever the row is updated.

Step 2: Fetch Data with Version

When reading data, also fetch the RowVersion column.

SELECT Id, Name, Salary, RowVer
FROM Employees
WHERE Id = 1;

Why this matters:
You will use RowVer later to detect conflicts.

Step 3: Update Data with Concurrency Check

Use RowVersion in the WHERE clause while updating.

UPDATE Employees
SET Salary = 60000
WHERE Id = 1 AND RowVer = @OldRowVer;

Simple explanation:

Update only if RowVer matches
If someone else updated the row, RowVer changes → update fails

Step 4: Check Rows Affected

After update, check if any row was updated.

IF @@ROWCOUNT = 0
BEGIN
    PRINT 'Concurrency conflict occurred';
END

Meaning:

0 rows updated → conflict detected
1 row updated → success

Step 5: Handle Conflict Gracefully

When conflict occurs, you can:

Show error message
Reload latest data
Ask user to retry

Real-world example:
In a banking app, if two users edit the same record, the second user is asked to refresh data.

Alternative Approach Without RowVersion

You can also compare all column values manually:

UPDATE Employees
SET Salary = 60000
WHERE Id = 1 AND Salary = @OldSalary;

But this is less reliable and harder to maintain.

When to Use Optimistic Concurrency

Use OCC when:

Conflicts are rare
High read operations
Web applications with many users

Avoid when:

Frequent updates on same data
Critical systems needing strict consistency

Advantages of Optimistic Concurrency Control

Better performance (no locks)
Scales well for large systems
Reduces blocking issues

Disadvantages and Challenges

Conflict handling required
Possible retries needed
Not suitable for highly conflicting systems

Real-world mistake:
Not checking @@ROWCOUNT after update can silently overwrite data issues.

Best Practices

Always use RowVersion for tracking
Handle conflicts properly in application logic
Inform users about conflicts clearly
Test concurrency scenarios

Summary

Optimistic concurrency control in SQL Server is an efficient way to handle data conflicts without using heavy locking mechanisms. By using a RowVersion column and validating it during updates, you can detect conflicts and prevent unintended data overwrites. This approach is ideal for high-performance and scalable applications where conflicts are rare, and with proper handling of update failures, it ensures both data integrity and user experience.