CODEWITHSUNDEEP
c#sql-serverconcurrencyscope
Anton Kruk
Anton Kruk

Reputation: 41

c# - concurrent inserts with Scope_Identitiy()

We have a dozen clients which use an Assembly for inserting data into my SQL Server 2012 in form of simple SQL statements.

There are two tables (e.g. [User] and [Location]) with an identity column and a foreign key.

The execution of the statement is always the same: create user, save the new id as foreign key for creating the location.

In pseudo code:

//open DB Connection
Connection.Open();

//Insert user
"INSERT INTO User (Name, Birthdate) VALUES ("Smith", "1.1.1919");
 SELECT SCOPE_IDENTITY();" //save the new ID in var "newID"

//Execute Statement
ExecuteQuery();

//Insert Location
"INSERT INTO Location(Country, City, User_ID) VALUES ("Germany", "Cologne", newID)"

//Execute Statement
ExecuteQuery();

//close Connection
Connection.Close();

No magic till this point...but if I run this code at the same time on multiple clients or in parallel threads is there a possibility that SCOPE_IDENTITY() retrieves the new created ID of an User created by another client/thread?

Especially is there time between the insert of the user and Scope_Identity() for another thread to insert a new user which Scope_Identity reads?

Is maybe the OUTPUT clause an alternative?

Upvotes: 1

Views: 1297

Answers (1)

rducom
rducom

Reputation: 7320

To clarify things

A session corresponds to the current connexion you have to the database (Ado.Net / EF / SSMS etc.). An application might have multiple sessions to a database.

A scope is the context of execution of a SQL command. Imagine you call some T-SQL, which calls a stored procedure, which executes some Trigger. There will be a scope for your T-SQL, then another nested scope for the code inside the stored procedure, then another scope for the code inside the trigger. So when your use SCOPE_IDENTITY, you are retrieving the last inserted PK Id inside the scope where you are.

Inherently, different sessions means different scopes.

At the opposite, @@IDENTITY returns the last inserted ID inside the session. It's not "scope-aware". If you insert in a table, and a trigger do something behind the scene, you have chances to get the ID inserted by the trigger.

Note that if you rollback a transaction where a PK Id have been generated, the PK counter don't go back to the previous value, the row is not committed because of the rollback, but a little hole will exists your table PK continuity ...

Upvotes: 2

Related Questions