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:
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:
Step 4: Check Rows Affected
After update, check if any row was updated.
IF @@ROWCOUNT = 0
BEGIN
PRINT 'Concurrency conflict occurred';
END
Meaning:
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:
Avoid when:
Advantages of Optimistic Concurrency Control
Disadvantages and Challenges
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.