Deadlocks occur when two processes both hold locks on resources, and also try to take locks on resources from the other process. This results in neither process being able to continue.
SQL Server resolves these situations automatically by terminating one of the processes, thus allowing the other(s) to proceed. This is done by an internal process called the Deadlock Monitor.
In this video demo, I demonstrate a basic sequence of events needed to create a deadlock. I also look at the locks that are held and requested by each process, and introduce you to reading a deadlock graph (which is what’s captured from the Extended Events deadlock capturing system I presented last week).
(Sorry my cursor isn’t visible in the video — I blame the capture program I used. It won’t happen again.)
If you want to reproduce the situation yourself, you can download the scripts here.
Voluntary DBA 