Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Here are a few examples: simple-talk.com/sql/t-sql-programming/… –  AlexKuznetsov Mar 3 '13 at 3:37
add comment

2 Answers

up vote 5 down vote accepted

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.

share|improve this answer
    
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 '13 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. –  ConcernedOfTunbridgeWells Mar 3 '13 at 16:53
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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