CODEWITHSUNDEEP
c#sqlsql-serverconcurrency
Chedy2149
Chedy2149

Reputation: 3051

Is it safe to get the last value of an auto incremented primary key with a select statement?

Let's assume the following simple table in SQL Server 2012:

CREATE TABLE Person (
   Id INT IDENTITY(1, 1) PRIMARY KEY,
   Name NVARCHAR(50)
)

And the following corresponding class in C#:

public class Person 
{
    public int Id {get; set;}
    public string Name {get; set;}
}

Consider now the following CreatePerson method (pseudo code):

public static Person CreatePerson(string name) {
    ...
    DB.Execute("INSERT INTO Person(Name) VALUES (name)", name);
    int lastId = DB.Get("SELECT MAX(Id) FROM Person");
    return new Person {Id = lastId, Name=name};
}

This method creates a new person row in the db, builds a new Person object and then it returns the created object that is supposed to contain the database generated Id.

Is it safe and/or correct (from a concurrency perspective) to get the last generated Id by using the following SELECT statement?

SELECT MAX(Id) FROM Person

I am concerned about the following scenario:

  1. USER A Inserts a person 1
  2. USER B Inserts a person 2
  3. USER A reads the last Id and thinks that it is the Id of person 1

Note that USER A and USER B here refers to threads handling the two separate queries to the database.

Upvotes: 0

Views: 121

Answers (2)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

No it is not safe,you can use this procedure for data insert and get your lastId by using scopre_Identity function

create PROCEDURE Your_procedure_for_insert
@Name      VARCHAR(50),
@lastID      INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO Person
            (
            Name                
            )
VALUES  
            (
            @Name 
            );

SET @lastID = SCOPE_IDENTITY();

END;

Upvotes: 2

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

If you wanted to get the last identity value inserted by a person on that particular scope, use Scope_Identity().

If you wanted to get the last identity value inserted to a particular table across the scope, you can go with @@identity.

If you wanted to get the current identity value of a particular table, use Ident_Current ('Yourtablename').

Upvotes: 4

Related Questions