Optimistic Locking Using time stamp Values

When more than one user is working in a database, you might expect some concurrency problems to appear. The most common problem is the following: User A reads a record. User B also reads the record and then changes it. Any changes that user A now decides to commit to the database will overwrite the changes that user B made to it, without knowing that user B already changed it. Two standard solutions for this kind of problem are the following:

• Pessimistic locking

  Optimistic locking

In the pessimistic locking scenario, user A acquires a lock on the record so that nobody can change it until user A is finished with it. User A is a pessimist and expects that someone will attempt to change the record while user A is editing it.

Note 

I will not go into the details of how locks are implemented. Locks are covered in detail in other SQL Server hooks. At this time, it is important to know that it is possible to mark a record so that nobody else can change it.

When user A changes the record, the lock is released and user B can now access the updated record and change it.

The trouble with this solution is that user A might go out for lunch—or on vacation—and, if user A didn't close the application that retrieved the record, the lock will not be released. This scenario is one of the reasons why this kind of solution is not recommended in a client/server environment.

In the optimistic locking scenario, user A locks the record only while he or she is actually performing the change. User A is an optimist who believes that nobody will change the record while user A is editing it. A mechanism in SQL Server will notify other users if somebody has changed the record in the meantime. The user can then decide either to abandon their changes or to overwrite the updated record.

A simple way to find out whether somebody has changed a record since the time user A read it would be to compare all fields. To run this comparison, user A must keep both an "original" and a "changed" record and needs to send them both to the server. Then, a process on the server must compare the original record with the current record in the table to make sure that it wasn't changed. Only then can the record be updated with the changed record. This process is obviously slow and increases network traffic, but there are solutions in the industry that use precisely this method.

timestamp

SQL Server has a timestamp data type. It is used for versioning records in a table. When you insert or update a record in a table with a timestamp field, SQL Server "timestamps" the change. Figure 15-2 demonstrates such behavior.

Figure 15-2: Use of timestamp data type

The table is created with a timestamp field. When a record is inserted, SQL Server automatically sets its value. When the record is updated, SQL Server increases the value of the timestamp.

It is important to realize that timestamp values are not actually a kind of timekeeping. They are just binary values that are increased with every change in the database and, therefore, are unique within the database. You should not make any assumptions about their values and growth. Somebody (or some process) might change something in the database concurrently, and even two changes that you executed consecutively might not have consecutive timestamp values.

To make sure that nobody has changed a record since it was read, you should include criteria that compare original value (in variable) and current value (in column) in the Where clause:

      update #aTable      set description = 'testB '      where id = 1      and ts = OxOOOOOOOOOOOOOVE0 

The record will be updated only if the time stamp is unchanged. You can wrap such update statement in a stored procedure:

      Create Procedure dbo.ap_Contact_Update      -- update record from contact table      -- prevent user from overwriting changed record           (               @intContactId int,               @chvFirstName varchar(BO),               @chvLastName varchar(BO),               @chvPhone typPhone,               @chvFax typPhone,               @chvEma i1 typEma i1,               @insOrgUnitId smallint,               @chvUserName varchar(50),               @tsOriginal timestamp           )      As      Set nocount on           Update dbo.Contact           Set FirstName = @chvFirstName,           LastName = @chvLastName,           Phone = @chvPhone,           Fax = @chvFax,           Email = @chvEmail,           OrgUnitId = @insOrgUnitId,           UserName = @chvUserName      Where ContactId = @intContactId      and ts = @tsOriginal      return @@rowcount 

You can execute this code from Transact-SQL:

      Declare @intRecordCount int      Exec @intErrorCode = dbo.ap_Contact_Update1                           1,           'Dejan',    'Saunderic',                           '121-1112',  '111-1112', '[e-mail]',                           1,            'dejans',   0x00000000000009C3      Select @ intRecordCount intRecordCount 

You can determine if the record was updated by examining the returned value.

TSEqual() Function

The trouble with the previous solution is that you will not know what happens after the statement is executed. Maybe everything is okay and the record has been successfully changed. It is possible that the record was not updated because the time stamp was changed, but it is also possible that the record is not in the table anymore.

Old versions of SQL Server supported the TSEqual() function to compare timestamp values in the table and the Transact-SQL statement:

      update #aTable      set description = 'test4 '      where id = 1      and TSEQUAL(ts, OxOOOOOOOOOOOOOOVA) 

If they do not match, this function raises an error 532 and aborts the statement. Then you would know that the record has been changed in the meantime, and you could use appropriate error-handling actions properly (for example, the user can be prompted for further action). Unfortunately, on SQL Server 2005, you can use this function only against databases that are running in 80 (SQL Server 2000) or earlier compatibility mode (see sp_dbcmptlevel in BOL).