Deadlocks. We all know a lot about them, right? A sample scenario is simple - one process (A) deadlocks with another one (B) when they acquire locks in a different order. A waits on B and B waits on A. One of them has to be killed to resolve the situation. Is that all? Not really. If you work with SQL Server you might have experienced a deadlock when one of the processes was executing a SELECT query and nothing else. It is surprising at the first glance, but actually it makes sense. Watch my video explanation.
If you are more interested in deadlocks, you might want to know how to check deadlock details in SQL Server.