SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Deadlock by getting Scope_identity()
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gpc44
Starting Member

35 Posts

Posted - 12/05/2013 :  23:27:14  Show Profile  Reply with Quote
Hi,

I need a unique value for a composite key. I have created a little table that has an IDENTITY column. I inserts a record via StoredProc., And get as output parameters the SCOPE_IDENTITY ().
In a BulkInsert from a flat file, sometimes it comes at a high load to a DEADLOCK on this place..the procedure continues to run without having the key. Is there a Methode, where i can wait for a key ???
Is there a better method to get a UniqueKey quickly?

BestReagards
Nicole

Edited by - gpc44 on 12/05/2013 23:32:32

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/06/2013 :  03:26:03  Show Profile  Reply with Quote
Could you add an IDENTITY column to the actual table itself, and include that in your composite key instead?

It sounds like you are doing

BEGIN TRANSACTION
EXEC MySP_GetID @TheID = @TheID OUTPUT
INSERT INTO MyTable(Col1, Col2, ... ColID)
VALUES(@Col1, @Col2, ... @TheID)
COMMIT

and code from other places is also using the MySP_GetID, probably for other tables, and that is causing the deadlock (NOT the act of using Scope_Identity() )
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/06/2013 :  05:01:50  Show Profile  Reply with Quote
If its a bulk insert why should you do row by row insert? if your attempt is to get all generated id values you can use OUTPUT caluse for that. You dont need to make proc process row by row just for that.
the code will be like

INSERT YourTable (col1,col2,..)
OUTPUT INSERTED.IDColumn -- INTO @TempTable
SELECT value1,value2,..
FROM Source

if you want to capture it somewhere you can do that by adding the INTO part (commented above)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 12/06/2013 :  10:59:47  Show Profile  Reply with Quote
You are talking about composite keys and identities. Perhaps you should explain in more detail what you are doing or, even better, what you WANT to do.

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000