|
|||||||||||||||||||
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.
* 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
Object: SQLServer: Locks Counter: Number of Deadlocks/sec Instance: _Total |
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:
SELECT cntr_value AS NumOfDeadLocks
FROM
sys.dm_os_performance_counters
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
No comments:
Post a Comment