?
Solved

SQL Server - optimistic lock - read timstamp back

Posted on 2007-10-12
15
Medium Priority
?
438 Views
Last Modified: 2008-06-02
I'm trying to use optimistic locking with SQL Server.  When I run my update, I want to make sure that the timestamp I have is the most recent one, and also read in the newest value, and I'd like to do this in ONE query.  I was thinking something like

UPDATE table SET ....   WHERE keyField = @blah AND timestampField = @ts;  SELECT timestampField from table WHERE keyField=@blah;

The problem is, I'm not sure how to tell how many rows were updated.  

If I were using ExecuteNonQuery it would return the row count to me, but now I need ExecuteScalar to get me the timestamp, and so I'm not quiyte sure how to get the number of rows updated from the first part of it.

Thanks!
0
Comment
Question by:ARACK04
  • 7
  • 7
15 Comments
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
ID: 20069033
timestamp is a binary
0
 

Author Comment

by:ARACK04
ID: 20069084
I know, I want to do an update, but make sure I have the right timestamp, and then read back the number of rows that were affected from the first part - if it's 0, then I have an optimistic lock problem.  I think @@rowcount might be what I want...
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20069112
Ehm ... when you are updating multiple records, you should check each record for its timestamp.
So the
    update table
    set col1 = @parm1
    where keyfield = @key
    and timestampfield = @timestamp

will result in an update of exactly one or zero records ... so I don't think you need to bother on how many records were updated ... Due to the unique timestamp value for each record in the timestampfiled.

I don't know whether this is going to help, but if you are looking for the most recent timestamp value released in the current database, you could always check the system parameter @@DBTS. @@DBTS returns the last-used timestamp value of the current database. A new timestamp value is generated when a row with a timestamp column is inserted or updated.

Hope this helps ...



0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:Yveau
ID: 20069137
always check @@rowcount and @@error after a DML statement. It's a good habit.
Make sure to DIRECTLY fetch the two session parameters, every DML statement will change these !

so:
    update table
    set col1 = @parm1
    where keyfield = @key
    and timestampfield = @timestamp

    select @MyErr = @@error
    ,       @MyCount = @@rowcount

will work fine. This won't:
    update table
    set col1 = @parm1
    where keyfield = @key
    and timestampfield = @timestamp

    select @MyTms = @@DBTS

    select @MyErr = @@error
    ,       @MyCount = @@rowcount

In the latter case the @@error and @@rowcount reflect the result of the capturing of the @@DBTS !

Hope this helps ...


0
 

Author Comment

by:ARACK04
ID: 20069188
Yveau, that does help, a great deal.  I already know that the query I was sending would update 0 or 1 records.  I was updating one specific row, which is why I put the keyField in the WHERE clause.  I put the timestamp in the where clause to ensure taht no one else had changed the values in that row since I originally read it in, ie, optimistic locking.

I will take a look at @@DBTS - that should make my life a lot easier WRT getting the latest timestamp.
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20069218
... OK, but make sure it's not the best in all scenarios. When you do an update and before you can fetch the @@DBTS someone else does an update/insert, your fetch of the @@DBTS will reflect the latter timestamp value, and the one that is in your updated column ... So if you are sure this is not something that can happen often, you should be in the clear !

Good luck testing !
0
 

Author Comment

by:ARACK04
ID: 20069220
It seems as though @@DBTS gets the last timestamp used in the database period - is there another version of this that returns the last timestamp used in the current session?
0
 

Author Comment

by:ARACK04
ID: 20069231
Yes, exactly.  SCOPE_IDENTITY saves the last identity vlaue generated in YOUR session - is there a similar function for timestamps?

If not it seems I would jus have to manually select it out through the row key
0
 
LVL 18

Accepted Solution

by:
Yveau earned 2000 total points
ID: 20069273
No @@DBTS (DataBaseTimeStamp) per session I'm afraid ...

But thinking it over ...
    update table
    set col1 = @parm1
    where keyfield = @key
    and timestampfield = @timestamp

    select timestampfield
    from table
    where keyfield = @key

If this would give you the 'post-script' timestamp, you are able to compare the 'pre-script' timestamp and the 'post-script' timestamp in the application. Two options:
1. they are the same, the update failed -> no row was updated and @@rowcount would have been 0.
2. they are not the same, the update succeeded -> given the scenario only 1 row could have been updated and @@rowcount would have been 1.

... so in the end you can derive the @@rowcount from the 'pre-script' timestamp value and the 'post-script' timestamp value in the application.

Hope this helps ...
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20069308
and what if you create a stored procedure using output parameters:

create proc MyProc
    @parm1 int
,   @key int
,   @timestamp timestamp
,   @newtimestamp timestamp output
,   @err int output
,   @count int output
as
begin
    update table
    set col1 = @parm1
    where keyfield = @key
    and timestampfield = @timestamp

    select @err = @@error
    ,       @count = @@rowcount

    select @newtimestamp = timestampfield
    from table
    where keyfield = @key
end

That way you can retutn all three values to your app.

hope this helps ...
0
 

Author Comment

by:ARACK04
ID: 20069316
Let's say I read the data, and the TS is 1

You update the data and the TS is now 2

I attempt to update the data - no update is made since the WHERE fails, and when I query the current TS, I get 2.  How would I be able to tell whether that 2 was put there by you (or someone else), or put there by the update I just attempted to execute.  I would think I would need @@rowcount for that

Thank you very much for all your help - I wish I could give you more than just 500 points :)
0
 

Author Comment

by:ARACK04
ID: 20069319
the stored procedure would definitely work - I could manually compare, and send back whatever error mess I wanted.  That's just not an option right now.  I think I'll have to just use @@rowcount
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20069327
Rats ... thats a good one. Didn't think of that scenario !
How about the procedure approach ? Is that an option for you ?

Just curious, in what environment are you programming the front end ?

0
 
LVL 18

Expert Comment

by:Yveau
ID: 20069333
OK, good luck.
Glad I could be of any help and thanks for the grade !
0
 

Author Comment

by:ARACK04
ID: 20069337
.NET 2.0  I'm actually working on my O/R Mapper, and trying to put optimistic locking in.  
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question