CODEWITHSUNDEEP
sqlsql-server
JamesNT
JamesNT

Reputation: 57

Using Identity in SQL Server without race condition

So let's say you have a table of Patients with an IDENTITY(1,1) for the primary key. By using @@Identity, how do we avoid a race condition where two people may save a new patient at the same time? Obviously, duplicate ID's in the Patients table would not be created, but what if the application needed the ID for one of the inserted patients to update a record in another table elsewhere? How do we know that @@Identity won't get the ID of the other record if both are inserted at the same time?

Or is there a best practice for avoiding this?

JamesNT

Upvotes: 1

Views: 784

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

Yes, there is a best practice. Don't use @@Identity.

The safest way to get the identity values assigned in an insert statement is to use the OUTPUT clause. You should start with the documentation.

This has numerous advantages:

  • It does not get confused by triggers and nested statements.
  • It can handle multiple inserts at the same time.
  • It can return the values of other columns, not just the identity column.
  • It specifically returns the rows affected by the transaction, so you don't even think about sessions, users, or anything else.

Upvotes: 3

Sean Lange
Sean Lange

Reputation: 33581

@@IDENTITY will not cause a race condition but it is NOT best practice either. You should instead be using SCOPE_IDENTITY.

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Upvotes: 3

Related Questions