Hello Dear Reader! My lastest blog on our Azure FastTrack blog for Microsoft is live, Monitoring Deadlocks in Azure SQL Managed Instance.
Here's a little from the blog:
To paraphrase Annie, Deadlocks ARE JUST AWFUL! When they occur, it means one transaction was the victim and rolled back, the other succeeded. You have a couple different ways to monitor them in Azure SQL Managed Instance. Let's review those together.
What is a deadlock? To quote our MS Learn Documentation, "Deadlocks occur when two or more processes or transactions block each other from continuing because each has locked a database resource that the other transaction needs".
In layman's terms a deadlock occurs when two queries are blocking one another and neither could ever complete its transaction. Pretend two people need to use the same door. They cannot fit through at the same time. One must stop and let the other proceeded first. Now add on top of that they both reached for the door knob and grabbed it at the same time, both pulling the door in an opposite direction.
Instead of simply waiting, one must let go of the door knob. In this case the door knob is a table, the hands reaching out are attempting to gain a lock. In SQL Server, all versions of the database engine, this is accomplished by the Lock Manager it uses a process called FIFO, first in first out, to determine who has to let go.
In the case of SQL one transaction is a deadlock victim, it is killed and the transaction is rolled back. So if this is occurring on your system fixing it is very important. First you have to find out if they are occurring, and that's what we will cover in this blog post today!
To read the rest here is the link: Monitoring Deadlocks in Azure SQL Managed Instance.
As always Dear Reader, Thank You for stopping by.
Thanks,
Brad