Pages

Monday, May 14, 2012

Implement Database Objects: Design the locking granularity level

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:

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

useMeasureMySkills;

go

createtableLockedTable

(

idintidentity(1, 1)notnull,

SomeString1nvarchar(100)notnull

defaultreplicate('a', 100),

SomeString2nvarchar(100)notnull

defaultreplicate('b', 100)

);

go

insertintoLockedTable

values(default,default);

go 100

begintran;

updateLockedTable

setSomeString1=replicate('c', 100)

whereid

between 5 and 7;

--commit tran;

select

resource_type,

resource_description,

resource_associated_entity_id,

request_mode,

request_status

fromsys.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:

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

altertableLockedTable

set

(

lock_escalation=disable

);

go

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