Learning SQL server part.1 — lock and concurrency in SQL server

Copy right@A Layman

Even though I used the SQL server for a long time, I didn’t know it very well. However, I received an alert with the message ‘Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim’ from PagerDuty on one day.

So I tried to figure out what happened on the SQL server and study the knowledge.

The story starts from here

• The message is ‘Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim’.
• Then I traced the log from Management->Extend Events->Sessions-> system health.

Copy right@A Layman

• There is a deadlock diagram. What happened? What is “deadlock”? How did it cause?

Copy right@A Layman

To answer these questions, we have to acquire some basic knowledge. So here are the concepts I wrote on this topic.

• Isolation levels