none
An impossible (not any more) deadlock - Conversion deadlock with both processes holding and needing "U" lock.

    Question

  • Can anyone explain this to me? Is this a bug? As per locking concepts, this is not possible (or wasn't until I found it). The update statements are operating on the same table but different columns. One does a IF EXISTS and then issues an UPDATE statement. The WHERE clause of the IF EXISTS check and the actual UPDATE statement is the same. The other one issues the UPDATE statement directly. The locked resource is the same, the primary key.

      <resource-list>

       <keylock hobtid="72057594063093760" dbid="12" objectname="Some Procedure" indexname="PK_RAPTable" id="lockadef300" mode="U" associatedObjectId="72057594063093760">

        <owner-list>

         <owner id="processa212bc8" mode="U"/>

        </owner-list>

        <waiter-list>

         <waiter id="process5e27288" mode="U" requestType="wait"/>

        </waiter-list>

       </keylock>

       <keylock hobtid="72057594063093760" dbid="12" objectname="Some Other Procedure" indexname="PK_RapTable" id="lock6dd8500" mode="U" associatedObjectId="72057594063093760">

        <owner-list>

         <owner id="process5e27288" mode="U"/>

        </owner-list>

        <waiter-list>

         <waiter id="processa212bc8" mode="U" requestType="wait"/>

        </waiter-list>

       </keylock>

      </resource-list>

    Thanks in advance
    Shishir Khandekar


    Thank you and regards Shishir Khandekar
    Friday, October 28, 2011 12:48 AM

Answers

  • Hi Shishir,

    The scenario of conversion deadlock is transactions which hold Shared locks on the same resource, and want to convert to the shared lock to the exclusive lock to update the resource. Each one will wait for others to release the shared lock and all of them cannot release the shared lock until then take the exclusive lock. To avoid this type of deadlock, you can explicitly specify the lock hint UPDLOCK instead of shared lock. The blog below elaborated on the conversion deadlock:
    http://www.sql-server-performance.com/2004/advanced-sql-locking/

    According to your description, did you mean that two transactions with each holding an UPDATE statement lead to a conversion deadlock? Is there any shared lock before the update lock? You can use the view below to see the locks in a database before the transaction completed.

    CREATE VIEW DBlocks AS
    SELECT request_session_id as spid,
    db_name(resource_database_id) as dbname,
    CASE
    WHEN resource_type = 'OBJECT' THEN
    object_name(resource_associated_entity_id)
    WHEN resource_associated_entity_id = 0 THEN 'n/a'
    ELSE object_name(p.object_id)
    END as entity_name, index_id,
    resource_type as resource,
    resource_description as description,
    request_mode as mode, request_status as status
    FROM sys.dm_tran_locks t LEFT JOIN sys.partitions p
    ON p.partition_id = t.resource_associated_entity_id
    WHERE resource_database_id = db_id();
    
    --obtain the lock information
    select * from DBlocks order by spid,index_id,resource
    
    
    

    If the deadlock can be reproduced, please post the table’s DDL structure and sample data along with queries.

    Best Regards,
    Stephanie Lv

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Stephanie Lv Friday, November 04, 2011 11:47 AM
    • Marked as answer by Stephanie Lv Monday, November 07, 2011 10:53 AM
    Monday, October 31, 2011 3:29 AM