Concurrency issue in SQL server 2008

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 i开发者_JS百科ssue.

I would use the SCOPE_IDENTITY() in this case.

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

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.

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