5

I have a deadlock problem between an insert and a select

Connection A:

  1. Inserts a record in table MyTable (X lock on record)
  2. Updates the non-clustered index MyTable_Index (requests X lock on index entry)

Connection B:

  1. Uses MyTable_Index to locate records with a specific Destination (column of MyTable) (S lock on index entry)
  2. Returns (SELECT) the MyTable record through the index. (this is a subquery that subsequentially updates the record) (requests S lock on record)

So when A inserts (step 1) and B uses the index (step 1), a deadlock seems to occur. Connection A and B both use a ROWLOCK hint.

Is there a way to specify for connection B that MyTable_Index should not be locked, like a NOLOCK for a table, but now for the index?

Or some other clever trick (other than dropping the index or using a clustered index)?

Looking forward to some smart solutions :-)

Edwin
  • 527
  • 7
  • 15
  • Check out this question and the answers provided: http://stackoverflow.com/questions/11016935/how-can-i-force-a-query-to-not-use-a-index-on-a-given-table – digital.aaron Feb 17 '17 at 16:32
  • 1
    Are these running in transactions? If so, what transaction isolation level? have you looked at `serializable` or using `updlock` and/or `holdlock`? Showing us some actual code would go a long way here. – SqlZim Feb 17 '17 at 17:06
  • I would start an SQL trace (with SQL Profiler) or an extended event session to intercept *[xml] Deadlock graph* event. Then I would anonymize and would publish the xml here. This should.be.the fist step when we have to debug such exceptions – Bogdan Sahlean Feb 17 '17 at 17:51

0 Answers0