1

We get the next ID for our tables from a separate table with two columns, TABLE_NAME and NEXT_ID. We have a stored procedure which reads the NEXT_ID and then adds an increment to the NEXT_ID. The work is done within a transaction and the transaction contains a critical section. The critical section was added when some processes where reading the same NEXT_ID and causing key violations. Now the critical section is appearing in dead lock graphs. Can the critical section cause/help cause deadlock?

marc_s
  • 8,932
  • 6
  • 45
  • 51
Geoff Bowden
  • 105
  • 1
  • 1
  • 8
  • See this question for the best way to do this: http://dba.stackexchange.com/questions/36603/handling-concurrent-access-to-a-key-table-without-deadlocks-in-sql-server – Hannah Vernon May 01 '15 at 15:18
  • 3
    Just out of curiosity, what do you mean by "critical section" here? I understand the term generally, but wonder how you implemented that in T-SQL. – Paul White May 01 '15 at 16:12
  • Yes, this can - and will cause deadlocks. you want this to be as fast as possible. Max Vernon points you to an article using sp_getapplock to serialize access to the identity table. I would reccomend that you rewrite the procedure to use a sequence. – Spörri May 01 '15 at 16:12
  • 2
    Sequence objects may be an option, but they are not suitable for all cases and have had concurrency problems (bugs) of their own 1 2 – Paul White May 01 '15 at 16:15

0 Answers0