SQL Server - optimistic lock - read timstamp back

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!
ARACK04Asked:
Who is Participating?
 
YveauConnect With a Mentor Commented:
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
 
SQL_SERVER_DBACommented:
timestamp is a binary
0
 
ARACK04Author Commented:
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
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
YveauCommented:
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
 
YveauCommented:
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
 
ARACK04Author Commented:
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
 
YveauCommented:
... 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
 
ARACK04Author Commented:
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
 
ARACK04Author Commented:
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
 
YveauCommented:
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
 
ARACK04Author Commented:
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
 
ARACK04Author Commented:
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
 
YveauCommented:
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
 
YveauCommented:
OK, good luck.
Glad I could be of any help and thanks for the grade !
0
 
ARACK04Author Commented:
.NET 2.0  I'm actually working on my O/R Mapper, and trying to put optimistic locking in.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.