SQL Server Performance

Issue --- Transaction (Process ID 136) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadloc

Discussion in 'General DBA Questions' started by ajashadi, Jan 26, 2010.

  1. ajashadi New Member

    Just wondering if anyone faced the SQL Server 2000 deadlock error. WE are using SQL Server 2000 and ColdFusion 7. The error returned is

    Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Transaction (Process ID 136) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    I found the stored proc which results in deadlock situation. It has lets say about 10 updates like this

    update tbl set col3 = someval where col1 = @col1 and col2 = @col2

    update tbl set col4= someval where col1 = @col1 and col2 = @col2

    update tbl set col5 = someval where col1 = @col1 and col2 = @col2
    and so on for other cols 6-10.

    col1 and col2 are part of clustered index.

    I can share the code off the list.

    Now,I do understand( from google search) that this could be because of badindexing or long processing update insert select at same time etc. Cansomeone share their experience if any with this kind of issue. It wouldbe nice to know how to tackle this issue because we are seeing thisfrequently in several applications.

    thanks.
  2. preethi Member

    1. What is the other process involved in deadlock? You can use profiler and select deadlock graph to get the other process.
    2. Why are you updating the same set of rows multiple times. you can update in a single statement
    update tbl set col3 = someval, col4 = soemval, col5 = someval ... where col1 = @col1 and col2 = @col2

Share This Page