The TSEQUAL() Function

The last piece of the puzzle is provided by the TSEQUAL() function, which is used to verify that timestamp values have not changed (and hence the row has not been modified) prior to an UPDATE operation. You use tsequal() to compare the value of the timestamp stored in the row to the version provided when the row was initially read. Let’s run through a brief example and then see how it works in a multiuser update situation.

You start by reading a row specified by the unique key, id. You need to retrieve the timestamp column as well.

select  descr,  qty,  ts
    from  ts_example
  where id = 3

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

When you update the row, you pass both the id and the timestamp value. The id is referenced directly in the WHERE clause to allow SQL Server to find the correct row in the table. On the other hand, the timestamp value should not be directly compared to the ts value online in the form,

<timestamp_column_name>  =  <timestamp_value>

as in

ts  =  0×000000010000120c

If you try to compare them directly in this way and the timestamp had changed, SQL Server would fail to find the row, but no error message would be generated. In this example, I’ll pass the wrong timestamp value, but use a direct comparison:

-- This  is  the wrong way to test a timestamp value
update   ts_example
    set qty = 38
  where id = 3
    and ts = 0×1111     -- the server will fail to find the row

(0  row(s)  affected)

As you expected, the row was not found. No row matched the criteria (id = 3, ts = 0×1111). Although data integrity is preserved, no error message warns the user of a timestamp mismatch. To properly compare timestamps, use tsequal():

-- This is the proper way to test a timestamp value
update   ts_example
   set qty = 38
 where id = 3
   and tsequal(ts, 0×1111)     -- the server finds the row but returns an
   error

Msg 532, Level 16, State 1
The timestamp (changed to 0×000000010000120c) shows
that the row has been updated by another user.

The end result is the same as before: no rows were modified. On the other hand, the error message is extremely useful because it warns of an optimistic locking problem. The row was found according to the criteria (id = 3). Only after the row is found is the timestamp compared. That comparison causes the error message to be returned.

Figure 20.6 shows you how optimistic locking works in a multiuser update situation. Both users retrieve the row, obtaining the same data, including the same timestamp value, 0×1110. The first user to attempt an update will succeed because her timestamp value still matches the value stored online.

Figure 20.6.  The timestamp value distinguishes different versions of a row.

---

Note:  Don’t be confused on this point. The order of retrieval does not matter at all. The first user to update the row gets through. Others, regardless of how long ago they originally read the row, will have an invalid timestamp value and any ultimate updates will fail.

---

The second user fails to update because the timestamp value she has (0×1110) does not match the one stored in the row (0×19c3).

Optimistic locking provides a useful method of managing data integrity in a multiuser environment. It’s not very intrusive (there are no persistent locks); its scope is limited to a row, not a page; and it provides excellent data integrity.

Drawbacks to Optimistic Locking

There are two drawbacks to optimistic locking. First, users may be inconvenienced (perhaps furious?) when a complex row modification needs to be repeated because of an optimistic locking violation.

---

Note:  You can write your application to improve usability after an optimistic locking violation. The program can compare the newly revised row to the original row to determine which columns have changed. If the modifications made by another user don’t affect the modifications made by the current user of the program, you can re-execute the UPDATE statement.

Updating requires a bit more code, but further reduces the chance that there will be a reason to frustrate the user. Updating when one user’s changes don’t affect the current user’s changes works because normally one of the two updates is due to a program, updating a ytd_sales figure for instance, which the user doesn’t care about. If the user only modifies the mailing address, their update should be allowed to happen on those fields.

---

Second, and this is quite serious, this method requires every application to participate. A single rogue user who performs updates without checking timestamp validity could wreak havoc on data integrity. To guarantee the reliability of optimistic locking, you would be wise to disallow direct updates using SQL and instead use stored procedures for all updates. The procedure could require a valid timestamp and always check tsequal() during the update process. Here is an example of a stored procedure that would handle a timestamp column properly:

create  proc  p_ts_example_update
(@id  int,
  @descr  char(4),
 @qty int,
 @ts timestamp = null)
as
if @ts is null
begin
    raiserror (“A timestamp value is required”, 16, 1)
   return 99
end
declare @err int, @rc int
update ts_example
set qty   = @qty,
    descr = @descr
 where id = @id
   and tsequal(ts, @ts)
select @err = @@error, @rc = @@rowcount
if @err <> 0
   return 98
if @rc = 0
begin
   raiserror (“Row not found: %d”, 16, 1, @id)
   return 97
end
return 0

To execute the procedure, you must pass a timestamp value.

declare @ret int
exec @ret = p_ts_example_update 3, “lion”, 36
if @ret <> 0
   print “error in update”
else
   select * from ts_example where id = 3

Msg 50000, Level 16, State 1
A timestamp  value  is  required
error  in  update

The stored procedure requires the timestamp. If the wrong timestamp is passed, the timestamp error is generated. By using a stored procedure to manage all updates, you guarantee that no applications can access the table without passing a proper timestamp value.