CODEWITHSUNDEEP
sql-server
Evgeniy Berezovsky
Evgeniy Berezovsky

Reputation: 19248

SELECTing everything that has changed since the last SELECT

Leaving aside probably better ways to get "table diffs" from an SQL server - suppose I have a table with an UpdTime column of type datetime, which is set and updated using getdate() on every insert and update, like so:

UPDATE mytable SET Updtime=getdate(), ... WHERE ...
INSERT INTO mytable VALUES (getdate(),...)

After an initial

SELECT getdate() AS Now, ... FROM mytable WITH(readcommitted,rowlock)

where I once read all rows and store the value of Now in a variable lastNow, I then continue to do periodical

SELECT getdate() AS Now, ... FROM mytable WITH(readcommitted,rowlock) WHERE UpdTime >= lastNow;

calls, always storing the new Now into my lastNow variable.

Can I be sure to never miss a change (except DELETEs, obviously)? Or is this dependent on some settings?

Btw, seeing a change twice doesn't bother me, only missing a change would.

Edit I just added WITH(readcommitted,rowlock) to the SELECT statements above, because that's how the old statements, to which I want to add the "diff" feature, look like.

Upvotes: 2

Views: 132

Answers (2)

Martin Smith
Martin Smith

Reputation: 453426

getdate() is a runtime constant function meaning it is evaluated once and the same value is used throughout the statement.

It would certainly be possible at default readcommitted level for an UPDATE statement to begin and evaluate GETDATE() then for a SELECT statement to begin and get a later GETDATE() value before the UPDATE has finished updating all rows.

If the SELECT is locating the rows in a different order than the UPDATEs (perhaps the two use different index access methods) or the UPDATE has a blocking operator in the plan (e.g. for halloween protection) then the SELECT could read rows before they have yet been updated. These rows would not then be read at the next request as the eventual Updtime they will receive is less than the lastNow value.

If the UPDATE plan was to contain a blocking operator maybe this would even be possible under serializable.

Upvotes: 1

Ian P
Ian P

Reputation: 1724

I'm not really sure what you question is, because "Can I be sure to never miss a change (except DELETEs, obviously)? Or is this dependent on some (transactional?) settings?" seems to imply that if a change is made (and then rolled back) you want to see it? I'm not sure if you want to see if an attempted change has been made?

You will not see the change unless your select contains the 'read dirty' hint. If it does, you will see all committed changes and all uncomitted changes, but not those rolled back, as they no longer exist. With read dirty you will get both original and uncommitted (updated) rows returned.

The other aspect of using getdate() is that it is granular down to 1000th sec, which used to be ok, but with modern parallel processors it is quite possible to get mutiple updates with the same GetDate() value.

Upvotes: 0

Related Questions