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.

How would I face deadlock:

First I will try to find cause of deadlock. I need to know which processes led to the deadlock scenario and what resource and types of locks are involved. In SQL Server 2008 and higher, I will look output of system_health Extended Event session. It's started by default in SQL Server 2008, so if administrator didn't turn it off, we have information about processes, resources and types of locks involved in deadlock scenario. It's excellent way for retroactively finding deadlock information. And in this situation we can immediately start to work on resolving the deadlock problems.

If system_health Extended Event session is stopped I will start it to capture all detected deadlocks in the system. But I need to wait to deadlock happened again to know exactly what is going on. To capture deadlock information in older version I will:

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.

When I find information about processes, statements, resourdes and types of locks that are involved in deadlock I will run queries through DTA (Database tuning advisor). DTA will tell me if some indexes are missing. Usually deadlock can be resolved by adding missing index (adding covered nonclustered index resolved almost all my deadlocks).

If one of the involving statements is SELECT operation, I can add NOLOCK hint or set isolation level to READ UNCOMMITTED (if it’s OK that SELECT operation read uncommited data). If that’s not option, in SQL Server 2005 or higher I will set READ COMMITTED SNAPSHOT or SNAPSHOT isolation level (it will remove shared lock caused by reads). It’s always good that the query is using the minimum necessary transaction isolation level.

Also it’s good to check that transactions involved in deadlock are as short as possible and if they change the same tables, change them in the same order.

BLOCKING

Blocking occurs when one process holds a lock on a specific resource and a second process attempts to acquire a conflicting lock type on the same resource. This is normal behavior and may happen many times with no noticeable effect on system performance.

If blocking increase to the point where it impact on system performance I will first find what cause blocking (need information about queries that are involved in blocking and types of locks). I can use SQL Trace Blocked Process Report or can use DMVs (sys.dmoswaitingtasks, sys.dmtranlocks, sys.dmexecrequest, sysdmexecsql_text) to get that informations.

Solutions of blocking problems are the same as for deadlocking problems:

Identify and add missing indexes, ensure that all transactions are as short as possible, use hints to modify locking behavior, use the lowest isolation level acceptable, rewrite problematic code, ensure that indexes aren’t fragmented and that statistics are up to date.

• 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.

BLOCKING Blocking occurs when one process holds a lock on a specific resource and a second process attempts to acquire a conflicting lock type on the same resource. This is normal behavior and may happen many times with no noticeable effect on system performance.

Solution: Checking for all above types of blocking run Idera SQL Check utility and found out problem or process where blocking is occurred and kill it. Then checks missing indexes and create if suggested. Checks code performance issues and resolve it.

Thanks,

Harish Kumar

Blocking

Blocking occurs when one connection (user process or application process) places a lock on a table or a number of rows and a second connection attempts to read or modify the data under the lock by first connection. Depending on the type of the lock, this can cause the second connection to wait until the first connection releases its lock. A blocked connection waits indefinitely for the blocking connection to release its lock.

The more blocking happens on the server the less concurrency the system achieves. A certain amount of blocking is unavoidable but too many blocks for longer periods of time can degrade the performance of SQL Server.

Deadlocks

The combination of two blocked connections where the first is blocking the second and the second is blocking the first is called a deadlock. Since deadlocks are not naturally resolved with time, SQL Server automatically kills one of the connection (Deadlock victim) once it detects a deadlock. This allows the other connection to continue with its transaction.

Although deadlocks can be caused by two short blocks (Fraction of a second), it is often the long blocks that increase the chances of a deadlock to happen.

Live Lock

A Live lock is one, where a request for exclusive lock is denied continuously because a series of overlapping shared locks keeps on interfering each other and to adapt from each other they keep on changing the status which further prevents them to complete the task. In SQL Server Live Lock occurs when read transactions are applied on table which prevents write transaction to wait indefinitely. This is different then deadlock as in deadlock both the processes wait on each other.

