When conducting MS SQL Server Trainings I often resort to weird examples to explain how certain things work. Over the years I have some favorites that I use over and over. The most disgusting by far would be the one I use to explain Locking, blocking and deadlocks. There are some definite advantages to doing it this way because it helps keep the training interesting and more often than not the participants remember the example even if they don’t remember exactly what I said. In addition to this the example then provides a frame of reference which the participants use to ask more questions. So here is my high level explanation of what the common terms in MS SQL Server mean.
Imagine you’re at a party, it’s late in the day and you have had quite a few beers. As a side effect to the beer “Nature SHOUTS”.
| You tell your partner that you’ll be right back | You write an entry into the T log |
| You ask other guests(non-clustered) or the host (clustered) where the bathroom is | You look up allocation structures and perform IO to take you the destination |
| You reach the bath room but there is already someone in it | Your transaction is being blocked by another user who has locked the bathroom |
| Your wait | Your transaction is waiting and adding rows to sys.dm_os_wait_stats |
| Your partner looks at their watch wondering what’s taking so long | Your wait time is being tracked in queues and monitored |
| The person in the bathroom comes out and you enter and lock the room | You acquired a lock on the resource. |
| Case 1 – Timeout | |
| You pass out, After waiting a long enough time your partner lets the host know you’re stuck and the host uses a master key to break into the room and gets you out. Your partner tells everybody your back. | When timeout is reached SQL decides to kill the transaction and rollback transaction. |
| Case 2 – Normal transaction | |
| You perform you transaction and everything is fine. You do your business and exit the bathroom and get back to you partner in the shortest time possible. | Everything is back to normal , ideally the best case when working with SQL |
| Case 3 – Optimistic concurrency | |
| You only want to Take a Leak so rather than lock the entire bathroom you decide to leave the bathroom door unlocked and simply use the urinal. During this time another person walks in to use the stall. You both ignore each other but get your jobs done. While you could also have used the stall to take a leak you decide to do it elsewhere thus freeing up the stall for those who need it the most. | You enabled optimistic concurrency there by allowing you to read while the other transactions can still perform a write. |
| Case 4 – Deadlock | |
| You decide to use the stall and just as you grabbed the handle another person grabs hold of the toilet paper. Now neither of you can do your business until the other releases the lock they have acquired. After waiting a sufficient amount of time your partner calls the host and he kicks one of you out of the bathroom. | The transaction has entered a deadlock. The SQLOS deadlock monitor kicks in and start the process of choosing a deadlock victim. |
| Case 5- Latch | |
| It’s time for dinner and there is a buffet. You decide to eat dinner (start a transaction). You grab a plate and some food and sit down at a table with your partner. You finish your plate and still feel hungry so decide to take a second pass at the buffet. As you get up you tell your partner to save your seat (latch) as you perform synchronous IO till the buffet table. | Latch is acquired to ensure that internal objects remain consistent during the transaction. Think of it like a lock within a lock. |
| Case 6- NUMA | |
| The host and his wife and kids have their own bathrooms which are generally off limits to guest. Whenever they want to do a transaction they prefer to do it in their respective bathrooms | Dedicated CPU to RAM to I/O access where the CPU is the host and his kids |
| Case 7- Parallelism | |
| There is something wrong with the food and now a lot of people are making a beeline to the guest bathroom. Slowly the queue become unmanageable and the host decides to let guests use the master bathroom too to ease the load and get more transaction complete sooner. Now you go to one bathroom while your partner goes to another. | SQL Server decides that the transaction is too heavy to be performed by just one CPU and creates a new plan which utilizes two CPU instead. |
| Case 8- Lazy Writer | |
| The buffet is in full swing and there is a shortage of chairs at the dinner table. After a while the host asks guests who have finished dinner and just lounging in the dinner area to vacate the area so that others may sit. | When SQL server detects memory pressure it calls lazy writer to start moving unwanted pages to disk just freeing up RAM for other processes. |
| Case 9- Yielding | |
| The community starts getting upset by the loud music and asks the host to turn down the volume. Since the Host still has to reside in the community even after the party is over he obliges and turns the volume down. | The Operating system is suffering from shortage of resources and thus as asks SQL to free up resources. Since SQL cannot run without the OS running properly it releases / yields resources to the OS without questions. Since the OS and SQL share API (they act like best friends) and do not deny a reasonable request from the other. |
| Case 10- Recovery | |
| The party is over, you pass out long before then and wake up in the morning with a strong hangover and can’t remember the stuff you did. You ask your partner what happened and they fill you in. | Server suffered an unexpected shutdown and is now in recovery with the T-Log performing analysis, roll forward and roll back. |
Please Consider Subscribing
