Post reply

Deadlocks

  • May 7, 2004 at 4:23 pm

    #85010

    Is there an easy was to detect o monitor for deadlocks. I need to know when and what tables are involved. What other tables are lock at this time.

  • May 8, 2004 at 12:59 am

    #505726

    To monitor all connections for deadlocks and get deadlock info printed to the sql errorlog, enter:

        dbcc traceon (-1, 1204, 1205)

    To stop this monitoring:

        dbcc traceoff (-1, 1204, 1205)

     

    Keep an eye on the errorlog.  If there's a lot of deadlocks it will grow rapidly.

     

     


    Cheers,
    - Mark

  • May 10, 2004 at 8:08 am

    #505819

    Have you thought about running a trace against the server to capture the textdata information (that is related to deadlock only) and save this trace into Table

    and then on this table make a triger that inform you (by e-mail or net send ) that there is a deadlock happend

    so that you can go back and see what that spid and SQL statements was attempting to execute at the time of the deadlock

    I hope this help u


    Alamir Mohamed
    Alamir_mohamed@yahoo.com

  • May 10, 2004 at 10:00 am

    #505838

    Executing sp_who2 from Query Analyzer is a quick way to see if you have any existing blocks.  It allows you to see what blocked by another spid at the time sp_who2 is executed.

  • May 10, 2004 at 10:29 am

    #505843

    I looked into using alerts for this, you can detect deadlocks/per sec, and fire an email accordingly.

  • May 10, 2004 at 10:37 am

    #505844

    Deadlocks? Or Locks owned by orphaned sessions?

    Some times deadlocking is confused with normal blocking. When one transaction has a lock on a resource that another transaction wants, the second transaction waits for the lock to be released. In this case the second transaction is blocked, not deadlocked.

    Deadlocking

    A deadlock occurs in multi-threaded systems such as SQL Server. Let’s assume that a SQL Server thread acquires a lock. If the lock being acquired is currently owned by another thread, the first thread has to wait for the owning thread to release the lock. In this case the waiting thread has a dependency on the owning thread for that particular lock.

    If the owning thread wants to acquire another lock that is currently owned by the waiting thread, the situation becomes a deadlock. Neither the first nor the second thread can release the resources they own until their transactions are committed or rolled back, and their transactions cannot be committed or rolled back because they are waiting on resources the other owns.

    SQL Server automatically performs deadlock detection. After a deadlock is identified, SQL Server ends a deadlock by choosing the thread running the transaction that is least expensive to undo as the deadlock victim. SQL Server rolls back the deadlock victim's transaction, notifies the thread's application, cancels the thread's current request, and then allows the transactions of the other thread to continue.

    The Lockeadlock, Lockeadlock Chain, event classes can be used to monitor deadlocks and list the database objects involved. This information may be useful to analyze the application code and make changes to minimize deadlocks.

     For more information about deadlocks, see Identifying Deadlocks, and Troubleshooting in BOL.

     

    Orphaned Sessions

    An orphaned session is a session that remains open on the server side after the client has disconnected.

    Normally orphan sessions occur if a client connection breaks suddenly (power failures on the client computer, client computer is powered off without performing a proper shutdown, hung application that don’t complete executing) from the server. In this case, the client process is unable to notify the network to close the connection. Therefore, SQL Server continues to keep locks owned by the client until they are killed.

    The orphan session takes up one of the SQL Server network connections. Since the maximum number of connections is dependent on the client licenses, orphaned sessions may prevent other clients from connecting. Orphan sessions also use server resources, and may have locks, open cursors, and temporary tables. These locks may block other connections from completing transactions. In overwhelming situations it can appear that SQL Server has stopped working. An obvious case of an orphan connection is if the client computer is down.

    To release the locks held by an orphan SQL Server session use the KILL statement

    Note: Before you kill a session, review “Orphan Sessions”, “Customizing the Lock Time-out” in SQL Server BOL.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply