(This is part of the Study Guide series,
70-464)
Microsoft’s
Measured Skill description: This objective may include but is not
limited to: choose the right lock mechanism for a given task; handle and/or
avoid deadlocks; fix locking and blocking issues caused by previous development
or third-party applications; analyze a deadlock scenario to alleviate the
issue; impact of isolation level and Microsoft ADO.NET defaults; impact of
locks and lock escalation; reduce locking scenarios; understand how isolation
levels affect blocking and locking; identify bottlenecks in the data design and
make improvements
What
I see:
·
capture deadlocking
·
analyze locks
·
isolation levels and their explanations
·
ADO.NET defaults
·
lock escalation
Capture deadlocking
There are a couple of ways to
capture deadlocks. The first one I’m
going to go over is by the use of a trace flag (1204). Coupled with trace flag 3605 you can have
deadlock information written directly to the SQL Server error log. For my testing purposes, I set up the typical
deadlock scenario: Created two tables,
and used an exclusive lock on each table in two sessions, then before committing
swapped them up to create my deadlock.
Here’s the output I saw in my error log:
Deadlock
encountered .... Printing deadlock information
Wait-for
graph
Node:1
RID:
11:1:276:0 CleanCnt:2
Mode:X Flags: 0x3
Grant List 1:
Owner:0x00000002F5908A80 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:53
ECID:0 XactLockInfo: 0x00000002F2C043E8
SPID: 53 ECID: 0 Statement Type: UPDATE Line
#: 2
Input Buf: Language Event:
update DlTable2
set int1 = 6
where int2 = 1;
commit tran;
Requested
by:
ResType:LockOwner
Stype:'OR'Xdes:0x00000002F2C056A8 Mode: U SPID:54 BatchID:0 ECID:0
TaskProxy:(0x00000002EB0CE608) Value:0xf5908b00 Cost:(0/144)
NULL
Node:2
RID:
11:1:278:2 CleanCnt:2
Mode:X Flags: 0x3
Grant List 1:
Owner:0x00000002F5908540 Mode: X Flg:0x40 Ref:0 Life:02000000 SPID:54
ECID:0 XactLockInfo: 0x00000002F2C056E8
SPID: 54 ECID: 0 Statement Type: UPDATE Line
#: 2
Input Buf: Language Event:
update DlTable1
set int1 = 3
where int2 = 1;
commit tran;
Requested
by:
ResType:LockOwner
Stype:'OR'Xdes:0x00000002F2C043A8 Mode: U SPID:53 BatchID:0 ECID:0
TaskProxy:(0x00000002EB0EA608) Value:0xf5906b80 Cost:(0/144)
NULL
Victim
Resource Owner:
ResType:LockOwner
Stype:'OR'Xdes:0x00000002F2C056A8 Mode: U SPID:54 BatchID:0 ECID:0
TaskProxy:(0x00000002EB0CE608) Value:0xf5908b00 Cost:(0/144)
Relatively interesting
information; you see the two deadlock participants, as well as their statements
that were running. You can even see
which SPID that SQL Server chose as the victim (in this case, 54). Another way is through SQL Trace. There are three event classes you can capture
for deadlocks: Lock: Deadlock, Lock:
Deadlock chain, and Deadlock graph. The
Deadlock graph for the above scenario looks like this:
Analyze locks
The overarching DMV for
analyzing locks is sys.dm_tran_locks. It gives a current view on the locks that are
acquired on the instance. Here’s a
little test demo:
Example
use MeasureMySkills;
go
create table LockedTable
(
id int identity(1, 1) not null,
SomeString1 nvarchar(100) not null
default replicate('a', 100),
SomeString2 nvarchar(100) not null
default replicate('b', 100)
);
go
insert into LockedTable
values(default, default);
go 100
begin tran;
update LockedTable
set SomeString1 = replicate('c', 100)
where id
between 5 and 7;
--commit tran;
select
resource_type,
resource_description,
resource_associated_entity_id,
request_mode,
request_status
from sys.dm_tran_locks;
What the above shows us is that
there are three RID locks (these would be KEY locks if this was an index) that
have exclusive (X) locks on them. Notice
the intent locks (IX) of the objects that are higher in the lock hierarchy,
including a page and an object (the actual table itself). This is an optimization strategy by SQL
Server to have quick notification that there is an exclusive lock (X) somewhere
lower in the lock hierarchy. The use of
this DMV can show a vast amount of information pertaining to current locking
issues. Also, if you want to get a good
idea if locks are causing a lot of waits, you can look at the sys.dm_os_wait_stats
DMV, and if a LCK_M_* prefixed wait type is high up there then you’re experiencing
a cumulatively large delay due to concurrency.
Isolation levels
This topic can be a one sentence
explanation for each isolation level, or can be very in depth due to the nature
of these concurrency policies. Due to
this, I recommend reading what each of these
are and learning how they prevent/allow dirty reads, non-repeatable reads,
and phantom reads.
ADO.NET defaults
I believe the answer here is
READ COMMITTED. I have written a little
application to open a connection and select the trans isolation level for the
current SPID and that’s what my result was.
If you have another theory, I’d love to hear it. Nothing too evident and clear-cut while
researching this topic.
Lock escalation
Lock escalation is the process
that SQL Server takes when it has too many row or page locks, and then attempts
to escalate the lock to a table lock. It
will convert the IS/IX lock to the appropriate S/X lock. There are thresholds that will cause this:
·
a statement obtains more than 5,000 row or page
locks (and lock escalation isn’t disabled)
·
memory consumption of config threshold is
reached
At this point, SQL Server will attempt to escalate to a table lock. If this isn’t possible due to concurrency
restrictions, the database engine will attempt this lock escalation after
another 1,250 locks have been acquired.
You can disable lock escalation a few ways:
Example
alter table LockedTable
set
(
lock_escalation = disable
);
go
dbcc traceon(1211); -- or 1224
go
The first method is at the table
level. The second is instance-wide. Trace flag 1211 disables all lock escalation,
whereas 1224 just disables it based on number of locks (but doesn’t prevent
based on memory pressure).
References
Version-Controlled Code (GitHub)
If there are any comments, questions, issues, or suggestions please feel
free to leave a comment below or email me at sqlsalt@gmail.com.
No comments:
Post a Comment