Concurrency issue in SQL server 2008

Question

I have 2 tables- master and detail table. Master Table has ID column which is Primary key and Auto Id. I have wrtten an SP to insert records into both tables. In this SP, when a record id inserted in master table, current_ident is fetched and stored in a variable which is then passed to detail table insert the detail records. This is working absolutely fine when transactions are done one by one. But when multiple users concurrently create the transactions sometimes the detail records of one transaction are getting inserted for some other transaction which is a dangerous thing. Please help me to resolve this issue.

Answer 1

I would use the SCOPE_IDENTITY() in this case.

http://msdn.microsoft.com/en-us/library/ms190315.aspx

Answer 2

Have you got your transactions setup correctly? Your SP should look something like this

BEGIN TRAN

DECLARE @id INT

INSERT INTO Master (x, y , z) ...
SELECT @id = @@identity

INSERT INTO Child (id, foo, bar) VALUES (@id, ...

COMMIT TRAN
RETURN 0

:Err
ROLLBACK
RETURN 1

Assuming this is correct you need to check the default isolation level of your server, and the isolation level of the connections executing your stored procedure, but its more likely a bug in the SP

Answer 3

use SCOPE_IDENTITY() (Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch.) instead of @@IDENTITY.

Answer 4

I would just use SCOPE_IDENTITY() for this.

In SQL Server 2008 (looking at the question title not the tag) this is possible without but the syntax is pretty clunky.

USE tempdb

CREATE TABLE [master] (id INT IDENTITY (10,1) PRIMARY KEY,
                       othercol VARCHAR(10));

CREATE TABLE details (id INT IDENTITY PRIMARY KEY, 
                      masterid INT FOREIGN KEY REFERENCES [master](id),
                      detailscol VARCHAR(10));

SET XACT_ABORT ON
BEGIN TRAN     

DECLARE @details VARCHAR(10) = 'details row'
DECLARE @master VARCHAR(10) = 'master row'

INSERT INTO details
                      (masterid, detailscol)
  EXEC sp_executesql
    @stmt = N'
     INSERT INTO master
                      (othercol)
               OUTPUT INSERTED.id AS master_id, @details VALUES (@master)',
    @params = N'@details varchar(10), @master varchar(10)',
    @details = @details,
    @master = @master;


 SELECT * FROM [master]
 SELECT * FROM [details]
 COMMIT