Lock, block and deadlock are three related terms in SQL Server. They have a specific meaning and are often confused with each other. I hope to clear them up here.
(I’m using a new visual format. This means you RSS readers will have to come by and see it in person.)
Process
A process is something that performs computations. If you’ve studied computer science, you know this concept well. Processes in SQL Server typically execute queries or other statements.
A process is something that performs computations. If you’ve studied computer science, you know this concept well. Processes in SQL Server typically execute queries or other statements.
Resource
A resource is a database object like a table or a row. Resources are accessed by processes.
A resource is a database object like a table or a row. Resources are accessed by processes.
Locks
When a process wants to use or modify a resource. It needs to reserve it. This reservation is called a lock. SQL Server grants locks to processes to coordinate their access to resources.
When a process wants to use or modify a resource. It needs to reserve it. This reservation is called a lock. SQL Server grants locks to processes to coordinate their access to resources.
Locks
When a process is finished, SQL Server releases the locks.
When a process is finished, SQL Server releases the locks.
Blocking
When more than one process needs access to a resource, SQL Server will sometimes make one process wait. A waiting process is blocked.
When more than one process needs access to a resource, SQL Server will sometimes make one process wait. A waiting process is blocked.
Blocking
Once the first process completes, the other one will continue.
Once the first process completes, the other one will continue.
Deadlocks
Occasionally, blocking occurs in such a way that processes wait on each other. This is called a deadlock
Occasionally, blocking occurs in such a way that processes wait on each other. This is called a deadlock
Deadlocks
SQL Server will automatically detect a deadlock. It will choose a process to kill. This process is called a deadlock victim.
SQL Server will automatically detect a deadlock. It will choose a process to kill. This process is called a deadlock victim.
Deadlocks
After the one process is killed, the other process is allowed to continue.
After the one process is killed, the other process is allowed to continue.
But There’s One More Thing…
As long as I’m experimenting with visual formats, check out the same content in another medium.
The Locking Primer Presentation
This was just a primer. The best resource page remains Kendra Little’s Locking and Blocking in SQL Server.
Why did I pick this content?
My parents recently found out that I made a webcast and posted it on Youtube. They watched the whole 15 minutes. Not for any database lessons, but to watch how I spoke. I couldn’t help but put myself in their shoes. They would have needed the smallest introduction in order to get something out of the webcast. That small introduction is what you see here.
Why these new formats?
A number of reasons, and not just novelty.
Jonathan Corum is a science graphics editor at the New York Times (That’s what I want to be when I grow up). Jonathan gave a talk called “the Weight of Rain” at a visualized conference and he sums it up here. I loved the format of slides-on-the-left, notes-on-the-right and I wanted to try it out.
The other visual format is the presentation. I remember the first time I saw Prezi. I was impressed. It was just a vector image combined with panning/zooming and scrolling. Then I found out about RaphaelJS. Prezi is to RaphaelJS as WordPress is to HTML. The first is easy to use, but the second is only limited by imagination. With some programming skills, RaphaelJS lets me code whatever is in my head.
Comment by Michael J. Swart — March 18, 2014 @ 8:53 am
[…] A Primer on Locks, Blocks and Deadlocks, Michael J. Swart […]
Pingback by Lock Block Deadlock Primer - The Daily Six Pack: March 19, 2014 — March 19, 2014 @ 12:01 am
[…] A Primer on Locks, Blocks and Deadlocks – Michael J. Swart (Blog|Twitter) […]
Pingback by (SFTW) SQL Server Links 21/03/14 • John Sansom — March 21, 2014 @ 8:17 am