Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Deadlock on Indexed view

Author  Topic  netedk Starting Member 11 Posts Posted - 2004-04-20 : 10:16:42 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 Ec:(0x3c97d4d8) Value:0x5db9af60 Cost:(0/3C) Victim: ResType:LockOwner Stype:'OR' Mode: IX SPID:62 ECID:0 Ec:(0x3c97d4d8) Value:0x5db9af60 Cost:(0/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