Post reply

Deadlock issue on partitioned view

  • April 20, 2004 at 8:16 am

    #107966

    Hi!

    I have a partitioned view as follows:

    View: recp_group

    partition_id (partitioning field, contstraint on this field)

    group_id

    recipient_id

    I am tryig to delete some rows from the view based on group_id. Since I cannot join the partitioned view with itself in a delete operation, I am doing this in 2 steps:

    SELECT recipient_id

    INTO #recipients_to_delete

    FROM  recp_group t WITH (NOLOCK)

    WHERE partition_id = @partition_id

    AND  group_id = @groupid

    DELETE

    FROM recp_group

    WHERE partition_id = @partition_id

    AND recipient_id IN (

    SELECT recipient_id FROM #recipients_to_delete

    )

    This almost always gives a deadlock error on the DELETE statement. I enabled trace 1204 and this is what I get:

    Requested By:

    ResType:LockOwner Stype:'OR' Mode: IX SPID:62 ECID:0 Ec0x3c97d4d8) Value:0x5db9af60 Cost0/3C)

    Victim:

    ResType:LockOwner Stype:'OR' Mode: IX SPID:62 ECID:0 Ec0x3c97d4d8) Value:0x5db9af60 Cost0/3C)

    From this I can make out that the process is getting into a deadlock with itself (same spid) - but I cannot understand why this is happening? Can someone explain what else I can understand from this?

    Any help is greatly appreciated.

    Thanks

    DK

  • April 20, 2004 at 9:46 am

    #503513

    Just a thought - do you have a DELETE trigger on your underlying table? Perhaps this is trying to reference the table and causing the deadlock.

    Regards

    Rob

  • April 20, 2004 at 11:02 am

    #503520

    Nope - no triggers at all.

  • May 6, 2005 at 10:08 am

    #557398

    Could it be a bug? I have heard about some deadlock issues with DISTRIBUTED partitioned views. That the underlying provider is doing something extra in a bad way.

     

    //Hanslindgren 

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply