Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
    Transaction 1 : SELECT distinct column_id from grid where row_id =  @P0
    Transaction 2 : INSERT INTO grid (grid_id, row_id, column_id)

    In this table grid grid_id is primary key clustered. 
    CREATE NONCLUSTERED INDEX [ix_row_id] ON grid
   (
    [row_id] ASC
   )
   INCLUDE ( [column_id]) 

How can these cause a deadlock. How to ensure a deadlock free in a simple series of select and update queries.

   <keylock hobtid="72057594070695936" dbid="28" objectname="grid" indexname="ix_row_id" id="lock151a83e600" mode="X" associatedObjectId="72057594070695936">
    <owner-list>
     <owner id="process5e3bdc8" mode="X" />
    </owner-list>
    <waiter-list>
     <waiter id="processe01d288" mode="S" requestType="wait" />
    </waiter-list>
   </keylock>
   <pagelock fileid="1" pageid="1539664" dbid="28" objectname="grid" id="lockb69420e80" mode="SIX" associatedObjectId="72057594070827008">
    <owner-list>
     <owner id="processe01d288" mode="SIX" />
    </owner-list>
    <waiter-list>
     <waiter id="process5e3bdc8" mode="IX" requestType="wait" />
    </waiter-list>
   </pagelock>
share|improve this question
3  
Can you add the rest of the deadlock graph? Also, the select query wouldn't take any kind of "X" lock (X, IX, SIX) so the insert query is deadlocking on something besides the select. Ideally, turn on traceflag 1222 and capture the deadlock in the errorlog as it provides more detailed information. –  StrayCatDBA May 10 '13 at 13:16
2  
Agreed with @StrayCatDBA. We need the entire deadlock graph, there isn't enough information to determine root cause. –  Thomas Stringer May 10 '13 at 14:35
add comment

closed as unclear what you're asking by Max Vernon, RolandoMySQLDBA, Kin, Mark Storey-Smith, Paul White Sep 28 '13 at 1:18

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

Browse other questions tagged or ask your own question.