SQL Server Performance

deadlocked on lock | communication buffer resource

Discussion in 'T-SQL Performance Tuning for Developers' started by rsnaik75@yahoo.com, Mar 22, 2005.

  1. rsnaik75@yahoo.com New Member

    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
  2. Adriaan New Member

    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
  3. mmarovic Active Member

    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.
  4. derrickleggett New Member

    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.
  5. rsnaik75@yahoo.com New Member

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

Share This Page