0

By replacing @@identity with SCOPE_IDENTITY() I am getting a deadlock, though using scope_identity() is recommended by Microsoft.

In my C# method, I have lot of insert queries for master and it's details. Does anyone know why calling scope_identity() would cause timeout or deadlock issues?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Amit Bhatt
  • 45
  • 10
  • why exactly `SCOPE_IDENTITY()`? may be cause in `INSERT`? – anatol Dec 29 '16 at 09:52
  • 1
    please share deadlock graph – TheGameiswar Dec 29 '16 at 09:54
  • if using sql server 2008,you can check below answer ,to get deadlock graph,subject to some constraints:http://dba.stackexchange.com/a/10646/31995 – TheGameiswar Dec 29 '16 at 09:56
  • 1
    there should some another region for dead lock no way it could be SCOPE_IDENTITY(). – sandeep rawat Dec 29 '16 at 10:02
  • previously using @@identity and IDENT_CURRENT(table),there is no problem,but after replacing it with scope_identity creates timeout and deadlock. – Amit Bhatt Dec 29 '16 at 10:05
  • I have posted a [table valued parameters based solution](http://stackoverflow.com/questions/34826450/fastest-way-to-perform-nested-bulk-inserts-with-scope-identity-usage/34832231#34832231) to inserting many parent-child records in a single stored procedure. You might find it useful. – Zohar Peled Dec 29 '16 at 10:20
  • moreover,We are using sql server 2012. – Amit Bhatt Dec 29 '16 at 10:48

2 Answers2

1

I think this situation can be about your code which executes parallel Inserts to table. Pinal Dave has an article about SCOPE_IDENTITY's bug about parallel inserts You should read it here ; http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/

Mehmet
  • 1,824
  • 3
  • 19
  • 28
  • I go through this article,but we are using sql server 2012.but still I am clueless that what is the problem? – Amit Bhatt Dec 29 '16 at 10:27
  • so what happens if you follow the advice in the article, does that fix anything? for example sp_configure 'max degree of parallelism', 1; – Cato Dec 29 '16 at 10:31
  • @Cato,Will it solve the problem by setting max degree of parallelism? – Amit Bhatt Dec 29 '16 at 11:08
  • I'd suggest using some of the articles suggestion, such as replacing SCOPE_IDENTITY with the OUTPUT method described - I can't say for sure, because I'm not familiar with your project, but it looks like the info the answerer gave you is likely to be related to it. – Cato Dec 29 '16 at 11:26
0

Following are the solutions I applied for deadlock in my scenario :

  1. deadlock is solved by setting row lock on insert and update query.
  2. Also I found some uncommitted transactions in code which is also another factor for creating deadlock.
  3. setting sp_configure 'max degree of parallelism', 1 as well replacing scope_identity with @@identity.
Amit Bhatt
  • 45
  • 10