SQL Server Performance

dead lock

Discussion in 'General DBA Questions' started by snarayanas, Apr 23, 2006.

  1. snarayanas New Member

    Hello All,

    Below are i am getting from Application side, can i know the reason. Is it application error or database issue. Please let me know.


    Error state: 50 Severity: 13
    Source: Microsoft OLE DB Provider for SQL Server
    Error message: Transaction (Process ID 231) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    )
    2006/04/24 00:18:18.022: <E> [CS,SCU:3380] Database Error:
    HRESULT: HR=0x80004005, Unspecified error
    SQLSTATE: 001C663C Native Error: 11
    Error state: 1 Severity: 16
    Source: Microsoft OLE DB Provider for SQL Server
    Error message: [DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.

    Please advise

    Sirigineedi
  2. satya Moderator

    Due to the poor application design, some of the incorrect settings on the server, or poorly written transactions, the locks can conflict with other active locks. nappropriate use of locking hints can be yet another cause of blocking. If you force SQL Server to acquire 50000 row level locks, your transaction might have to wait until other transactions complete, and this many locks are available.

    KBAs:
    http://support.microsoft.com/?kbid=832524
    http://support.microsoft.com/?kbid=898626

    http://www.sql-server-performance.com/blocking.asp
    .. the abvoe links should help you to understand and resolve dead locking.

    HTH

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  3. mmarovic Active Member

    Deadlock means there are at least two transactions locking resources other transaction needs. For example:

    1. Transaction 1 locks row 1 from table 1.
    2. Transaction 2 locks row 1 from table 2.
    3. Transaction 1 attempts to lock row 1 from table 2 but it has to wait for transaction 2 to release the lock.
    4. Transaction 2 attempts to lock row 1 from table 1 but it has to wait for transaction 1 to release the lock.
    5. Both transactions hold their locks waiting for another to complete and release the lock.
    6. SQL Server enging detects the deadlock, selects one transaction as a deadlock victim and kills it.

    Deadlocks occasionally occur during paralell executions because of bugs in paralell execution algorithm.
  4. Madhivanan Moderator

Share This Page