Implement Database Objects: Design the locking granularity level

(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_statsDMV, 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.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s