1

My understanding of optimistic locking is that it uses a timestamp on each record in a table to determine the "version" of the record, so that when the record is access by multiple processes at the same time, each has a reference to the record's version.

Then, when an update is performed, the timestamp is updated. Before an update is committed, it reads the timestamp on the record a 2nd time. If the timestamp (version) that it has is no longer the timestamp on the record (because it's been updated since the first read), then the process must re-read the entire record and apply the update on the new version of it.

So, if anything I have stated is not correct, please begin by making clarifications for me. But, assuming I'm more or less correct here...

How does this actually manifest itself in a RDBMS? Is this 2nd read/verification enforced in the application logic (the SQL itself) or is it a tuning parameter/configuration that the DBA makes?

I guess I'm wondering where the logic comes from to read the timestamp and perform a 2nd update if the timestamp is stale. So I ask: does the application developer enforce optimistic locking, or is it enforced by the DBA? Either way, how? Thanks in advance!

1

2 Answers 2

5

The basic technique is quite straightforward. When you read the record you take a note of the version or timestamp column, e.g.

Select FooID
      ,Foo
      ,Bar
      ,TS      -- timestamp
  from Foobar
 where FooID = @FooID

When you go to write out the record you filter the write by the timestamp/version so that the write writes nothing if the timestamp/version has changed. This makes the write atomic, e.g.

update Foo
   set Foo = @foo
      ,Bar = @bar
      ,TS = @timestamp  
 where FooID = @FooID
   and ts = @timestamp

select @row_count = @@rowcount  -- specific to t-sql, but this is a system variable
                                -- that holds the number of rows affected by the
if @@rowcount = 0               -- most recent operation.  Other DBMS platforms do
    [deal with outdated record] -- this differently.

This allows an application to do the update without holding locks open. This is necessary for n-tier systems working through a connection pool, and prevents a class of deadlocks that used to be common on two-tier client-server systems.

There is nothing enforced in the database about this. It's all done explicitly by the application.

2
  • Thanks @ConcernedOfTunbridgeWells (+1) - that helps put everything in perspective for me. One quick follow up: I'm sure your code above is just a simple example, but something jumped out at me, and I just want to make sure that I am understanding the core concept correctly: In between the update and the select @rowcount = @@rowcount, couldn't another process have updated the record? In "real life", would one need to protect against this and put the updated and select inside a transaction or something to guarantee atomicity? Thanks again!
    – Mara
    Mar 3, 2013 at 14:54
  • @Mara - No. The @@rowcount is local to the session. It is a system generated variable that records the number of rows affected by the most recent DML statement. Note that particular example is specific to T-SQL. Other DBMS platforms do it slightly differently. Mar 3, 2013 at 16:53
1

The app doesn't read version column second time. You are just issuing update with extra condition in where : UPDATE table1 set .... WHERE pk_column = some_value and version_column = version_column_value_you_got_in_the_beginning. Then you check if update was successful(number of rows affected). If it's 0, then you have to re-read data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.