Transcript Document

DAT382
Troubleshooting Deadlocks in
SQL Server 2000
Ron Talmage
Prôspice, L.L.C.
[email protected]
Agenda: Troubleshooting
Deadlocks in SQL Server 2000
•
•
•
•
•
I. Deadlocking Concepts
II. Types of Lock Deadlocks
III. Gathering Deadlock Information
IV. Finding Causes
V. Resolution Techniques
• Focus of this session will be on deadlocks due
to lock contention
Troubleshooting Deadlocks in
SQL Server 2000
•
•
•
•
•
I. Deadlocking Concepts
II. Types of Deadlocks
III. Gathering Deadlock Information
IV. Finding Causes
V. Resolution Techniques
Deadlocking Defined
• A SQL Server deadlock involves two or
more server threads or processes (spids)
• Each must wait to acquire some resource
that the other thread already has
• Neither thread can finish, resulting in a
deadly embrace
Deadlocking Illustrated
Thread 1
Resource
Resource
Thread 2
Grant
Blocked
request
Wait
SQL Server Resource Waits
• According to BOL, SQL Server threads can wait
on
–
–
–
–
–
Locks
Parallel query resources
Other threads
Memory resource
Application events
• Deadlocking can occur with locks, parallelism,
threads, and application events.
– Memory waits are resolved by query time-out.
– Sp_getapplock deadlocks are treated differently
• The most frequent source of SQL Server
deadlocking is resource locking
Deadlocks Involving Locks
• Involve two or more spids, usually each in a
transaction, and one or more resources
• The deadlock takes place in two stages:
– Grant stage
• Each spid is granted a lock on its resource
– Blocked Request stage
• Each spid requests an incompatible lock on the other thread's
resource
• Each spid waits on the other to release its locks before it can
complete, entering a WAIT state
• SQL Server detects the deadlocked state and
chooses one of the spids as the victim
Sample Deadlock Transaction
History
Time
Spid 1
Spid 2
T1
Begin Tran
Begin Tran
T2
Granted
Lock Request
T3
Granted
T4
Blocked
Lock Request
Lock Request
(WAIT on Tran2)
T5
Blocked
T6
T7
Lock Request
(WAIT on Tran1)
Deadlock Victim
(blocking removed)
Commit
Note: Deadlocking
is more than Blocking
• Blocking occurs when one spid is waiting on
another
• Some blocking is normal
– Expected in an RDBMS that uses locking to maintain
transaction isolation, and not versioning
– Only blocks with long durations should be considered a
problem
• Lock-based deadlocking is a special type of
blocking
– Two or more spids mutually block each other
How SQL Server
handles a Deadlock
• The Lock Monitor thread (spid 4) detects the
deadlock
– Uses periodic detection (<5 seconds)
• Automatically chooses one spid as the deadlock
victim
– Rolls back its transaction
– Returns error 1205 to the client
– Cancels its current query
• Generally chooses the least expensive transaction
to roll back
– You can override this somewhat using SET
DEADLOCK_PRIORITY to LOW for a session
Error 1205 Notes
• Text of the message:
"Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 54) was deadlocked on lock
resources with another process and has been chosen
as the deadlock victim. Rerun the transaction."
• Error 1205 is non-fatal - connection not terminated
– SQL Server aborts the batch and cancels the query
– Error 1205 cannot be detected by @@ERROR within
the batch or from calling a stored procedure
– Severity level 13 – correctable by the user
• Does not reveal
– Statements at the Grant stage
– Statements at the Blocked Request stage
– The identity of the other participating spid or spids
SQL Server Deadlocking Factors
1.
2.
3.
4.
Lock compatibility
Transaction timing
Order of lock requests
Isolation level
1. Lock Compatibility (lock modes)
Lock Mode
Abbreviation
Description
Schema-Stability
Sch-S
Used when compiling queries
Schema
Modification
Sch-M
Used for DDL operations (ALTER or DROP) on a table
schema
Shared
S
Used for reading (read lock)
Update
U
Used to evaluate prior to writing (may become exclusive)
Exclusive
X
Used for writing (insert, update, delete)
Intent Shared
IS
Have or intend to request shared lock(s) at a finer level
Intent Update
IU
Have or intend to request update lock(s) at a finer level
Intent Exclusive
IX
Have or intend to request exclusive lock(s) at a finer level
Shared Intent
Update
SIU
Have shared lock with intent to acquire update lock at a
finer level
Shared Intent
Exclusive
SIX
Have shared lock with intent to acquire exclusive lock at a
finer level
Update Intent
Exclusive
UIX
Have update lock with intent to acquire exclusive lock at a
finer level
Bulk Update
BU
Used with bulk copy into a table with either TABLOCK hint
or table lock option is set
Lock Compatibility Matrix
(default isolation level)
Lock Mode Already Granted
Lock Mode
Requested
S
U
X
IS
IU
IX
SIX
Sch-S
Sch-M
BU
S
Yes
Yes
No
Yes
Yes
No
No
Yes
No
No
U
Yes
No
No
Yes
No
No
No
Yes
No
No
X
No
No
No
No
No
No
No
Yes
No
No
IS
Yes
Yes
No
Yes
Yes
Yes
Yes
Yes
No
No
IU
Yes
Yes
No
Yes
Yes
Yes
?
Yes
No
No
IX
No
No
No
Yes
Yes
Yes
No
Yes
No
No
SIX
No
No
No
Yes
?
No
No
Yes
No
No
Sch-S
Yes
Yes
Yes
Yes
Yes
Yes
Yes
Yes
No
Yes
Sch-M
No
No
No
No
No
No
No
No
No
No
BU
No
No
No
No
No
No
No
Yes
No
Yes
Lock Compatibility Exception
• Exclusively-locked rows can be read on pages
with no data changes:
– Xlock row-locking hint (reported by Fernando Guerrero)
Select *
From titleauthor (With xlock)
Where au_id = '172-32-1176'
– A no-op Update statement:
Update titleauthor
Set royaltyper = royaltyper
Where au_id = '172-32-1176'
• If any data on the page is changed, the
exclusive lock appears to be honored.
2. Transaction Timing
• Transactions must overlap in time for
blocking and deadlocking to occur
• Transactions must have sufficiently long
duration to make blocking and
deadlocking likely
• Some deadlocks require a large number
of concurrent transactions to surface
3. Order of Lock Requests
• Initial locks must be granted before
incompatible locks are requested
• Changing the order of lock requests may
release locks early or remove
incompatible locks
4. Isolation Level
• Higher isolation levels make deadlocks more
likely
• Deadlocks involving all exclusive locks are
independent of isolation level
• Some deadlocks involving shared locks can
occur with the READ COMMITTED isolation
level
• Other deadlocks involving shared locks require
isolation levels higher than READ
COMMITTED
Isolation Level and Lock Accumulation
Lock
Mode
Read Uncommitted
Read
Committed
Repeatable Serializable
Read
Shared
Held until data
read and
processed
Held until data
read and
processed
Held until end
of transaction
Held until end
of transaction
Update
Held until end of
transaction
unless promoted
to exclusive or
released
Held until end of
transaction
unless promoted
to exclusive or
released
Held until end
of transaction
unless
promoted to
exclusive
Held until end
of transaction
unless
promoted to
exclusive
Exclusive
Held until end of
transaction
Held until end of
transaction
Held until end
of transaction
Held until end
of transaction
Troubleshooting Deadlocks
in SQL Server 2000
•
•
•
•
•
I. Deadlocking Concepts
II. Types of Lock Deadlocks
III. Gathering Deadlock Information
IV. Finding Causes
V. Resolution Techniques
Types of Lock Deadlocks
• 1. Single-Resource Deadlock
– Sometimes called 'conversion' deadlock but also is a
cycle
– Involves a single resource
– Deadlock occurs when attempting a conversion from
compatible to incompatible locks
• 2. Multi-Resource Deadlock
– Sometimes called ‘cycle’ deadlock
– Involves distinct resources for each thread
– Deadlock occurs when each spid requests an
incompatible lock on the other’s resource
• 3. Application resource Deadlocks
– Use sp_getapplock
1. Single-Resource Deadlock
Overview
• Involves only one resource
• Initial Grant stage requires shared locks,
because both spids must obtain a lock on a
single resource
– Requires an isolation level higher than READ
COMMITTED to hold share locks
• Blocked Request stage requires exclusive
locks
Single-Resource Deadlock
Illustrated
Spid 1
Resource
Spid 2
Grant
Blocked
request
Wait
Single-Resource Deadlock Example
Time
Spid 1
Spid 2
T1
Begin Tran
Begin Tran
T2
Granted
Select *
From Authors With (HOLDLOCK)
Where au_id = '172-32-1176'
Select *
From Authors With (HOLDLOCK)
Where au_id = '172-32-1176'
T3
Granted
T4
Blocked
Update Authors
Set contract = 0
Where au_id = '172-32-1176'
Update Authors
Set contract = 1
Where au_id = '172-32-1176'
T5
Blocked
T6
T7
Deadlock Victim
(blocking removed)
Commit
Single-Resource Deadlock Notes
• Often called 'conversion' deadlocks
– Attempt is made to 'convert' a shared lock to an exclusive
lock
– Update lock mode prevents a type of conversion deadlock
• Require shared locks at the Grant stage
• Are comparatively rare because they require a
higher than default isolation level:
• SQL Server’s default isolation level is READ COMMITTED
• Conversion deadlocks require REPEATABLE READ or
SERIALIZABLE isolation levels
• HOLDLOCK hint is effectively the SERIALIZABLE isolation level
• Require exclusive locks at the Blocked Request
stage
– Requests must be blocked by existing share locks
2. Multi-Resource Deadlocks
Overview
• Involve more than one resource
• Grant stage normally requires exclusive locks
• There are many variations; here are two:
– Exclusive only (X-only): spids only make exclusive lock
requests
– Mixed exclusive and shared (X-S):
• First, spids are granted exclusive locks
• Then they request shared locks and are blocked
• Mixed X-S deadlocks can be more subtle and challenging
MR Deadlock Illustrated
Spid 1
Res 1
Res 2
Spid 2
Grant
Blocked
request
Wait
X-only MR Deadlock Variation
• Involves only exclusive locks
• Simplest type of MR deadlock
• Works at any isolation level
– Exclusive locks are always held to the end
of a transaction
X-only MR Deadlock Example
Time
Spid 1
Spid 2
T1
Begin Tran
Begin Tran
T2
Granted
Update Authors
Set contract = 0
Where au_id = '172-32-1176'
Update Titles
Set ytd_sales = 0
Where title_id = ‘BU1032'
T3
Granted
T4
Blocked
Update Titles
Set ytd_sales = 0
Where title_id = ‘BU1032'
Update Authors
Set contract = 0
Where au_id = '172-32-1176'
T5
Blocked
T6
T7
Deadlock Victim
(blocking removed)
Commit
X-only MR Deadlock Notes
• Relatively easy to diagnose
– Involves two spids, each in a transaction, accessing the
same resources in inverse order
• Could use Update locks in place of Exclusive locks
– Like an exclusive lock, only one update lock can be
placed on a resource at one time
• Often used as an example of 'cycle' deadlock
• Easy to prevent if all resources are accessed in
the same order
Mixed X-S MR Deadlock Variation
• Can be a more complex and subtle type of
deadlock
• Many variations:
–
–
–
–
–
X-X:S-S,
X-X-X:S-S-S,
X-IS:IS-X,
S-IX:IX-S,
X-X:U-U, etc.
• Can occur with the READ COMMITTED isolation
level
Mixed X-S MR Example #1
Time
Spid 1
Spid 2
T1
Begin Tran
Begin Tran
T2
Granted
Update Authors
Set contract = 0
Where au_id = '172-32-1176'
Update Titles
Set ytd_sales = 0
Where title_id = ‘BU1032'
T3
Granted
T4
Request
Select *
From Titles
Where title_id = ‘BU1032'
Select *
From Authors
Where au_id = '172-32-1176'
T5
Request
T6
T7
Deadlock Victim
(blocking removed)
Commit
Mixed X-S MR Example #2
Time
Spid 1
Spid 2
T1
Begin Tran
Begin Tran
T2
Granted
Insert Authors Values
('111-11-1111', 'test1', '', '', '', '',
'', '11111', 0)
Insert Authors Values
('111-11-1112', 'test2', '', '', '', '', '',
'11111', 0)
T3
Granted
T4
Request
Select *
From Authors
Select *
From Authors
T5
Request
T6
T7
Deadlock Victim
(blocking removed)
Commit
Mixed X-S MR Deadlock Notes
• Can be much more difficult to diagnose
• Can take place with the default isolation level of
READ COMMITTED
• SELECT statements can also participate with
READ COMMITTED or higher isolation level
• Requires exclusive locks at the initial stage
– An update lock is compatible with the subsequent
shared locks
3. A Note About sp_getapplock
• Used for locking an application resource by name
– Locks a resource name
• Shared, Update, Exclusive, IntentExclusive, IntentShared
–
–
–
–
Procedure is a wrapper around xp_userlock
Can be used in a transaction with Commit and Rollback
Managed by the lock manager
Application locks (type APP) are reported by sp_lock
• Deadlocks
– Reported in Profiler
– Not reported by the –1204 trace flag
– Stored procedure returns a –3, not 1205 error
Troubleshooting Deadlocks
in SQL Server 2000
•
•
•
•
•
I. Deadlocking Concepts
II. Types of Lock Deadlocks
III. Gathering Deadlock Information
IV. Finding Causes
V. Resolution Techniques
Gathering Deadlock Information
• 1. Use the Perfmon Deadlocks/sec counter
• 2. Use the 1204 trace flag
– Redirect deadlock event information to the SQL
Server Error Log
• 3. Use SQL Trace/Profiler
1. Use the Deadlocks/sec Sysmon
(Perfmon) counter
• Useful in measuring frequent deadlocking
• Useful when combined with multiuser testing
to determine the minimum number of users
required to reproduce deadlocks
Adding the Deadlock Counter
2. Use the 1204 Trace Flag
• Used in combination with the 3605 flag:
DBCC TRACEON(3605)
DBCC TRACEON(1204)
• Output will be sent to the SQL Server error log
• It can be useful to make a SQL Agent job execute
these statements at startup
• Can be used in combination with a SQL Server
Agent Alert defined on Error 1205.
Retrieving 1204 Output
• When using Enterprise Manager to read the
1204 output, statements occur in unusual
order
– Do not export the log
• Instead, use a text editor like Notepad to
read the statements in correct order
– You can strip out the datetime and spid data to
give more readable output
Sample Trace Flag 1204 Output
(using MR Example #2)
Locked resource (leaf node
of clustered key, I.e. row)
Deadlock encountered .... Printing deadlock information
Hash of key value
Wait-for graph
Type of lock granted
Node:1
KEY: 7:1977058079:1 (f600d547433a) CleanCnt:1 Mode: X Flags: 0x0
Grant List::
Owner:0x19090e20 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:54 ECID:0
SPID: 54 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: Select * From Authors
Spid owning the lock
Current input buffer
Requesting spid and
lock type requested
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:53 ECID:0 Ec:(0x19c5d530) Value:0x19090dc0 Cost:(0/114)
Node:2
KEY: 7:1977058079:1 (f5006f164aa3) CleanCnt:1 Mode: X Flags: 0x0
Grant List::
Owner:0x19091000 Mode: X
Flg:0x0 Ref:0 Life:02000000 SPID:53 ECID:0
SPID: 53 ECID: 0 Statement Type: SELECT Line #: 1
Input Buf: Language Event: Select * From Authors
Deadlock victim spid
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:54 ECID:0 Ec:(0x19dc3530) Value:0x19091180 Cost:(0/114)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:54 ECID:0 Ec:(0x19dc3530) Value:0x19091180 Cost:(0/114)
Interpreting
Trace Flag 1204 Output
• KEY: 7:1977058079:1 (f600d547433a)
– Database 7, Object 1977058079, Indid 1
• Reflects a row lock on a clustered index
– Hash of key value: f600d547433a
• Spid numbers are important when crossreferencing to SQL Trace data
– See 'Displaying Locking Information' and
'Troubleshooting Deadlocks' in BOL for more
information
Trace Flag 1204 Output Notes
• What 1204 shows:
– The input buffer of the Blocked Request statements
– The locks held at the Grant stage
• What it does not show:
– The statements initiating the Grant locks
– The transaction history for each spid
• Input buffer may be incomplete
– Some system deadlocks provide incomplete information
3. Use SQL Trace with Profiler
• Add Deadlock events
• Save trace data to a table
• Reproduce the transaction history of the
deadlock
Configuring SQL Trace/Profiler
• When tracing specifically for deadlocks, include
– Events
•
•
•
•
Cursors: CursorExecute, CursorPrepare
Errors and Warnings: Attentions and Exceptions
Locks: Lock:Deadlock and Lock:Deadlock Chain
Stored Procedures: RPC:Completed, RPC:Starting,
SP:StmtCompleted, SP:StmtStarted
• Transactions: SQL Transaction
• TSQL: SQL:BatchCompleted, SQL:BatchStarting,
SQL:StmtCompleted, SQL:StmtStarting
– Data Columns:
• EventClass, EventSubclass, Spid, IntegerData, Mode, StartTime,
TextData, DatabaseID, IndexID, HostName, ObjectID,
TransactionID, Severity
SQL Trace and SP2
• Make sure you apply SQL Server 2000
Service Pack 2 for deadlock tracing
• See "FIX: Deadlock Information Reported
with SQL Server 2000 Profiler Is Incorrect"
(Q282749)
Viewing SQL Trace Output in Profiler
Deadlock spids now
cross-referenced in
SP2
SQL Trace Output Notes
• What SQL Trace shows:
– The statements involved in the deadlock
• The deadlock victim will have a statement starting but not
ending
– Shows the beginning and end of each spid's
transaction
– Can be used to reproduce each deadlocking spid's
transaction history
• What it does not show:
– The statements gaining the initial locks at the Grant
stage
Agenda: Troubleshooting
Deadlocks in SQL Server 2000
•
•
•
•
•
I. Deadlocking Concepts
II. Types of Lock Deadlocks
III. Gathering Deadlock Information
IV. Finding Causes
V. Resolution Techniques
Isolating the Cause
• Find a method to reproduce the deadlock
– Stress test, load test
– Minimal conditions possible
• Reproduce with both 1204 Trace Flag and SQL
Trace active
• Examine Trace Flag 1204 output
– Determine which spids are involved
– Determine what types of locks are involved
• Get each spid’s transaction history
– Save SQL Trace data output to SQL Server table
– Reproduce the deadlock under controlled conditions
Steps for Reproducing a
Complex Deadlock
• Save the Trace output to a table
• Find the beginning and ending row numbers of
each spid's transaction
• Select the StartTime and TextData for a given spid
the transaction in question
• Arrange the transaction histories according to
StartTime
• Remove extraneous commands
• Observe locks held using sp_lock
Extracting a Transaction History
Reproducing the Deadlock
Observing Locks
•
•
Use sp_lock to detect locks per spid
Additional tools:
•
•
Both sp_lock2 and sp_blockinglocks from Kalen
Delaney, Inside SQL Server 2000
Identify the statements resulting in the Grant
stage locks
•
Find the statement causing the lock found in the
1204 output
Identifying the Cause (query)
Identifying the Cause (locks)
KEY: 7:1977058079:1 (f600d547433a)
(from trace flag 1204 output)
Troubleshooting Deadlocks
in SQL Server 2000
•
•
•
•
•
I. Deadlocking Concepts
II. Types of Lock Deadlocks
III. Gathering Deadlock Information
IV. Finding Causes
V. Resolution Techniques
Resolution Techniques
•
•
•
•
1. Remove incompatible lock requests
2. Change the timing of transactions
3. Change the order of resource requests
4. Lower the isolation level
1. Remove incompatible lock
requests
• Queries may be obtaining too many locks
– WHERE clauses may be too broad
• I.e., reading a whole table or range of rows where
only one row's data is actually desired
• Queries may be obtaining the wrong kinds
of locks
– Using hints, such as HOLDLOCK or UPDLOCK,
unnecessarily
2. Change the timing of
transactions
• Make transactions shorter
– Long transactions increase the likelihood of
deadlocks
• Transactions may not need to overlap
– Run batch processes sequentially rather than in
parallel
3. Change the order of resource
requests
• For X-only Multi-Resource deadlocks,
ensure that resources are accessed in the
same order
• May not work for Mixed X-S MR deadlocks
4. Lower the Isolation Level of the
Transaction or Session
• Use SET TRANSACTION ISOLATION LEVEL
• For single-resource 'conversion' deadlocks
– lower the isolation level to READ COMMITTED or
READ UNCOMMITTED
• For Mixed X-S MR deadlocks, with Share locks
involved, lower the isolation level to READ
UNCOMMITTED
• For X-only MR deadlocks, lowering the isolation
level will not work
– Exclusive locks are held until the end of the transaction
Lower the Isolation Level of a
Query
• For Mixed X-S MR deadlocks, use a locking
hint on the Share lock queries:
– Use READPAST when it is not important to read
uncommitted data
– Use NOLOCK when uncommited data must
also be read
• Most effective for low-impact resolution
Some Sample Deadlocks: Trace
Flag 1204 Output
•
•
•
•
Deadlock involving Parallelism
Cascading deadlock
Page and IX deadlock
As time permits…
Additional Resources
• SQL Server Locking Internals
– Inside SQL Server 2000, Kalen Delaney (MS Press)
• Transaction Isolation Level Concepts
– Transaction Processing: Concepts
and Techniques, Gray and Reuter (Morgan Kaufmann)
– http://research.microsoft.com/~gray/Isolation.doc
“A Critique of ANSI SQL Isolation Levels”
– DAT380, Fernando Guerrero, “Concurrency Problems and Locking
Techniques in SQL Server 2000 and Visual Basic .NET”
• Relevant Knowledge Base Articles
– Q224453 - INF: Understanding and Resolving SQL Server 7.0
or 2000 Blocking Problems
– Q271509 - INF: How to Monitor SQL Server 2000 Blocking
– Q282749 - FIX: Deadlock Information Reported with SQL
Server 2000 Profiler Is Incorrect
Thank You
• Please fill out and turn in your evaluation!
• If you have an interesting deadlock, please
send the 1204 output to:
– [email protected]
Don’t forget to complete the
on-line Session Feedback form
on the Attendee Web site
https://web.mseventseurope.com/teched/