Dead Lock

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. Transaction A has a dependency on transaction B, and transaction B closes the circle by having a dependency on transaction A is called deadlock.

Facing Dead Lock (and Blocking)

I found some design strategies that help me to reduce dead lock and blocking

• Use clustered indexes on high-usage tables.
• Avoid count on big table that cause a table lock.
• Break long transaction into small transaction parts.
• Use existing index in UPDATE and DELETE statement.
• In nested transaction, be sure that commit and rollback can;t conflicts.

Now, suppose that dead lock occurs frequently.

There are two other method to capture dead lock information and this tools help to resolve dead lock.

• Trace Flag 1204 and Trace Flag 1222
• Deadlock graph event in SQL Server Profiler

Live Lock

A livelock is similar to a deadlock, except that the states of the processes involved in the livelock constantly change with regard to one another, none progressing. Livelock is a special case of resource starvation, it states that a specific process is not progressing. In SQL Server Live Lock occurs when read transactions are applied on table which prevents write transaction to wait indefinitely.

Note: As queuing implemented in the database engine there is not Live Lock exists.

Blocking

A blocking lock occurs when one lock causes another process to wait in a holding queue until the current process is entirely done with the resources. As soon as the first process is complete, the blocked process resumes operation. In a normal server environment, infrequent blocking locks are acceptable. But if blocking locks are common, there is probably some kind of design or query implementation problem.

Face Blocking

Its same as Dead lock.

Deadlock:

A deadlock is a situation where in two or more competing actions are each waiting for the other to finish, and thus neither ever does Since deadlocks are not naturally resolved with time, SQL Server automatically kills one of the connection (Deadlock victim) once it detects a deadlock. This allows the other connection to continue with its transaction.Although deadlocks can be caused by two short blocks (Fraction of a second), it is often the long blocks that increase the chances of a deadlock to happen

. *Solution :* They can generally be resolved by changing application or stored procedure code to access tables in the same logic order, or by changing the actual database schema or indexing structures. The first step in Deadlock elimination is to identify what two statements are actually causing the deadlocks to occur. In some cases, there may be more than just two statements that contribute to a deadlock.

Livelock

A livelock is similar to a deadlock, except that the states of the processes involved in the livelock constantly change with regard to one another, none progressing. An analogy is when two people meet in a hallway and each tries to step around the other but they end up swaying from side to side getting in each other's way as they try to get out of the way.

Solution:

SQL Server avoids livelocks using queuing.

Blocking

Blocking occurs when one session holds a lock on a specific resource and a second session attempts to get resources under the lock of first session. This may cause a lock until first session release the lock.
Blocking occurs for the following Scenarios

1. Running Query with a Long Execution Time This type of blocking problem may be a performance problem.

   solution: Query Optimization

2. Due to Sessions which in Sleep or awaiting a command This can occur if the application experiences a query timeout, or issues a cancel without issuing the required number of ROLLBACK and/or COMMIT statements

   solution: Applications must properly manage transaction nesting levels

3. Blocking Caused by a session Whose Corresponding Client Application Did Not Fetch All Result Rows to Completion

   After sending a query to the server, all applications must immediately fetch all result rows to completion. If an application does not fetch all result rows, locks can be left on the tables, blocking other users.

   solution: The application must be re-written to fetch all rows of the result to completion.

4. Blocking Caused by a Distributed Client/Server Deadlock

   solution :Two reliable solutions are to use either a query timeout or bound connections.

5. Blocking Caused by a Session That Is in a "Golden," or Rollback state (which means it cannot be KILLed or selected as a deadlock victim). A data modification query that is KILLed, or canceled outside of a user-defined transaction, will be rolled back. A data modification query often cannot be rolled back any faster than the changes were initially applied.

   Solution:Do not perform large batch INSERT, UPDATE, or DELETE operations during busy hours on OLTP systems. If possible, perform such operations during periods of low activity.

6. Blocking Caused by an Orphaned Connection If the client application traps or the client workstation is restarted, the network session to the server may not be immediately canceled under some conditions. From the server's perspective, the client still appears to be present, and any locks acquired may still be retained.

   Solution:If the client application has disconnected without appropriately cleaning up its resources, you can terminate the session by using the KILL command. The KILL command takes the session value as input. For example, to kill SPID 9, simply issue the following command: KILL 9

strong text

Deadlock: Deadlock 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.

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.

How to Deal with:

Simply whenever somebody is writing T-SQL code use objects in order. whenever you are feeling DEADLOCK victim's check with other transactions and provide DEADLOCK_PRIORITY for that. in SQL 2K it was trace flags - 1204 & 1222, in 2k5 & onwards Microsoft have introduced new events were added in SQL Server Profiler. I am exaplaining some of the important events (we can use): · Deadlock graph · Lock: Deadlock · Lock: Deadlock Chain · RPC:Completed · SP:StmtCompleted · SQL:BatchCompleted · SQL:BatchStarting

Deadlock Graph

Of seven events I have listed above, the only event you must have is the Deadlock Graph event. It captures, in both XML format and graphically, a drawing that shows you exactly the cause of the deadlock. We will examine how to interpret this drawing later in this article.

Lock:Deadlock

This event is fired whenever a deadlock occurs, and because of this, it is also fired every time the Deadlock Graph event is fired, producing redundant data. I have included it here because it makes it a little easier to see what is happening, but if you like, you can drop this event from your trace.

Lock:Deadlock Chain

This event is fired once for every process involved in a deadlock. In most cases, a deadlock only affects two processes at a time, and because of this, you will see this event fired twice just before the Deadlock Graph and the Lock:Deadlock events fire. In rare cases, more than two processes are involved in a deadlock, and if this is the case, an event will be fired for every process involved in the deadlock.

RPC: Completed The RPC: Completed event fires after a stored procedure is executed as a remote procedure call. It includes useful information about the execution of the stored procedure, including the CPU time used to execute the stored procedure, the total length of time the stored procedure ran, logical reads and writes that occurred during its execution, along with the name of the stored procedure itself.

SP: StmtCompleted

Stored procedures are made up of one or more statements. In SQL Server 2005, each statement within a stored procedure is traced. The SP: StmtCompleted event indicates when a statement within a stored procedure has ended. The StmtCompleted event’s data columns provide lots of useful information about the statement, including the actual code in the statement, the duration the statement ran, the amount of CPU time used, the number of logical reads and writes, the number of rows returned by the statement, among others.

SQL: BatchStarting

The SQL: BatchStarting event is fired whenever a new batch begins. Once a batch begins, then one or more individual Transact-SQL statements occur. The SQL: BatchStarting event is a good event to easily see where a batch begins, but other than this, it is not particularly useful.

SQL: BatchCompleted

The SQL: BatchCompleted event occurs when a batch completes. This means that one or more Transact-SQL statements have completed for the batch. The SQL: BatchCompleted event is more useful than the SQL: BatchStarting event because it includes useful information like the duration of the entire batch, the logical number of reads and writes caused by all the statements inside the batch, the total number of rows returned by the batch, and other useful information.

Some of the keypoints in reducing DeadLocks

1) Ensure Database design is properly normalized 2) As i have mentioned above objects in the same order. 3) Keep transaction as short as possible. 4) During Transactions - dont allow user input/user interface 5) Avoid using cursors as much as you can....there are lot of alternatives available now. 6) Use NOLOCK hint to prevent locking 7) Use low level of isolation as possible for user connections. 8) reduce lock escalation by using the ROWLOCK or PAGLOCK hint.

Blocking: Blocking is normal consequence of locking.

Blocking occurs in SQL Server 2005 when one process has one or more records locked, while another process tries to acquire locks on the same set (or subset) of records. This causes the second process to wait until the first process is done so it can acquire the records; this can cause other processes to be blocked if they are waiting on the resources that the second connection has acquired. These processes are ultimately dependent upon the first process to complete its work before they can complete theirs. This can create a daisy-chain of processes waiting to complete their work.

There is thin line between Blocking and Deadlocking but it is require to understand.

This blocking situation is a bit different than a deadlock scenario. A deadlock occurs when process A has ownership of resources that process B needs. Process B, in turn, owns resources that process A needs to complete its work. In theory, this would last forever. Fortunately, SQL Server 2005 has algorithms to find these scenarios and stop one process so the other can finish its work. On the other hand, blocking can last a lot longer than a deadlock because it isn’t stopped automatically by SQL Server 2005. The blocked processes must wait until the blocking connection finishes its work before they can finish theirs. So i can say BLOCKING more dangerous than DEADLOCK.

How to deal with BLOCKING:

Fetching from sysprocesses where blocked >0. using 2k5 and onwards enhancements -

SELECT s.spid, BlockingSPID = s.blocked, DatabaseName = DBNAME(s.dbid), s.programname, s.loginame, ObjectName = OBJECTNAME(objectid,s.dbid), Definition = CAST(text AS VARCHAR(MAX)) INTO #Processes FROM sys.sysprocesses s CROSS APPLY sys.dmexecsqltext (sql_handle) WHERE s.spid > 50

To determine whether lock escalation is occurring around the time when you experience blocking issues, start a SQL Profiler trace that includes the Lock:Escalation event. Check Trace Flag -T1211.

LiveLock:

A Live lock is one, where a request for exclusive lock is denied continuously because a series of overlapping shared locks keeps on interfering each other and to adapt from each other they keep on changing the status which further prevents them to complete the task. In SQL Server Live Lock occurs when read transactions are applied on table which prevents write transaction to wait indefinitely. This is different then deadlock as in deadlock both the processes wait on each other.

In SQL Server Live Lock occurs when read transactions are applied on table which prevents write transaction to wait indefinitely. This is different then deadlock as in deadlock both the processes wait on each other.

Giving one of the example i am having –1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM authors

–2 UPDATE authors SET aulname = ‘X’ WHERE auid = ’238-95-7766′

–3 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM authors

–4 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN SELECT * FROM authors

–5 EXEC sp_who2

You will see that 2 is blocked by 1, 3 is blocked by 2 and 4 is blocked by 3. Theoretically, 3 and 4 could execute (there’s only a shared lock), but queuing makes 3 and 4 to wait. This is how livelocks are avoided: queuing.

1.) Dead Lock

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

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.

When deadlocks occur, trace flag 1204 and trace flag 1222 return information that is captured in the SQL Server 2005 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.

When deadlock append to much often, check for LOW performance on your I/O sub-system When deadlock append to much often, notice your vendor OR notice the appliation team

2.) Live Lock

http://blog.sqlauthority.com/2008/03/21/sql-server-introduction-to-live-lock-what-is-live-lock/

A Live lock is one, where a request for exclusive lock is denied continuously because a series of overlapping shared locks keeps on interfering each other and to adapt from each other they keep on changing the status which further prevents them to complete the task. In SQL Server Live Lock occurs when read transactions are applied on table which prevents write transaction to wait indefinitely. This is different then deadlock as in deadlock both the processes wait on each other.

I don't know yet how to detect / face this problem

3.) Blocking

http://support.microsoft.com/kb/224453

Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. On SQL Server, blocking occurs when one SPID holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for which the first SPID locks the resource is very small. When it releases the lock, the second connection is free to acquire its own lock on the resource and continue processing. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance.

When blocking append to much often, check for LOW performance on your I/O sub-system When blocking append to much often, notice your vendor OR notice the appliation team When blocking append to much often, add WITH(nolock) on query associated with "in house report" When blocking append to much often, place OLTP data in one database and OLAP data in another database

Dead Lock:

Dead Lock 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. 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.

First of all, finding a dead lock scenario is important. Once we find this out, the next step is how to resolve it. Finding dead locks, we can use the profiler, trace flags or third party tool such as SQL Doctor.

To help identify deadlock problems, use the SQL Server Profiler to get the dead lock graph. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis. To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock.

To help identify which tables or stored procedures are causing deadlock problems turn on trace flag 1204 or trace flag 1205.

DBCC TRACEON (3605,1204,-1)

Server side trace can also be used to capture dead lock graphs. Once the query or stored procedure is identified causing dead lock, we can tune the query by looking at the execution plan, and see if there are index scan or table scans. Most of the times dead locks will be resolved by adding index or changing the logic of the query. Database tuning advisor can be used so that it will give recommendations for adding indexes or updating the statistics.

If there are select statements involved in the dead lock scenario, NOLOC hint can be applied. Also need to make sure that the query is using minimum transaction isolation level. Make the transaction as short as possible. Always put the transaction in the transaction block with error handling logic.

SET DEADLOCK_PRIORITY can be used to control the way the session reacts when in a deadlock situation.

Blocking:

Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. On SQL Server, blocking occurs when one SPID holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource. Typically, the time frame for which the first SPID locks the resource is very small. When it releases the lock, the second connection is free to acquire its own lock on the resource and continue processing. This is normal behavior and may happen many times throughout the course of a day with no noticeable effect on system performance.

First we need to find out the cause of the blocking, it can be identified by the following ways:

• Identify the SPID (Session ID) at the head of the blocking chain and the SQL Statement by using activity monitor.
• Find the transaction nesting level and process status of the blocking SPID.

The transaction nesting level of a SPID is available in the @@TRANCOUNT global variable.

• Using profiler, gather blocking information. Look at the open transaction if there are any. Look at the Lastwaittype, waittype, and waittime. Also need to examine dbcc inputbuffer command output.

Use the following to detect blocking:

• Deadlock events and trace flag 1222
• Performance data collection and extended events
• SQL Profiler, Performance Monitor, Extended Events
• Use SP_WHO2

Use the following DMVs to find out the blocking details:

• sys.dmtranlocks
• sys.dmoswaiting_tasks
• sys.dmexecconnections
• sys.dmexecsql_text

How to minimize the blocking:

• Keep transactions short
• Keep transactions in one batch, no interactions
• Use indexes to lock only the necessary data
• Consider estimates instead of long running queries
• Consider separate analysis services for long running queries
• Look closely at long running or conflicting transactions

Once we capture the query which is involved in blocking scenario, we can look at the execution plan, add index if index are not utilized or if there are index scans or table scans. Database tuning advisor can be used for the further recommendations and it might ask to create index or update statistics.

Live Lock:

A live lock is one, where a request for an exclusive lock is repeatedly denied because a series of overlapping shared locks keeps interfering. SQL Server detects the situation after four denials and refuses further shared locks. A live lock also occurs when read transactions monopolize a table or page, forcing a write transaction to wait indefinitely. SQL server has logic to avoid live locks. It may be possible to force one to happen, but fundamentally they are very unlikely to occur.

1.) Dead Lock 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. 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.

2.) Live Lock A Live lock is one, where a request for exclusive lock is denied continuously because a series of overlapping shared locks keeps on interfering each other and to adapt from each other they keep on changing the status which further prevents them to complete the task. In SQL Server Live Lock occurs when read transactions are applied on table which prevents write transaction to wait indefinitely. This is different then deadlock as in deadlock both the processes wait on each other.

A human example of live lock would be two people who meet face-to-face in a corridor and each moves aside to let the other pass, but they end up moving from side to side without making any progress because they always move the same way at the same time and never cross each other. This is good example of live lock.

3.) Blocking When the LOCK_TIMEOUT setting is used to set a maximum amount of time that a connection can wait for a blocking lock to go away, the connection that has the lock and is causing the blocking problem is not affected, but the connection waiting for the block is halted, and receives an error message. When this happens, then error message 1222, “Lock request time-out period exceeded” is sent to the application.