05 - Blocking and Deadlocking

Hi,

Here we’ll cover below:

- Blocking

- Deadlocking

- Identify Blocking

- DEADLOCK_PRIORITY

- Deadlock & SQL Profiler

Blocking-

- Blocking occurs when a transaction is locking resources that one or more other transactions want to read or modify.

- Possible Reasons

o Transactions that BEGIN and then request user feedback or interaction

o Transactions that BEGIN and then look up data that could have been referenced prior to the transaction starting.

o Using locking hints inappropriately; for example, if the application only needs one row, but uses a table lock instead

o The application uses long-running transactions, updating many rows or many tables within one transaction.

Deadlocking -

- Deadlocking occurs when one user session (let's call it Session 1) has locks on a resource that another user session (let's call it Session 2) wants to modify, and Session 2 has locks on resources that Session 1 needs to modify. Neither Session 1 nor Session 2 can continue until the other releases the locks, so SQL Server chooses one of the sessions in the deadlock as the deadlock victim.

- This deadlock victim has its session killed and transactions rolled back.

- Possible Reasons

o The application uses long-running transactions, updating many rows or many tables within one transaction.

o In some situations, SQL Server issues several row locks, which it later decides must be escalated to page or table locks. If these rows exist on the same data pages, and two sessions are both trying to escalate the lock granularity on the same page, a deadlock can occur.

Identify Blocking-

- In Query Analyzer, run sp_who. This will produce below.

- Look at the Blk column. When a number exists in this column, this identifies the SPID that is blocking that current row's process. You could have a chain of blocking (numerous blocked or blocking processes), so you may need to run through this process many times

- Run sp_lock, including the SPID that is blocking other processes.

- From the results, you should track the type of locks, lock mode, and objects impacted. The options for status are GRANT (lock was obtained), WAIT (blocking is occurring), or CNVT (lock is converting to another lock).

- To see the object name from the ObjId integer, execute below.

SELECT OBJECT_NAME (integer)

This returns below.

- Now that you know which statement is blocking the resources, and which resources are being blocked, it is time to take a look at the statement issuing the block, by running below.

DBCC INPUTBUFFER (SPID):

- If possible, ask your client or application contact to stop the transaction blocking the resources. In either case, if you cannot stop the process in an elegant way, you can run the KILL process to stop the blocking:

KILL 53

SET DEADLOCK_PRIORITY

- You can increase the likelihood of a session being chosen as a deadlock victim by using the SET DEADLOCK_PRIORITY statement.

- LOW identifies the session to be the preferred deadlock victim.

- NORMAL returns the session to the default deadlock-handling method.

- @deadlock_var has the character value 3 for LOW and 6 for NORMAL

SET DEADLOCK_PRIORITY { LOW | NORMAL | @deadlock_var }

e.g

SET DEADLOCK_PRIORITY LOW

GO

Update MyTable Set …..

Capture Deadlock Activity using SQL Profiler

Thanks & Regards,

Arun Manglick || Senior Tech Lead