Re: Beware: Timestamps are not contained inside Transactions!!
From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 01/20/05
- Next message: Dolph Larson: "Bind failed on TCP port 1433"
- Previous message: Rob Diamant: "Re: Fast Sequencing"
- In reply to: Dave Slinn: "Beware: Timestamps are not contained inside Transactions!!"
- Next in thread: Louis Davidson: "Re: Beware: Timestamps are not contained inside Transactions!!"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 20 Jan 2005 17:03:36 -0500
A slightly more performant workaround than selecting the value from the
table might be to get it within the scope of a trigger, from the INSERTED
table... Something like:
use tempdb
go
create table stamp (id int, tstamp timestamp)
go
create trigger returnstamp
on stamp
for insert
as
select tstamp
from inserted
go
insert stamp (id)
values (123)
go
drop trigger returnstamp
drop table stamp
go
-- Adam Machanic SQL Server MVP http://www.sqljunkies.com/weblog/amachanic -- "Dave Slinn" <dslinn@accesscomm.ca> wrote in message news:OlXh$kz$EHA.2880@TK2MSFTNGP14.phx.gbl... > Our databases have tables that use a Timestamp column for row-level > concurrency checking. > > All of our Insert and Update stored procedures have an OUTPUT parameter that > is supposed to return the value of @@DBTS (which is the last assigned > Timestamp for the current database). This value is then passed back into > the sprocs when a record is to be updated to check if that row has been > modified by another party between updates. > > The PROBLEM is when you have multiple inserts happening at the same time, it > is possible that the @@DBTS value does not accurately return the Timestamp > value for the row just inserted (or updated) in the previous T-SQL batch, > EVEN IF you have wrapped a BEGIN TRAN and COMMIT TRAN around both the INSERT > [record] and SET @returnedTimestamp = @@DBTS statements. > > The steps we used to recreate/test this scenario follows: > 1 - A transaction is opened and a new record is inserted into table A > 2 - On a separate connection, another transaction is opened and a new record > is inserted into table B > 3 - A call to retrieve the value of @@DBTS and then commit the transaction > on the first transaction (Table A) returns a value > 4 - A call to retrieve the value of @@DBTS and then commit the second > transaction (on Table B) returns the SAME value that was returned in step 3. > > Basically, the workaround is to NOT rely on @@DBTS, as it is not guaranteed > to the value you were expecting. Instead we need to perform a SELECT for > the Timestamp column on the row that was just Inserted or Updated, and > return that value, as it accurately reflects the Timestamp for that row. > > >
- Next message: Dolph Larson: "Bind failed on TCP port 1433"
- Previous message: Rob Diamant: "Re: Fast Sequencing"
- In reply to: Dave Slinn: "Beware: Timestamps are not contained inside Transactions!!"
- Next in thread: Louis Davidson: "Re: Beware: Timestamps are not contained inside Transactions!!"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
