Deadlock by getting Scope_identity()

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Deadlock by getting Scope_identity()

Author

Topic

gpc44
Starting Member

35 Posts

Posted - 2013-12-05 : 23:27:14

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

Kristen
Test

22859 Posts

Posted - 2013-12-06 : 03:26:03

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() )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-06 : 05:01:50

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-12-06 : 10:59:47

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources