Requirements: There are many reasons why SQL Server database causes deadlocks, in production, everyone may encounter deadlock problems, and the specific cause may not be located based on the abnormal information of application deadlocks alone, so it is necessary to use extension events in SQL Server to track deadlock problems and record the specific details of when deadlocks occur.
Extended event overview
Extended Events is a lightweight performance monitoring feature that allows users to gather the data they need to monitor and resolve issues. This feature allows you to view details about the internal operations of the data engine for performance monitoring and problem-solving related purposes.
With the Extended Events (XEvents) architecture, users can use as much or as little data as they need to monitor, identify, or troubleshoot the performance of SQL Server, Azure SQL Database, and Azure SQL Managed Instance. Extended events are highly configurable, lightweight, and scalable well.
Reference:
The hyperlink login is visible.
The hyperlink login is visible.
Causes of deadlocks
Deadlocks are caused by competing concurrency locks in a database, usually in multi-step transactions. Each user session may have one or more tasks running on its behalf, where each task may fetch or wait to fetch various resources. The following types of resources can cause blocking and eventually cause a deadlock.
- Locks: Locks waiting to fetch resources such as objects, pages, rows, metadata, and applications can cause deadlocks. For example, transaction T1 has a shared lock (S lock) on row r1 and waits to get an exclusive lock (X lock) for row r2. Transaction T2 has a shared lock (S lock) on row r2 and waits to get an exclusive lock (X lock) for row r1. This results in a lock loop where both T1 and T2 wait for each other to release the locked resource.
- Worker threads: Tasks queuing for available worker threads can cause deadlocks. If a queued task has a resource that blocks all worker threads, it will result in a deadlock. For example, after session S1 starts a transaction and acquires a shared lock (S-lock) for line r1, it goes to sleep. An active session running on all available worker threads is trying to obtain an exclusive lock (X-lock) for line r1. Because session S1 can't get a worker thread, it can't commit the transaction and release the lock on line r1. This will result in a deadlock.
- Memory: A deadlock can occur when a concurrent request waits to gain memory and the currently available memory is not enough for its needs. For example, two concurrent queries (Q1 and Q2) are executed as user-defined functions, acquiring 10 MB and 20 MB of memory, respectively. If each query requires 30 MB and the total available memory is 20 MB, Q1 and Q2 must wait for each other to free memory, which will result in a deadlock.
- Parallel querying and execution-related resources: Processing coordinators, generators, or consumer threads typically associated with switched ports can block each other when they contain at least one process that is not part of a parallel query, resulting in deadlocks. In addition, when a parallel query starts execution, SQL Server determines the degree of parallelism or number of worker threads based on the current workload. A deadlock can occur if there is an unexpected change in the system workload, for example, when a new query starts running in the server or when the system runs out of worker threads.
- Multiple Activity Outcome Set (MARS) resources: These resources are used to control the cross-execution of multiple activity requests under MARS.
Reference:
The hyperlink login is visible.
Deadlock extended event logging
Create an extension event to record the deadlock information with the following command:
Start a deadlock event session
Stop an event session
Delete an event session
Query event session data
Test deadlocks
Create a new Tab1 table for testing, and create two new execution windows, and execute the following commands respectively:
A deadlock occurs as follows:
A transaction (process ID 68) with another process is deadlocked on a locked resource and has been selected as a deadlock victim. Please rerun the transaction.
Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 
View the event session data as shown in the following figure:
The detailed XML data is as follows:
(End)
|
Posted on 3/2/2024 7:13:04 PM
|
|
|
|
Landlord|
Posted on 3/2/2024 7:14:35 PM
|
