Timestamp issue inside a TSQL Transaction

jspurlin posted on Monday, January 15, 2007

I am not getting an updated timestamp from the database inside a TSQL transaction. When I first select a record detail and update it, all goes well. But if I am looking at the same detail after the update and try to make another update, the error checking in the stored procedure shows that the timestamp values do not match.

Values are being returned to the Business Object, just not an "updated value". That is why the first update works and second one does not. I tried removing TransactionScope attributes from the update method in question, but the SQL response is the same. If I do not use an TQSL Transaction, I do not have the problem. The DBA and I both like the idea of SQL server checking and returning these values within the scope of a transaction, a TSQL transaction. But I cannot figure out why an updated timestamp is not returned, although it is updated when I try to do the second update and get the timestamp error.

In this case, I just use "@LastChanged" as an InputOutput param for updates. To be sure, I went back to the examples and tried this with "@NewLastChanged" as only an output parameter. The same problem occures. When looking at the script below, it is important to note that @LastChanged is sending in the current timestamp as an InputOutput param, returning the new timestamp after selecting it after the update.

Another important thing to note is that I have tried this before and after committing the transaction. If anyone has encountered this, advise would be appreciated. Here is the script:

select @TimestampCheck = LastChanged from ADD_Address where AddressId=@AddressId

if @TimestampCheck is null
    begin
        RAISERROR ('AddressId does not exist in ADD_Address: E002', 16, 1) -- AddressId does not exist.
        return -1
    end
else if @TimestampCheck <> @LastChanged
    begin
        RAISERROR ('Timestamps do not match up, the record has been changed: E003', 16, 1)
        return -1
    end

Begin Tran Address

    Update ADD_Address
    set StreetNumber= @StreetNumber, AddressLine1=@AddressLine1, StreetTypeId=@StreetTypeId, AddressLine2=@AddressLine2, AddressLine3=@AddressLine3, CityId=@CityId, StateProvidenceId=@StateProvidenceId, ZipCode=@ZipCode, CreateId=@CreateId, CreateDate=@CreateDate
    where AddressId= @AddressId

    select @error_code = @@ERROR, @AddressId= scope_identity()

if @error_code = 0
    begin
        commit tran Address

        select @LastChanged = LastChanged
        from ADD_Address
        where AddressId = @AddressId

    if @LastChanged is null
       //this error will not throw if @LastChanged is an input output param as is the case here,
       // but the new timestamp selected is the same as the one sent it. In effect, the new timestamp has
      // not been successfully returned. BUT if you try to perform a second update immediately, the new
     //timestamp value IS in place and a Timestamp conflict occurs. This helps explain error 005 which
    //is the error I get when I use @LastChanged instead of @NewLastChanged exclusively as an        //output param.
        begin
            RAISERROR ('LastChanged has returned null in ADD_Address: E004', 16, 1)
            return -1
        end
    if @LastChanged = @TimestampCheck
        begin
            RAISERROR ('LastChanged original value has not changed in ADD_Address: E005', 16, 1)
            return -1
        end
        return 0
    end

Timestamp issue inside a TSQL Transaction

jspurlin posted on Monday, January 15, 2007

Jimbo replied on Thursday, January 18, 2007