Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - A query always ran in split second and suddenly it started to timeout

  • A query always ran in split second and suddenly it started to timeout. A quick look at the error log the cause was deadlock. What is a Deadlock and how to identify and resolve a Deadlock inside SQL Server 2008 R2?

    Posted on 01-09-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

10  Answers  

Subscribe to Notifications
  • Score
    7

    Deadlocking occurs when two user processes have locks on separate objects and each process is trying to acquire a lock on the object that the other process has. Usualy, they have different incompatible locks which are illegal or conflict (as per the compatible mode on locks).When this happens, SQL Server identifies the problem and ends the deadlock by automatically choosing one process and aborting the other process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.

    Identify Deadlock:

    1. Using SQL Profiler
    2. Using System_health extended event(It highly depends on the ring buffer)
    3. Using Server side trace (DBCC TRACEON (1204) )
    4. Using SQL Error Log with a trace
    5. Predefined notification functionalities to log the deadlock info using service broker, extended events etc.

    Here are some tips on how to avoid/resolve deadlocking on your SQL Server:

    1. Ensure the database design is properly normalized.

    2. Have the application access server objects in the same order each time.

    3. During transactions, don’t allow any user input. Collect it before the transaction begins.

    4. Avoid cursors.

    5. Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server.

    6. Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.

    7. If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.

    8. Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.

    9. If appropriate, use as low of an isolation level as possible for the user connection running the transaction.

    10. Consider using bound connections.

    11. Adding missing indexes to support faster queries

    12. Dropping unnecessary indexes which may slow down INSERTs for example

    13. Redesigning indexes to be "thinner", for example, removing columns from composite indexes or making table columns "thinner" (see below)

    14. Adding index hints to queries appropriately(I dont prefer this mostly, but it has got its own scope)

    15. Redesigning tables with "thinner" columns like smalldatetime vs. datetime or smallint vs. int

    16. Modifying the stored procedures to access tables in a similar pattern

    17. Keeping transactions as short and quick as possible: "mean & lean"

    18. Removing unnecessary extra activity from the transactions like triggers

    19. Removing JOINs to Linked Server (remote) tables if possible

    20. Implementing regular index maintenance; usually weekend schedule suffices; use FILLFACTOR = 80 for dynamic tables (Needs a good evaluation)

    The list really goes on. The solution will vary from situation to situation.

    Eg:
    To create a deadlock you can issue commands similar to the commands below. 
    
    1 --open a query window (1) and run these commands 
    begin tran 
    update products set supplierid = 2 
    
    2 -- open another query window (2) and run these commands 
    begin tran 
    update employees set firstname = 'SQL' 
    update products set supplierid = 1 
    
    3 -- go back to query window (1) and run these commands 
    update employees set firstname = 'SQL Server' 
    
    At this point SQL Server will select one of the process as a deadlock victim 
    
    and roll back the statement 
    
    4 --issue this command in query window (1) to undo all of the changes 
    rollback
    
    5 --go back to query window (2) and run these commands to undo changes 
    rollback
    
    Replied on Jan 9 2012 1:07AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    8

    What is a Deadlock ?

    A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. The following graph presents a high level view of a deadlock state where:

    • Task T1 has a lock on resource R1 (indicated by the arrow from R1 to T1) and has requested a lock on resource R2 (indicated by the arrow from T1 to R2).

    • Task T2 has a lock on resource R2 (indicated by the arrow from R2 to T2) and has requested a lock on resource R1 (indicated by the arrow from T2 to R1).

    • Because neither task can continue until a resource is available and neither resource can be released until a task continues, a deadlock state exists.

    The SQL Server Database Engine automatically detects deadlock cycles within SQL Server. The Database Engine chooses one of the sessions as a deadlock victim and the current transaction is terminated with an error to break the deadlock. Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process

    Deadlocks are sometimes called a deadly embrace.

    What can cause deadlocks?

    • Locks

    • Worker threads

    • Memory

    • Parallel query execution-related resources

    • Multiple Active Result Sets (MARS) resources.

           SQL Server contains a deadlock detector which is capable of noticing the majority of cases of deadlocks that occur. When it notices a deadlock, it will pick one of the processes and automatically kill it. The client of that process will receive an error message saying that their process was involved in a deadlock and picked as the deadlock victim. The client of the other process will not receive any messages.
    

    It is not necessary, not will it likely be possible, to manually kill a connection involved in a deadlock. The SQL deadlock detector takes care of that.

    How to identify a deadlock

    The following message is given when the server resolves a deadlock situation:

    Server: Msg 1205, Level 13, State 50, Line 1

    Transaction (Process ID 55) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    Deadlocks trace output can be examined in SQL Server log.

    Deadlocks can found using System_health extended event

    Deadlocks can found Using Server side trace (DBCC TRACEON (1204) )

    Predefined notification functionalities to log the deadlock info using service broker, extended events etc.

    In SQL Server 2005 and SQL Server 2008, the Deadlock graph trace feature of SQL Server Profiler provides visualization of deadlocks.

    Identify deadlock - Please Click1 ::

    Identify deadlock - Please Click2::

    Typical methods you can use to resolve deadlocks include:

    • Adding and dropping indexes.

    • Adding index hints.

    • Modifying the application to access resources in a similar pattern.

    • Removing activity from the transaction like triggers. By default, triggers are transactional.

    • Use of try catch

    • Keeping transactions as short as possible.

    /*
    Below are the steps to generate a deadlock so that the behaviour of a deadlock can be illustrated:
    */
    
    -- 1) Create Objects for Deadlock Example
    USE TEMPDB 
    
    CREATE TABLE dbo.foo (col1 INT)
    INSERT dbo.foo SELECT 1
    
    CREATE TABLE dbo.bar (col1 INT)
    INSERT dbo.bar SELECT 1
    
    -- 2) Run in first connection
    BEGIN TRAN
    UPDATE tempdb.dbo.foo SET col1 = 1
    
    -- 3) Run in second connection
    BEGIN TRAN
    UPDATE tempdb.dbo.bar SET col1 = 1
    UPDATE tempdb.dbo.foo SET col1 = 1
    
    -- 4) Run in first connection
    UPDATE tempdb.dbo.bar SET col1 = 1
    
    /*
    Connection two will be chosen as the deadlock victim
    
    ie.
    
    Server: Msg 1205, Level 13, State 50, Line 1
    Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    
    */
    

    How to Minimize Deadlocks

    Although deadlocks cannot be completely avoided, following certain coding conventions can minimize the chance of generating a deadlock. Minimizing deadlocks can increase transaction throughput and reduce system overhead
    Because fewer transactions are:

    • Rolled back, undoing all the work performed by the transaction.

    • Resubmitted by applications because they were rolled back when deadlocked.

    To help minimize deadlocks:

    Access objects in the same order.

    If all concurrent transactions access objects in the same order, deadlocks are less likely to occur.  For example, if two concurrent transactions obtain a lock on the Supplier table and then on the Part table, one transaction is blocked on the Supplier table until the other transaction is completed. After the first transaction commits or rolls back, the second continues, and a deadlock does not occur. Using stored procedures for all data modifications can standardize the order of accessing objects.
    

    Avoid user interaction in transactions.

    Avoid writing transactions that include user interaction, because the speed of batches running without user intervention is much faster than the speed at which a user must manually respond to queries, such as replying to a prompt for a parameter requested by an application.
    

    Keep transactions short and in one batch.

    A deadlock typically occurs when several long-running transactions execute concurrently in the same database. The longer the transaction, the longer the exclusive or update locks are held, blocking other activity and leading to possible deadlock situations.

    Keeping transactions in one batch minimizes network roundtrips during a transaction, reducing possible delays in completing the transaction and releasing locks.

    Use a lower isolation level.

    Determine whether a transaction can run at a lower isolation level. Implementing read committed allows a transaction to read data previously read (not modified) by another transaction without waiting for the first transaction to complete. Using a lower isolation level, such as read committed, holds shared locks for a shorter duration than a higher isolation level, such as serializable. This reduces locking contention.

    Use a row versioning-based isolation level.

    When the READCOMMITTEDSNAPSHOT database option is set ON, a transaction running under read committed isolation level uses row versioning rather than shared locks during read operations.

    Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
    

    Use snapshot isolation.

    Snapshot isolation also uses row versioning, which does not use shared locks during read operations. Before a transaction can run under snapshot isolation, the ALLOWSNAPSHOTISOLATION database option must be set ON.

    Use bound connections.

    Using bound connections, two or more connections opened by the same application can cooperate with each other. Any locks acquired by the secondary connections are held as if they were acquired by the primary connection, and vice versa. Therefore they do not block each other.

    Here are some tips on how to avoid deadlocking on your SQL Server:

    • Ensure the database design is properly normalized.

    • Have the application access server objects in the same order each time.

    • During transactions, don’t allow any user input. Collect it before the transaction begins.

    • Avoid cursors.

    • Keep transactions as short as possible. One way to help accomplish this is to reduce the number of round trips between your application and SQL Server by using stored procedures or keeping transactions with a single batch. Another way of reducing the time a transaction takes to complete is to make sure you are not performing the same reads over and over again. If your application does need to read the same data more than once, cache it by storing it in a variable or an array, and then re-reading it from there, not from SQL Server.

    • Reduce lock time. Try to develop your application so that it grabs locks at the latest possible time, and then releases them at the very earliest time.

    • If appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.

    • Consider using the NOLOCK hint to prevent locking if the data being locked is not modified often.

    • If appropriate, use as low of an isolation level as possible for the user connection running the transaction.

    • Consider using bound connections.

    Few links wich are studied ::

    Deadlocking click

    How to resolve a deadlock click

    Replied on Jan 9 2012 2:54AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    7

    Short Story Of Deadlock:

    Deadlocking occurs when two or more SQL Server processes have locks on separate database objects and each process is trying to acquire a lock on an object that the other processes have previously locked. For example, process one has an exclusive lock on object one, process two has an exclusive lock on object two, and process one also wants an exclusive lock on object two, and object two wants an exclusive lock on object one. Because two processes can’t have an exclusive lock on the same object at the same time, the two processes become entangled in a deadlock, with neither process willing to yield of its own accord.

    Since a deadlock is not a good thing for an application, SQL Server is smart enough to identify the problem and ends the deadlock by choosing one process over another. It does this by killing one of the processes (usually the process that has used the least amount of server resources up to this point) and lets the other one to continue to run. The aborted transaction is rolled back and an error message is sent to the application. If the application is deadlock aware, it will resubmit the killed transaction automatically and the user may never know the deadlock happened. If the application is not deadlock aware, then most likely an error message appears on the application’s screen and you get a call from a disgruntled user. Besides irritating users, deadlocks can use up SQL Server's resources unnecessarily as transactions are killed, rolled back, and resubmitted again.

    Identify deadlock problems:

    • Use the SQL Server Profiler: Create Trace Wizard to run the “Identify The Cause of a Deadlock” trace. This will provide you with the raw data you need to help isolate the causes of deadlocks in your databases.

    • To help identify which tables or stored procedures are causing deadlock problems: Turn on trace flag 1204 (outputs basic trace data) or trace flag 1205 (outputs more detailed trace data).

    DBCC TRACEON (3605,1204,-1)

    Be sure to turn off this trace flag when you are done, as this trace can eat up SQL Server’s resources unnecessarily, hurting performance

    • Spwho2: First find out which process block the other process from activity monitor or spwho2. Then kill that process.

    • Sp_lock:If update locks are more then it will make deadlock. So reduce that.

    Ideally, deadlocks should be eliminated from your applications. But if you are unable to eliminate all deadlocks in your application, be sure to include program logic in your application to deal with killed deadlock transactions in a user-friendly way.

    For example, let’s say that two transactions are deadlocked and that SQL Server kills one of the transactions. In this case, SQL Server will raise an error message that your application needs to respond to. In most cases, you will want your application to wait a random amount of time after the deadlock in order to resubmit the killed transaction to SQL Server.

    It is important that there is a random waiting period because it is possible that another contending transaction could also be waiting, and you don’t want both contending transactions to wait the same amount of time and then both try to execute at the same time, causing another deadlock.

    Reducing Deadlocking Problems

    Many different things can contribute to the cause of a deadlock in SQL Server. Below are some suggestions on how to eliminate, or at least mitigate, deadlocking problems in your applications. This list is only a starting point and should not be considered a complete list of the options you have to prevent or reduce deadlocking. You may want to share this list with your developers, along with the information you identified during your trace.

    • Ensure the database design is properly normalized.

    • Have the application access database objects in the same order every time.

    • Keep transactions as short as possible.

    • During transactions, don't allow any user input.

    • Avoid cursors.

    • Consider reducing lock escalation by using the ROWLOCK or PAGLOCK hint.

    • Consider using the NOLOCK hint to prevent locking.

    • Use as low a level of isolation as possible for user connections.

    • TRY/CATCH Helps to Resolve Deadlocks.

    Replied on Jan 9 2012 5:35AM  . 
    dips
    930 · 0% · 30
  • Score
    1

    In above post please replace Spwho2 with Sp_who2

    Replied on Jan 9 2012 5:36AM  . 
    dips
    930 · 0% · 30
  • Score
    7

    DEADLOCK

    When two processes are waiting for each other to release locks the other needs. Each process would wait indefinitely for the other to release the lock. SQL Server detects deadlocks and choose one process as a deadlock victim, rolls back the transaction and return error 1205.

    Creating a Deadlock Below are the steps to generate a deadlock so that the behaviour of a deadlock can be illustrated:

        -- 1) Create Objects for Deadlock Example
        USE TEMPDB
        CREATE TABLE dbo.foo (col1 INT)
        INSERT dbo.foo SELECT 1
        CREATE TABLE dbo.bar (col1 INT)
        INSERT dbo.bar SELECT 1
        -- 2) Run in first connection
        BEGIN TRAN
        UPDATE tempdb.dbo.foo SET col1 = 1
        -- 3) Run in second connection
        BEGIN TRAN
        UPDATE tempdb.dbo.bar SET col1 = 1
        UPDATE tempdb.dbo.foo SET col1 = 1
        -- 4) Run in first connection
        UPDATE tempdb.dbo.bar SET col1 = 1
    

    Connection two will be chosen as the deadlock victim
    ie.
    Server: Msg 1205, Level 13, State 50, Line 1
    Transaction (Process ID 56) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    Identify Deadlock:

    Using SQL Profiler
    To do this using SQL Profiler, you will need to capture the Lock Events Lock:Deadlock and Lock:Deadlock Chain.

    Using Server side trace If it's SQL Server 2005 I will turn on trace flag 1222 or will start server side trace to capture Deadlock graph. For SQL Server 2000 trace flag is 1204. (DBCC TRACEON (1204) )

    Using SQL Error Log with a trace (xp_readerrorlog)

    Using Extended Event Systemhealth extended event
    The system
    health session uses a ringbuffer target which stores the information collected by events firing in memory as an XML document in the sys.dmxesessiontargets DMV.

    Event Notification Event Notification lets you capture events that can occur on server, database or broker queue, for a list of events, query sys.eventnotificationeventtypes. For this purpose, the event type 1148, DEADLOCKGRAPH is of interest. Also note that the DEADLOCK_GRAPH is a trace event, which can only be captured at server level.

    http://msdn.microsoft.com/en-us/library/ff877955(v=sql.110).aspx

    http://www.sqlservercentral.com/articles/deadlock/65658/

    How to resolve a deadlock

    1. Adding missing indexes and drop unnecessary indexes.
    2. Adding index hints.
    3. Access objects in the same order.
    4. Removing activity from the transaction like triggers. By default, triggers are transactional.
    5. Keep transactions short and in one batch.
    6. Avoid user interaction in transactions.
    7. Use a lower isolation level.
    8. Use a row versioning-based isolation level.
      . Set READCOMMITTEDSNAPSHOT database option ON to enable read-committed transactions to use row versioning.
      . Use snapshot isolation.
    9. Use bound connections.
    10. Normalize database
    11. Use query hints to prevent locking if possible (NoLock, RowLock)
    12. Reduce lock time in application.
    13. Select deadlock victim by using SET DEADLOCK_PRIORITY.
    14. Avoid cursors, while loops, or process which requires user input while it is running.

    SQL SERVER 2008 R2 has new priority HIGH as well as numeric-priority.

    SQL SERVER 2008 R2 Syntax
    SET DEADLOCK_PRIORITY { LOW | NORMAL | HIGH | <numeric-priority> | @deadlock_var | @deadlock_intvar }
    <numeric-priority> ::= { -10 | -9 | -8 | … | 0 | … | 8 | 9 | 10 }
    Example:
    The following example sets the deadlock priority to NORMAL.
    SET DEADLOCK_PRIORITY NORMAL;
    GO
    

    http://msdn.microsoft.com/en-us/library/ms177433.aspx

    http://blog.sqlauthority.com/2007/05/16/sql-server-fix-error-1205-transaction-process-id-was-deadlocked-on-resources-with-another-process-and-has-been-chosen-as-the-deadlock-victim-rerun-the-transaction/

    Replied on Jan 9 2012 5:59AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    3

    A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.

    Deadlock can be tracked using the help of trace flags 1204 and 1222.

    In the event of a deadlock SQL Engine will mark one sessions as deadlock victim and the transaction is terminated and an error is sent back.

    Using a lower isolation level,keeping transactions short and in batch are some of the best ways to reduce deadlocks.

    Replied on Jan 9 2012 5:35PM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    5

    What is Deadlock?

    A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock. For example:

    • Transaction A acquires a share lock on row 1.
    • Transaction B acquires a share lock on row 2.
    • Transaction A now requests an exclusive lock on row 2, and is blocked until transaction B finishes and releases the share lock it has on row 2.
    • Transaction B now requests an exclusive lock on row 1, and is blocked until transaction A finishes and releases the share lock it has on row 1.

    Transaction A cannot complete until transaction B completes, but transaction B is blocked by transaction A. This condition is also called a cyclic dependency: Transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A.

    Both transactions in a deadlock will wait forever unless the deadlock is broken by an external process. The Microsoft SQL Server Database Engine deadlock monitor periodically checks for tasks that are in a deadlock. If the monitor detects a cyclic dependency, it chooses one of the tasks as a victim and terminates its transaction with an error. This allows the other task to complete its transaction. The application with the transaction that terminated with an error can retry the transaction, which usually completes after the other deadlocked transaction has finished.

    Identify Deadlock

    We can Identify Deadlock using following

    • SQL Server Profiler (Deadlock graph event)
    • Using SQL Server logs Using
    • Trace Flag 1204 and Trace Flag 1222

    Reduce the deadlock situations

    • Adding and dropping indexes.
    • Adding index hints like NOLOCK
    • Modifying the application to access resources in a similar pattern.
    • Removing activity from the transaction like triggers. By default, triggers are transactional.
    • Use of try catch block
    • Keeping transactions as short as possible.
    Replied on Jan 11 2012 4:25AM  . 
    Hardik Doshi
    20 · 9% · 2864
  • Score
    6

    Deadlock can be one of the reasons. It occurs when there is an open transaction in one session and you try to retrieve the data from the same table(s) in another session. Easy way to create it is Execute the following query in one Query window;

    Create Table Table1 As (ID int, Name varchar(100))
    
    Insert into Table1 Select 1,'A' union all Select 2,'B'
    

    Open 2nd query window and execute the following statements;

    Begin Tran Update Table1 Set name = 'D'
    

    As you can see, no COMMIT or ROLLBACK is specified. So the transaction will remain opened until indefinite period of time until you execute commit or rollback.

    Now, open 3rd query window and execute the same query;

    Update Table1 Set name = 'E'
    

    The query will not complete because the table has been occupied by the session of 2nd Query window. This is dead lock.

    Now go to 2nd query window and execute commit or rollback. On doing this, the 3rd query window query will be complete.

    We have to be very careful while using transactions as an open transaction due to any error can cause alot of trouble.

    The following message is given when the server resolves a deadlock situation:

    Server: Msg 1205, Level 13, State 50, Line 1 Transaction (Process ID 55) was deadlocked on {lock} resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    To see what was running in Process ID 55 (just an example), run immediately after the deadlock before the spid is recycled:

    DBCC INPUTBUFFER(55)
    

    Deadlocks can be traced by turning on two specific flags:

    dbcc traceon (1204, 3605, -1)
    
    go
    
    dbcc tracestatus(-1)
    
    go
    

    Deadlocks trace output can be examined in SQL Server log.

    In SQL Server 2005 and SQL Server 2008, the Deadlock graph trace feature of SQL Server Profiler provides visualization of deadlocks.

    DEADLOCK PREVENTION

    Deadlocks can be prevented by one or more of the following methods:

    • Adding missing indexes to support faster queries
    • Dropping unnecessary indexes which may slow down INSERTs for example
    • Redesigning indexes to be "thinner", for example, removing columns from composite indexes or making table columns "thinner" (see below)
    • Adding index hints to queries
    • Redesigning tables with "thinner" columns like smalldatetime vs. datetime or smallint vs. int
    • Modifying the stored procedures to access tables in a similar pattern
    • Keeping transactions as short and quick as possible: "mean & lean"
    • Removing unnecessary extra activity from the transactions like triggers
    • Removing JOINs to Linked Server (remote) tables
    • Implementing regular index maintenance; usually weekend schedule suffices; use FILLFACTOR = 80 for dynamic tables
    Replied on Jan 11 2012 11:31PM  . 
    ATif-ullah Sheikh
    133 · 1% · 391
  • Score
    7

    DEADLOCK

    A deadlock occurs when two system server process IDs (SPIDs) are waiting for a resource and neither process can advance because the other process is preventing it from getting the resource.

    The lock manager’s thread checks for deadlocks. When a lock manager’s deadlock detection algorithm detects a deadlock, the lock manager chooses one of the SPIDs as a victim. The lock manager initiates a 1205 error message that is sent to the client, and the lock manager kills the SPID. Killing the SPID frees the resources and allows the other SPID to continue.

    For example you have two sessions that are updating the same data, session 1 starts a transaction updates table A and then session 2 starts a transaction and updates table B and then updates the same records in table A. Session 1 then tries to update the same records in table B. At this point it is impossible for the transactions to be committed, because the data was updated in a different order and SQL Server selects one of the processes as a deadlock viticm.

    To further illustrate how deadlocks work you can run the following code in the Northwind database. To create a deadlock you can issue commands similar to the commands below.

    Step 1:

    --open a query window (1) and run these commands

    begin tran 
    update products set supplierid = 2
    

    Step 2:

    -- open another query window (2) and run these commands

    begin tran 
    update employees set firstname = 'Bob' 
    update products set supplierid = 1
    

    Step 3:

    -- go back to query window (1) and run these commands

    update employees set firstname = 'Greg'
    

    At this point SQL Server will select one of the process as a deadlock victim and roll back the statement .

    Step 4:

    --issue this command in query window (1) to undo all of the changes

    rollback
    

    Step 5: --go back to query window (2) and run these commands to undo changes

    rollback
    

    Identify and Resolve deadlock

    There's no need to kill processes manually in a deadlock. When SQL picks up that a deadlock has occured, one of the processes is selected as the deadlock victim and that process is terminated by SQL server and its work rolled back.

    The only solution for handling deadlocks is to find the problem in your code and then modify your processing to avoid deadlock situations. The first thing you need to do is find the deadlock situations and then investigate the problem.

    There are a couple of ways of doing this.The first approach is to turn on the trace flag to find the deadlocks. This can be done with any of the following statement run in Query Analyzer.

    DBCC TRACEON (1204)
    

    OR

    DBCC TRACEON (1222)
    

    When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server error log. Trace flag 1204 reports deadlock information formatted by each node involved in the deadlock. Trace flag 1222 formats deadlock information, first by processes and then by resources. It is possible to enable both trace flags to obtain two representations of the same deadlock event.

    From this error log we could see what tables were part of the deadlock process, To provide further information about the deadlock process we need to run a Trace to capture all of the information and then try to decipher what is going on. This can be done by either using Profiler or by using a Server Side Trace.

    SQL Profiler

    using SQL Profiler, you will need to capture the Lock Events Lock:Deadlock and Lock:Deadlock Chain. And also capture the ObjectId data column.

    Server Side Trace

    For a Server Side Trace the following additional information will need to collected to capture the deadlock information.

    1. EventNumber :25

      Event : Lock:Deadlock

      Description:- Indicates that two concurrent transactions have deadlocked each other by trying to obtain incompatible locks on resources the other transaction owns.

    2. EventNumber :- 59

      Event :- Lock:Deadlock Chain

      Description:- Produced for each of the events leading up to the deadlock.

    In addition, you will also need to capture this additional column to see what objects are part of the deadlock chain.

    ColumnNumber :- 22

    Column:- ObjectID

    Description:-System-assigned ID of the object.

    From The output of our trace we can see what was occurring at the time of the deadlock.

    When we have a lot of information to go through it is easier to load the data into a SQL Server table and then query the data for the particular timeframe and SPIDs in question. This was covered in a Server Side Trace tip. Here is a sample query that can help you narrow down the timeframe when the deadlock occurred. By changing the date values and the SPIDs to look at we can narrow down what was occurring at the time or right around the time that the deadlock occurred.

    DECLARE @lowDate AS datetime, @highDate AS datetime 
    
    SET @lowDate = '2006-08-01 13:47:17.000' 
    SET @highDate = '2006-08-01 13:47:18.999' 
    
    
    SELECT  
           TextData, 
           StartTime,  
           EndTime, 
           SPID,  
           Duration,  
           Reads,  
           Writes,  
           EventClass       
    FROM  
           TraceFile 
    WHERE  
           SPID IN (52,53,4) AND 
           (StartTime BETWEEN @lowDate AND @highDate  
            OR EndTime BETWEEN @lowDate AND @highDate  
            OR StartTime < @lowDate AND EndTime > @lowDate) 
    ORDER BY  
           StartTime
    

    Reference:

    http://www.sqlservercentral.com/articles/deadlock/65614/

    http://msdn.microsoft.com/en-us/library/ms178104.aspx

    http://www.mssqltips.com/sqlservertip/1036/finding-and-troubleshooting-sql-server-deadlocks/

    Replied on Jan 18 2012 12:30AM  . 
    Sineetha
    106 · 2% · 492
  • Score
    8

    Introduction

    A deadlock is a situation wherein two transactions wait for each other to give up their respective locks.

    When this happens, the SQL Server ends the deadlock by automatically choosing one and aborting the process, allowing the other process to continue. The aborted transaction is rolled back and an error message is sent to the user of the aborted process. Generally, the transaction that requires the least amount of overhead to rollback is the transaction that is aborted.

    This article will explain how to handle deadlocks in a user-friendly way.

    The Deadlock

    Transaction A attempts to update table 1 and subsequently read/update data from table 2, whereas transaction B attempts to update table 2 and subsequently read/update data from table 1. In such situations, transaction A holds locks that transaction B needs to complete its task and vice versa; neither transaction can complete until the other transaction releases locks.

    The Deadlock Situation

    The below example shows the deadlock situation between the two transactions.

    Transaction A

     BEGIN TRANSACTION
    
     UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
     WAITFOR DELAY '00:00:05' -- Wait for 5 ms
     UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221
    
     COMMIT TRANSACTION
    

    Transaction B

    BEGIN TRANSACTION
    
     UPDATE Orders SET ShippingId = 12 WHERE OrderId = 221
     WAITFOR DELAY '00:00:05' -- Wait for 5 ms
     UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
    
     COMMIT TRANSACTION
    

    If both the transactions are executed at the same time, then Transaction A locks and updates Customer table whereas transaction B locks and updates Orders table. After a delay of 5 ms, transaction A looks for the lock on Orders table which is already held by transaction B and transaction B looks for lock on Customer table which is held by transaction A. So both the transactions cannot proceed further, the deadlock occurs and the SQL server returns the error message 1205 for the aborted transaction.

    (1 row(s) affected)
    Msg 1205, Level 13, State 45, Line 5
    Transaction (Process ID 52) was deadlocked on lock resources with 
    another process and has been chosen as the deadlock victim.
    Rerun the transaction.
    

    But what if you don't like the default behavior (aborting the transaction)? Can you change it? Yes, you can, by rewriting Transactions A and B as shown below.

    Transaction A

    RETRY: -- Label RETRY
    BEGIN TRANSACTION
    BEGIN TRY
    
        UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
        WAITFOR DELAY '00:00:05'  -- Wait for 5 ms
        UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221
    
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        PRINT 'Rollback Transaction'
        ROLLBACK TRANSACTION
        IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
        BEGIN
        	WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
        	GOTO RETRY -- Go to Label RETRY
        END
    END CATCH
    

    Transaction B

    RETRY: -- Label RETRY
    BEGIN TRANSACTION
    BEGIN TRY
        UPDATE Orders SET ShippingId = 12 Where OrderId = 221
        WAITFOR DELAY '00:00:05' -- Wait for 5 ms
        UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
    
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        PRINT 'Rollback Transaction'
        ROLLBACK TRANSACTION
        IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
        BEGIN
        	WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
        	GOTO RETRY -- Go to Label RETRY
        END
    END CATCH
    

    Here I have used Label RETRY at the beginning of both the transactions. The TRY/CATCH method is used to handle the exceptions in the transactions. If the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back, and if the exception is occurred due to deadlock (Error_Number 1205), the transaction waits for 5 milliseconds. The delay is used here because the other transaction (which is not aborted) can complete its operation within delay duration and release the lock on the table which was required by the aborted transaction. You can increase the delay according to the size of your transactions. After the delay, the transaction starts executing from the beginning (RETRY: Label RETRY at the beginning of the transaction) using the below statement:

    GOTO RETRY -- Go to Label RETRY
    

    This statement is used to transfer the control to the label named RETRY (which is at the beginning).

    Now Execute the Transaction A and Transaction B at the same time. Both the transactions will execute successfully. Have a look into the outputs of the transaction where the exception occurred.

    (1 row(s) affected)
    Rollback Transaction
    
    (1 row(s) affected)
    
    (1 row(s) affected) 
    Using RetryCounter
    

    Now, I guess you understood how to handle deadlock without aborting the transaction. Let's move to the next interesting topic about deadlock. Imagine if there are more than two processes that read/update the Customer or Orders table at the same time. Below, I have modified both the transactions where I have shown how we can use RetryCounter to solve the problem.

    Transaction A

    DECLARE @RetryCounter INT
    SET @RetryCounter = 1
    RETRY: -- Label RETRY
    BEGIN TRANSACTION
    BEGIN TRY
    
        UPDATE Customer SET LastName = 'John' WHERE CustomerId=111
        WAITFOR DELAY '00:00:05'  -- Wait for 5 ms
        UPDATE Orders SET CustomerId = 1 WHERE OrderId = 221
    
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        PRINT 'Rollback Transaction'
        ROLLBACK TRANSACTION
        DECLARE @DoRetry bit; -- Whether to Retry transaction or not
        DECLARE @ErrorMessage varchar(500)
        SET @doRetry = 0;
        SET @ErrorMessage = ERROR_MESSAGE()
        IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
        BEGIN
        	SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
        END
        IF @DoRetry = 1
        BEGIN
        	SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
        	IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
        	BEGIN
        		RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message if 
        			-- still deadlock occurred after three retries
        	END
        	ELSE
        	BEGIN
        		WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
        		GOTO RETRY	-- Go to Label RETRY
        	END
        END
        ELSE
        BEGIN
        	RAISERROR(@ErrorMessage, 18, 1)
        END
    END CATCH
    

    Transaction B

    DECLARE @RetryCounter INT
    SET @RetryCounter = 1
    RETRY: -- Label RETRY
    BEGIN TRANSACTION
    BEGIN TRY
        UPDATE Orders SET ShippingId = 12 Where OrderId = 221
        WAITFOR DELAY '00:00:05' -- Wait for 5 ms
        UPDATE Customer SET FirstName = 'Mike' WHERE CustomerId=111
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        PRINT 'Rollback Transaction'
        ROLLBACK TRANSACTION
        DECLARE @DoRetry bit; -- Whether to Retry transaction or not
        DECLARE @ErrorMessage varchar(500)
        SET @doRetry = 0;
        SET @ErrorMessage = ERROR_MESSAGE()
        IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
        BEGIN
        	SET @doRetry = 1; -- Set @doRetry to 1 only for Deadlock
        END
        IF @DoRetry = 1
        BEGIN
        	SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
        	IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
        	BEGIN
        		RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message 
        			-- if still deadlock occurred after three retries
        	END
        	ELSE
        	BEGIN
        		WAITFOR DELAY '00:00:00.05' -- Wait for 5 ms
        		GOTO RETRY	-- Go to Label RETRY
        	END
        END
        ELSE
        BEGIN
        	RAISERROR(@ErrorMessage, 18, 1)
        END
    END CATCH
    

    The RetryCounter variable used here gives a chance for the transaction to execute again if it fails due to deadlock (Error_Number 1205). In this example, the transaction can try to execute up to three times if it fails due to a deadlock. This scenario would be very useful if the transaction looking for the lock which was not released by the other transactions for a long time. So the transaction can try three times to check whether the required lock is available.

    To see what was running in Processes , run immediately after the deadlock before the spid is recycled:

    DBCC INPUTBUFFER(55)
    

    Deadlocks can be traced by turning on two specific flags:

    dbcc traceon (1204, 3605, -1)
    go
    dbcc tracestatus(-1)
    go
    

    Deadlocks trace output can be examined in SQL Server log.

    In SQL Server 2005 and SQL Server 2008, the Deadlock graph trace feature of SQL Server Profiler provides visualization of deadlocks.

    SQL Profiler is also can use to detect deadlocks.

    http://www.extremeexperts.com/sql/Yukon/DeadLockDetection.aspx

    Replied on Jan 24 2012 5:21AM  . 
    indika saminda kannangara
    188 · 1% · 251

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.