On the gridiron, two opposing teams are contending for one very limited resource, the football. One team has it; the other team wants it. The team that has it, tries very hard to protect it by securing the football and blocking all others from getting to it.

The same can be said about SQL Server. When one process needs to access a resource, say a table, it secures the resource (locking) which prevents other processes from accessing it (blocking).

Locking in SQL Server is a good thing. Locking helps to maintain data integrity by ensuring that two different processes are not going to use a single resource in a way that is considered incompatible.

Blocking, a natural by-product of locking, is not so good of a thing.

If a resource is locked by one process and another process needs access to that resource, the second process will be blocked until the resource is released by the first process.  The effect? The second process experiences a longer wait time and performance suffers.

But locking and blocking are two sides of the same coin.

Locking & Blocking Made Simple

Okay, if locking is good and blocking is bad, what can be done about locking? I’m glad you asked! Tomorrow, I’m speaking at the Nashville SQL Server User Group. In the session, I’ll cover those topics and more. Here’s the abstract:

A good working knowledge of how Microsoft SQL Server makes use of locking and transaction isolation levels can help you greatly improve an application’s performance. In this session, we explore the SQL Server locking methodology and share techniques for enhancing query response times.

If you’re in the Middle Tennessee area, come on out and join us for the meeting. For more information on time, location, etc, visit the Nashville SQL Server User Group web site.

If you’re not in the Nashville area, I believe the meeting will be available via LiveMeeting provided by PASS. Stay tuned and follow me or NashSQL on Twitter for up to the connection information.

Thanks and I hope to see you there!