Locks

The following table shows the resource lock modes that the Database Engine uses. Lock mode Description Shared (S) Used for read operations that does not change or update data, such as a SELECT statement. Update (U) Used on resources that can be updated. Prevents a common form of deadlock that occurs when multiple sessions are reading, locking, and potentially updating resources later. Exclusive (X) Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time. Intent Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). Schema Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M) and schema stability (Sch-S). Bulk Update (BU) Used when bulk copying data into a table and the TABLOCK hint is specified. Key-range Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

Lock Compatibility (Database Engine)

The following table shows the compatibility of the most commonly encountered lock modes.
	Existing granted mode
Requested mode	IS	S	U	IX	SIX	X
Intent shared (IS)	Yes	Yes	Yes	Yes	Yes	No
Shared (S)	Yes	Yes	Yes	No	No	No
Update (U)	Yes	Yes	No	No	No	No
Intent exclusive (IX)	Yes	No	No	Yes	No	No
Shared with intent exclusive (SIX)	Yes	No	No	No	No	No
Exclusive (X)	No	No	No	No	No	No

BLOCKING:

In blocking, one process is holding s resource that another process requires. SQL knows that once the blocking process finishes the resource will be available and so the blocked process will wait (until it times out), but it won't be killed.

DEADLOCK

In a deadlock, there are 2 processes. P1 & P2 trying to get to 2 resources R1 & R2.

P1 gets a lock on R1

and

P2 gets a lock on R2

THEN

P1 tries to get a lock on R2 but can't because it is locked by P2

and

P2 tries to get a lock on R1 but can't because it is locked by P1

At this point neither process can finish because they are both waiting on locked resources. i.e. they are deadlocked. One of them must be killed to allow either of them to finish.

SQL decides which is the "least expensive process to kill" and it becomes the Deadlock victim.

Minimizing 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.
• Avoid user interaction in transactions.
• Keep transactions short and in one batch.
• Use a lower isolation level.
• Use a row versioning-based isolation level.
• Set READ_COMMITTED_SNAPSHOT database option ON to enable read-committed transactions to use row versioning.
• Use snapshot isolation.
• Use bound connections.

Transactions and Locks

* A transaction is single unit of work which may consists of one or more commands.

* Transactions works with ACID properties

          * Automicity

          * Consistancy

          * isolation

          * Durability

* SQL Server supports 2 types of transactions

          * Implicit

          * Explicit

* By default SS supports implicity transaction where for every insert, update and delete 3 records are stored in T.Log file

          Begin tran

                   insert/update/delete

          commit tran

* To implement business logic i.e. according to the required if we want to commit or rollback the changes we can use explicit transactions.

          Begin Tran    

                   ----

          commit/rollback tran

* Any transaction which consists of manipulations places locks on the tables.

* By default when we make a db as current db automatically Shared Lock is placed.

* At the working with insert,update,delete by default SS places Exclusive lock.

* Type of locks placed on objects depends on isolation levels.

What is a Deadlock?

Deadlocks happen when conflicting locks are taken by two or more processes.  In rare scenarios, I have heard of deadlocks caused by SQL Statements executing in parallel however I cannot reproduce it. Neither side is willing to step aside for the other processes.  Think of going shopping for furniture and two people (Person A and Person B) go in at same time.  Unknown to them they both want the same goods (Good A and Good B).  One starts searching from right another person starts searching from left side of the store.  Person A finds good A (Exclusive Lock) and starts looking for Good B (Shared Look).  But while Person A was searching for Good A, Person B found Good B (Exclusive Lock) and starts looking for Good A (Shared Lock).  When they find the other good they realize it is already reserved and cannot have it, neither Person A nor Person B is willing to let the other person have both the Goods. Therefore we are stuck in a deadlock.

In SQL Server to prevent this stalemate (i.e. a deadlock) from filling up the system, we have a Deadlock Monitor thread that is running in the background to “help” resolve deadlocks.

What is termed as a deadlock victim?

In case of deadlock, SQL Server will pick one task/session, whichever will be least expensive to rollback and kill it. This session/task is termed as a deadlock victim.

How do we monitor deadlocks?

Process	Comments
Windows Performance Monitor	This provides all the deadlocks that have happened on your server since the last restart.  We can look at this counter using the following SQL Statement:
Object: SQLServer: Locks	SELECT cntr_value AS NumOfDeadLocks
Counter: Number of Deadlocks/sec	FROM sys.dm_os_performance_counters
Instance: _Total	WHERE object_name = 'SQLServer:Locks'
	AND counter_name = 'Number of Deadlocks/sec'
	AND instance_name = '_Total'
Trace Flags1204 and 1222	Trace flag 1204 has existed since at least SQL Server 2000.  And Trace Flag 1222 was introduced in SQL Server 2005.Both output Deadlock Information to the SQL Server ERRORLOG.
SQL Server Profiler/Server Side Trace Event Class: Locks Event Name: Deadlock Graph	Gives an XML Graph like the example above.  Very easy to read and figure out what is going on.
Extended Events	The new way to do monitoring in SQL Server.  Extended Events are eventually going to replace the SQL Server Profiler all together (i.e. SQL Server Profiler is on the Deprecated Feature list).  It produces the same XML graph as SQL Server Profiler, and is lighter in performance impact
System Health	This is a “new” default trace, but it’s unlike the default trace that had limited the amount of information being tracked and could not be modified.  We can modify a system health definition, which is built on a Extended Events.  But unlike the default trace, the System Health tracks the Deadlocks that have occurred recently.  So we can pull this information from the System Health for analysis instead of implementing our own Extended Events monitoring.

Deadlock Monitoring Using the Extended Events Trace

1.     Navigate to SQL Server

2.     Management.

3.     Extended Events

4.     Sessions.

5.     Right Click on Sessions, Go to New Session Wizard.

6.     Enter in Session Name “Deadlock_Monitor” click Next.

7.     Select Do not use a template and click Next (Templates are like SQL Server Profiler Templates, where you have defaults to start with, but none of the templates fit our needs).

8.     This is where we define what events we want to capture. Type “deadlock” in Event Library.  We get following image.

9.     Click on “xml_deadlock_report” and click “>” to add it.  Click Next to continue.

10.   Now we need to define what columns we want to capture, for now just click Next.

11.   Define any filters needed. For now we’ll ignore this setting.  Click Next to continue.

12.   Select “Save data to file”, enter file name, size, etc.  Click Next to continue.

13.   Review all the configurations and click Finish to setup and start the session.

14.   Now you can start the capture and watch it live.

Deadlocks can be traced by turning on two specific flags:

-- Activate deadlock tracing

dbcctraceon (1204, 3605, -1)

go

dbcctracestatus(-1)

go

-- Turn off deadlock tracing

dbcctraceoff(1204, 3605)

go

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

·         Setting MAXDOP=1 solves deadlocking in some cases

“Deadlock graph”, “Lock:Deadlock” and “Lock:Deadlock Chain” events  from profiler