CODEWITHSUNDEEP
sql-serverlocking
LuigiEdlCarno
LuigiEdlCarno

Reputation: 2415

SQL Server READ_COMMITED_SNAPSHOT inside of trigger

Due to repeated deadlocks caused by temporary heavy load (around 10 sec.), I wanted to change the isolation level of our database form READ_COMMITED to READ_COMMITED_SNAPSHOT.

I have tried to read as much as possible about it, but there is one thing I am absolutely unsure of: How will this affect triggers inserting data and then reading the ID of the inserted row?

There are 2 possibilities here (assuming that there are rows with lower IDs present) and assuming those 2 rows are inside a trigger on some other

1) 

Insert into table (ID) value (3);
Select Top 1 ID from table order by ID desc;

2)

Insert into table (ID) value (3);
Select SCOPE_IDENTITY();

In theory these should be fine, but Id'd like confirmation. Thanks

Upvotes: 0

Views: 113

Answers (1)

HABO
HABO

Reputation: 15831

Use either Scope_Identity() or an OUTPUT clause to get the identity assigned to new rows. They do not suffer from the race conditions that a SELECT allows. The isolation level should not present a problem.

OUTPUT is a good thing to have in your bag of tools for a variety of reasons. It works for INSERT, DELETE, UPDATE and MERGE statements. It isn't limited to a single row. It does allow capture of multiple columns, not just an identity value.

Do not use @@IDENTITY, for it gets values from the dark side. (Unless you need a source of unpredictable values.) IDENT_CURRENT() is also rarely useful.

Upvotes: 2

Related Questions