Monday, May 14, 2012

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


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)

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)
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:

use MeasureMySkills;

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)

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;

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:

alter table LockedTable
       lock_escalation = disable

dbcc traceon(1211); -- or 1224

                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).


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

No comments:

Post a Comment