Multiple users frequently attempt to change the same data simultaneously in real-world database systems. Inconsistent records, overwriting of values, and data conflicts may result from improper handling. SQL Server employs Optimistic Concurrency Control (OCC) to manage such circumstances securely. It thinks that conflicts are uncommon and only looks for them while changing the data, rather than forcefully locking the data.
Using straightforward language and real-world examples, this step-by-step tutorial will teach you how to create optimistic concurrency management in SQL Server.
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
In SQL Server, optimistic concurrency control is an effective method of managing data conflicts without the need for complex locking methods. You can identify conflicts and stop accidental data overwrites by employing a RowVersion column and verifying it during updates. When update failures are handled properly, this method guarantees both data integrity and user experience, making it perfect for high-performance, scalable applications where conflicts are uncommon.
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
