 Optimistic Concurrency Control with rowversion
By Kalman Toth, M.Phil. Physics, M.Phil. Comp. Science, MCDBA, MCITP
August 22, 2010
A table can have only one timestamp column. The value in the
timestamp column is updated every time a row containing a timestamp
column is inserted or updated. Of course this means we should
not use it as a primary key, because we can get many orphans quickly
if updates are performed on other columns. As a row is modified
in a table, the timestamp is updated with the current database
timestamp value obtained from the @@DBTS function.
Timestamp is the wrong name, quite confusing in fact. It has
nothing to do with time. Microsoft will rename it rowversion in
the future. Rowversion is the synonym for timestamp in SQL Server
2005 and SQL Server 2008.
It is an 8 bytes unique binary key within the database.
Here is how it looks like: 0x0000000000000743. After an update
to the row: 0x0000000000000745.
The rowversion(timestamp) starts changing as soon as the transaction begins. If the transaction is rolled back, it returns to the original value.
So how can we use it?
The main purpose is row versioning in multi user environment, in other words concurrency checking.
Pessimistic concurrency means locking the data at the row, page, or table level and don't allow anyone to modify it until the target user is done modifying and saving it back to the database. Trouble with this method: it may take a few minutes for the target user to update a record during which other users may be prevented from doing their work (locked out from the table). If the target user called away for a meeting for example in the middle of data entry, you need to unlock the table by a timeout mechanism in order to prevent disruption to data access by other users.
Optimistic concurrency means reading a record in a table and displaying it for the target user, but not locking it. Other users can read and modify the record at anytime while the target user is performing the manual update on the computer screen. When the target user releases the record for database update you need to check if someone changed it in between the initial read and the release (like 1-5 minutes). Usually this is not a problem due to the work distribution among staff, nevertheless you have to program for it to avoid conflicting updates and damage to database integrity.
Assume you are a developer and developing a program in Visual
Basic to update the name and address table of customers. There
will be 100 staff member who can perform this application function.
How can you be sure that while target staff A typing in the change, staff
X is not changing the same row?
Here is what you do:
1. Read the name and address table including the timestamp. You
display the info to the user for update and save the timestamp.
2. Certain amount of time later, like 2 minutes, the user presses
the submit button after changes were typed in.
3. You open a transaction with Begin Transaction
4. You read the timestamp of the name and address row
5. You compare the current timestamp to the saved timestamp.
6. If the timestamps are same, you update the row and commit the
transaction
7. If timestamps are different, you roll back the transaction
and notify the user about the fact that the data was changed by
someone else. You can let the user decide what to do or follow the appropriate company business rule for data entry conflict resolution. |
This is pretty common practice in multi user environment. The
alternate would be to examine a datetime column, or the entire
row which is more processing intensive.
The following example shows timestamp (rowversion in SQL Server 2008) in action:
-- SQL Server 2008 T-SQL Code
USE tempdb;
-- SQL create table for Concurrency Checking demo
CREATE TABLE Celebrity (
CelebrityID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(25),
LastName VARCHAR(30),
VERSIONSTAMP ROWVERSION)
GO
-- SQL insert - populate table
INSERT Celebrity (FirstName, LastName)
VALUES
('Jessica', 'Simpson'),
('Nick', 'Carter'),
('Stevie', 'Brock'),
('Christina', 'Aguilera'),
('Frank','Sinatra'),
('Doris','Day'),
('Elvis', 'Presley')
GO
SELECT * FROM Celebrity
GO
/* Results
CelebrityID FirstName LastName VERSIONSTAMP
1 Jessica Simpson 0x0000000000000876
2 Nick Carter 0x0000000000000877
3 Stevie Brock 0x0000000000000878
4 Christina Aguilera 0x0000000000000879
5 Frank Sinatra 0x000000000000087A
6 Doris Day 0x000000000000087B
7 Elvis Presley 0x000000000000087C
*/
-- SQL update demo: SOMEONE UPDATED RECORD since it was read
CREATE TABLE #Semaphore (ID int identity(1,1) primary key,
StartVersion bigint,
PK int)
DECLARE @MyKey int
INSERT INTO #Semaphore (StartVersion, PK)
SELECT VERSIONSTAMP, 1
FROM Celebrity WHERE CelebrityID=1
SELECT @MyKey = SCOPE_IDENTITY()
-- SIMULATION: somebody else updating the same record
UPDATE Celebrity
SET FirstName = 'Celine',
LastName = 'Dion'
WHERE CelebrityID = 1
-- We are attempting to update.
BEGIN TRANSACTION
IF (SELECT StartVersion
FROM #Semaphore
WHERE ID = @MyKey) = (SELECT VERSIONSTAMP
FROM Celebrity
WHERE CelebrityID = 1)
BEGIN
UPDATE Celebrity
SET FirstName = 'Lindsay',
LastName = 'Lohan'
WHERE CelebrityID = 1
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK - UPDATE CONFLICT'
RAISERROR ('Celebrity update conflict.',10,0)
END
DELETE #Semaphore WHERE ID = @MyKey
SELECT * FROM Celebrity
GO
/* CelebrityID FirstName LastName VERSIONSTAMP
1 Celine Dion 0x000000000000087D
2 Nick Carter 0x0000000000000877
3 Stevie Brock 0x0000000000000878
4 Christina Aguilera 0x0000000000000879
5 Frank Sinatra 0x000000000000087A
6 Doris Day 0x000000000000087B
7 Elvis Presley 0x000000000000087C
*/
-- SQL UPDATE with NO CONFLICT
DECLARE @MyKey int
INSERT INTO #Semaphore (StartVersion, PK)
SELECT VERSIONSTAMP, 1
FROM Celebrity WHERE CelebrityID=1
SELECT @MyKey = SCOPE_IDENTITY()
-- We are trying to update.
BEGIN TRANSACTION
IF (SELECT StartVersion
FROM #Semaphore
WHERE ID = @MyKey) = (SELECT VERSIONSTAMP
FROM Celebrity
WHERE CelebrityID = 1)
BEGIN
UPDATE Celebrity
SET FirstName = 'Lindsay',
LastName = 'Lohan'
WHERE CelebrityID = 1
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
PRINT 'ROLLBACK - UPDATE CONFLICT'
RAISERROR ('Celebrity update conflict.',10,0)
END
DELETE #Semaphore WHERE ID = @MyKey
SELECT * FROM Celebrity
GO
/*
CelebrityID FirstName LastName VERSIONSTAMP
1 Lindsay Lohan 0x000000000000087E
2 Nick Carter 0x0000000000000877
3 Stevie Brock 0x0000000000000878
4 Christina Aguilera 0x0000000000000879
5 Frank Sinatra 0x000000000000087A
6 Doris Day 0x000000000000087B
7 Elvis Presley 0x000000000000087C
*/
-- Cleanup
DROP TABLE #Semaphore
DROP TABLE Celebrity
------------
Related articles:
Types of Concurrency Control
ASP.NET Optimistic Concurrency Control
|
