0

It is common practice to pick up the newly created IDENTITY of a table using the @@IDENTITY variable after an INSERT.

Is it just equality correct to retrieve the last rowversion value following an UPDATE in a similar manner using the @@DBTS value?

For example:

IF(OBJECT_ID('XXX') IS NOT NULL)
    DROP TABLE XXX
GO
CREATE TABLE XXX
(
    ID int IDENTITY(1,1) PRIMARY KEY,
    Name varchar(64) NOT NULL,
    RV rowversion
)
GO
INSERT INTO XXX(Name) VALUES 
    ('Apples'),('Bananas'),('Cranberries'),('Dragon Fruit'),('Eggplant'),('Fig'),('Grape')
GO
SELECT * FROM XXX
GO
UPDATE XXX
    SET Name = 'Chocolate' WHERE ID = 3
PRINT @@DBTS
GO

Now is @@DBTS safe from concurrent updates?

If another connection performs insert and updates between the UPDATE and the PRINT, would we end up with the rowversion of the 'other' connection rather than the one from our own update?

ughai
  • 9,830
  • 3
  • 29
  • 47
Chris Walsh
  • 3,423
  • 2
  • 42
  • 62
  • 2
    Using `@Identity` does not handle concurrency or consequent inserts happen within another scope (eg. in a trigger). Normally one would use [SCOPE_IDENTITY](https://msdn.microsoft.com/en-us/library/ms190315.aspx). – Richard Apr 29 '15 at 10:24
  • 1
    `@@DBTS` is not safe for concurrent operations on database. you are not guaranteed that the version is related to your `insert/update/delete` – ughai Apr 29 '15 at 10:30
  • Thanks. I suspected as much with `@@DBTS` but I didn't realise `@@IDENTITY` was also not thread safe. I have some refactoring work to do! – Chris Walsh Apr 29 '15 at 10:39

1 Answers1

0

According to MSDN @@DBTS

returns the last-used timestamp value of the current database

This means it is not thread safe.

You also should not use @@IDENTITY. @@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope. @@IDENTITY can for example return the wrong value if a trigger gets executed on a table with an identity column.

ughai
  • 9,830
  • 3
  • 29
  • 47
Alex
  • 21,273
  • 10
  • 61
  • 73