Optimistic Locking

By now, you’ve read about several locking approaches, none of which provides the perfect combination of reliability, persistence, and concurrency. Optimistic locking won’t do that either, but it gets a lot closer.

In order to understand optimistic locking, you need to understand timestamp columns because optimistic locking depends on the timestamp column to maintain data integrity in a multiuser update application.

A timestamp column is a column created with the data type timestamp. Every table can have zero or one timestamp columns. If a timestamp column is included in a table, SQL Server automatically assigns the column a new, unique value when a row is inserted and then assigns a different value every time the row is updated. This allows you to distinguish different versions of a row.

The timestamp data type is often confused with the niladic function, CURRENT_TIMESTAMP. The niladic function actually returns a datetime value with the current date and time. The timestamp data type refers to an 8-byte, steadily increasing binary value that correlates a version of a row to a row ID in the system table, syslogs, and the transaction log. The key point: There is no usable date or time information in a timestamp value.

Let’s create a table with a timestamp column and then observe how the value of the timestamp changes as a row is updated.

create   table   ts_example
(id  int  identity  primary  key,
   descr char(4) not null,
   qty  int  not  null,
   ts  timestamp  not  null)

Timestamp columns are similar to identity columns: you don’t specify them during insert or directly modify them, and the system is wholly responsible for their maintenance. (Unlike identity columns, there is no set option that permits you to specify timestamps.)

I’ve added an identity column to the table because a unique identifier is required along with the timestamp to perform optimistic locking effectively. The name of the timestamp column is entirely up to you. I prefer ts; others use timestamp. (Timestamp is not a reserved word.)

Next I’ll add a few rows to the table. Notice that you only need to provide values for columns a and b. The other column values are provided automatically by SQL Server.

insert   ts_example   (descr,  qty)
      values   (“deer”,  99)
insert   ts_example   (descr, qty)
      values   (“wolf”, 55)
insert   ts_example   (descr, qty)
      values   (“lion”, 75)
select *
    from  ts_example

id          descr qty         ts
----------- ----- ----------- ------------------
1           deer  99          0×0000000100001202
2           wolf  55          0×0000000100001206
3           lion  75          0×0000000100001209

As you can see, each of the inserted rows has been assigned a unique timestamp value. The timestamp is the unique identifier of the row in the transaction log where the table modification was recorded. If you update a row, its timestamp value changes because the latest modification to the row is stored in a new row in syslogs.

update   ts_example
    set qty = 36
  where id = 3
select *
    from ts_example
  where id = 3

id          descr qty         ts
----------- ----- ----------- ------------------
3           lion  36          0×000000010000120c

During the modification, SQL Server changed the timestamp value from 0×…1209 to 0×…120c. That change in the timestamp indicates that a new version of the row is now stored online.

How Timestamp Values Are Used in Optimistic Locking

How does SQL Server use timestamps to manage locking? The value of a timestamp column works like a token to allow data modifications. When a user retrieves a row from the server, he also retrieves the timestamp value. When he returns that row to the server, he also returns the timestamp. If the timestamp he originally retrieved matches the timestamp still stored in the table, the update is permitted because the row has not changed since it was read. If the timestamp has changed, the row has been modified, so the update is not valid. Figure 20.5 shows the logical flow of the timestamp validation process.

Figure 20.5.  The logical flow of optimistic locking.

You should be starting to understand why this is called optimistic locking. Pessimistic locking assumes the worst: people will frequently need to work on the same rows, so we will prevent them from simultaneously reading those rows. That method was laid out earlier in this chapter in the section on persistent exclusive locks.

Optimistic locking assumes that most of the time users won’t need the same rows. Even if they need to read the same rows, they won’t usually need to update the same rows all at once. Even though the locking is optimistic, it doesn’t mean that it’s unreliable or that it fails to maintain data integrity. It does mean that a user could end up running across a lock on a row after he’s modified a local version of that row. We just need to be “optimistic” that it won’t happen too often.