Deadlocks. Everything you ever wanted to ask but were too shy.
It's not a secret that a deadlock - it's not very good. Definition of deadlock is very simple and quite clear: This is an exceptional situation when two concurrent queries request same resources but in a different order.
Classic deadlock can occur when two concurrent transactions are modifying data from the two tables in a different order. Unfortunately, in real life deadlocks often are more complex and unobvious. One of the rules, which I always keep in mind, sounds: "You can not design a database, in which the occurrence of deadlock is impossible". And we should deal with them. The algorithm is simple:
1. Catch the deadlock
2. Find the root cause
3. Design and implement a solution.
In practice, these steps can be tricky and may require different types of analysis.
In this session, we will look at ten various deadlocks scenarios and find the root cause of the deadlocks. Then, you will see how it will be easy to get the solutions for the deadlock issue if we did the second step.
SQL basics. Query plan basics. Locks basics. SQL Trace of EE experience.