deadlocked on lock | communication buffer resource

Hi,

I am getting this following error when I try to run a procedure.
(Process ID 57) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction

This is related to delete. if I replace the delete statement with truncate then it works fine. We are trying to run these procedures in stages instead of running it at one go.

DELETE FROM TEST_JUNK_DELETE WHERE CAST(roll_number AS NUMERIC) between @start_num AND @end_num.

I tried used all kinds of optimizers rowlock etc. and these are not allowed NOLOCK, READUNCOMMITTED and READPAST.

Can anyone please help!!

Thanks in advance.

Rajendra Naik

Play around with the rowcount setting until you find the right one for you ...

SET ROWCOUNT 1000

WHILE EXISTS (SELECT * FROM TEST_JUNK_DELETE)
BEGIN
DELETE FROM TEST_JUNK_DELETE
END

SET ROWCOUNT 0

Your query uses table (or clustered index) scan because it is not posible to use index on roll_number.
If roll_number is allways numeric why don't define that column as such?
Than you could put index on that column and avoid locks.

However, error messages looks strange, can you check execution plan? It is possible that parallel execution plan is involved as well and microsoft still haven't resolved all bugs related to paralelism.

Btw. I like Adriaan's solution.

Have you looked at "deadlocks, troubleshooting". Also, are you on the latest service pack Rajendra? Set the MAXDOP option to not allow parallelism for this particular operation and see if that resolves.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Thank you all very much. I used "Adriaan" solution. Also DBA's are looking into MAXDOP option. Thanks agian for you help!