This is the 23rd day of my participation in the August More Text Challenge
How to Resolve Deadlocks in SQL Server I read this article when I was learning about deadlocks.
For deadlocks, DATABASE management systems such as SQL Server have their own handling mechanisms. However, in practice, after deadlocks are solved by the system, how to confirm the occurrence of deadlocks and how to deal with the follow-up (deadlocks do not occur again) should be paid attention to.
In the original article, you see that you can get the deadlocks that have occurred from the system_Health session, so that you can analyze the problems that have occurred. Therefore, it is specially translated and recorded. Include your own little instructions in the middle.
introduce
In this article, we’ll discuss deadlocks in SQL Server, then we’ll examine a real-life deadlock scenario and explore troubleshooting steps.
Generally, we can find various theoretical suggestions and examples of deadlock problems on the Web, but in this article, we will deal with a real-life deadlock story and have the opportunity to work with a problem-based case and learn the steps to resolve it.
First, let’s explain the concept of deadlocks. Deadlock problems occur when two (or more) operations want to access each other’s resources that have been locked by another operation. In this case, database resources are negatively affected because both processes are constantly waiting for each other. This contention problem is terminated by SQL Server intervention. It selects a victim from a transaction participating in a deadlock and forces it to roll back all operations.
As you can see from this explanation, deadlocks in SQL Server are a special contention problem; In addition, each deadlock has its own unique characteristics, so the solution can vary depending on the characteristics of the problem.
Now, let’s look at the problem scenario.
Problem scenario
In this real-world scenario, the internal application returns an error to the user, who notifys the development team of the error.
The development team realized that this was a deadlock problem, but they couldn’t find the main cause of the problem. In this case, the team decided to accept the consulting services of an experienced database administrator. In the next section, we’ll learn how a database administrator can analyze and resolve this deadlock problem.
A prerequisite for
The development team uses the following table to store order numbers and uses the following query to create the first row of the day.
CREATE TABLE [TestTblCounter](
[Id] [int] IDENTITY(1.1) NOT NULL PRIMARY KEY,
[SerialNumber] [int] NULL,
[LogDate] [datetime] NULL)
GO
IF NOT EXISTS(SELECT Id
FROM TestTblCounter
WHERE LogDate = CONVERT(VARCHAR(100), GETDATE(), 112))
BEGIN
INSERT INTO TestTblCounter
VALUES
('1'.CONVERT(VARCHAR(100), GETDATE(), 112))END
Copy the codeThe data structure of the following table is similar to the following figure;
The following stored procedure is being used to create a new order number.
CREATE PROCEDURE CreateLogNo
AS
DECLARE @LogNo AS VARCHAR(50), @LogCounter AS INT= 0;
BEGIN TRAN;
UPDATE TestTblCounter
SET
SerialNumber = SerialNumber + 1
WHERE LogDate = CONVERT(VARCHAR(100), GETDATE(), 112);
SELECT @LogCounter = SerialNumber
FROM TestTblCounter WITH(TABLOCKX)
WHERE LogDate = CONVERT(VARCHAR(100), GETDATE(), 112);
SELECT @LogCounter AS LogNumber;
COMMIT TRAN
Copy the codeWe will also use the SQLQueryStress tool to generate a production-like workload.
Use the system_health session to monitor deadlocks in SQL Server
Because of the error messages, the database administrator decided to investigate the deadlock problem. The error message clearly indicates a deadlock problem.
As a first step, he decided to check the system_health session used for deadlocks.
Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
System_health is the default extended event session for SQL Server, which is automatically started when the database engine is started.
The system_health session collects various system data, one of which is deadlock information. The following query reads the system_Health session.xel file and provides information about the deadlock problem that occurred.
A system_health session can be a good starting point for finding deadlock issues. The following query will help you find deadlock problems caught by the System_Health session.
DECLARE @xelfilepath NVARCHAR(260)
SELECT @xelfilepath = dosdlc.path
FROM sys.dm_os_server_diagnostics_log_configurations AS dosdlc;
SELECT @xelfilepath = @xelfilepath + N'system_health_*.xel'
DROP TABLE IF EXISTS #TempTable
SELECT CONVERT(XML, event_data) AS EventData
INTO #TempTable FROM sys.fn_xe_file_target_read_file(@xelfilepath.NULL.NULL.NULL)
WHERE object_name = 'xml_deadlock_report'
SELECT EventData.value('(event/@timestamp)[1]'.'datetime2(7)') AS UtcTime,
CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET,
EventData.value('(event/@timestamp)[1]'.'VARCHAR(50)')), DATENAME(TzOffset, SYSDATETIMEOFFSET()))) AS LocalTime,
EventData.query('event/data/value/deadlock') AS XmlDeadlockReport
FROM #TempTable
ORDER BY UtcTime DESC;
Copy the code
Note: Deadlock information is cleared after the transaction restarts, i.e., if a restart occurs, previous deadlock information will not be detected
When we click on any row of the XmlDeadlockReport column, a deadlock report appears.
Use extended events to monitor deadlocks in SQL Server
The database administrator found some clues to deadlock problems with the data captured by the System_Health session. However, he believes that the system_Health session only shows recent events due to file size limitations, so it is not reliable to detect all deadlocks in SQL Server. Therefore, he decided to create a new extended event session that could catch all deadlocks.
Extended Event is a system monitoring tool that helps collect events and system information from SQL Server. With XEvent, we can also capture deadlock information from SQL Server.
First, we will start SQL Server Management Studio and navigate to the Session located in the Management folder with Extended Events. Right-click the Sessions folder and choose New Session.
In the “New Session” screenshot, create a name for the session, and select the Start the Event Session Immediately after the Session Creation check box. Therefore, the session will start after the creation process is complete.
On the Events TAB, we select the Events to capture. For this session, we will select the following events:
- database_xml_deadlock_report
- xml_deadlock_report
- xml_deadlock_report_filtered
We will click the Configure button and select the global event that will be captured with the event:
- Client application name – Client app name
- Client connection ID – Client connection ID
- Client hostname – client hostname
- Database ID – Database ID
- Database name – Database name
- Nt username – nt username
- Username – username
- SQL text – SQL text
On the Data Storage TAB, we will select the Event_FILE type to store the captured Data, and then click the OK button.
The session will be created and then automatically started to catch deadlock events.
Analyze and resolve deadlocks in SQL Server
In this section, we will first simulate deadlock problems and then try to figure out the main causes of deadlock problems.
Start SQLQueryStress with the following parameters and wait for the query execution process to complete.
When we open the details of the exception, it displays the exception message.
To learn more about deadlock issues, we need to look at the extended event session we created earlier to catch deadlock events.
We open a MonitorDeadlock session and right-click the Target node, then select View Target Data. The captured deadlocks are displayed in the right pane.
The xml_deadlllock_report event contains more details about deadlocks, and we can also find the deadlock graph.
When we interpret the deadlock diagram, SPID 65 (victim) has acquired an intent exclusive lock and wants to place the update lock in the TestTblCounter table. SPID 64 has obtained the exclusive lock for TestTblCounter, and since TABLOCKX prompts you to place the exclusive lock on the same table.
A TABLOCKX prompt helps to place an exclusive lock on a table until the selection statement or transaction is complete. The disadvantage of TABLOCKX hint is that it reduces concurrency and therefore increases lock time. When we decide to use it, we need to consider locking and contention issues. Especially for this scenario, the logic of this prompt is not appropriate. When we reconsidered the query, the UPDATE statement modified some rows, and then the SELECT statement took the same modified rows, but due to TABLOCKX prompts, it placed an exclusive lock on the entire table until it was complete. The most meaningless part of this query is the row where values are assigned to variables, because the data assignment to variables is performed randomly.
TABLOCKX hint places an exclusive lock on the table!
If you want to get the last updated or inserted row, why do you need to set exclusive locks on all rows of the table? Therefore, we can remove TABLOCKX hints that cause query deadlocks. Also, you can use the OUTPUT clause to get the value of the last inserted or updated row.
ALTER PROCEDURE [dbo].[CreateLogNo]
AS
DECLARE @LogNo AS VARCHAR(50), @LogCounter AS INT= 0;
DECLARE @UptTable AS TABLE(SerNumber VARCHAR(50));
BEGIN TRAN;
IF NOT EXISTS(SELECT ID
FROM TestTblCounter
WHERE LogDate = CONVERT(VARCHAR(100), GETDATE(), 112))
BEGIN
INSERT INTO TestTblCounter
OUTPUT INSERTED.SerialNumber INTO @UptTable
VALUES
(1.CONVERT(VARCHAR(100), GETDATE(), 112));END;
ELSE
BEGIN
UPDATE TestTblCounter
SET
SerialNumber = SerialNumber + 1
OUTPUT INSERTED.SerialNumber INTO @UptTable
WHERE LogDate = CONVERT(VARCHAR(100), GETDATE(), 112);
END
SELECT @LogCounter = SerNumber FROM @UptTable ORDER BY SerNumber DESC
SELECT @LogCounter AS LogNumber;
COMMIT TRAN
Copy the codeWhen we simulate a new workload with 200 concurrent users for the changed stored procedure with SQLQueryStress, we will not encounter any deadlock issues.
conclusion
In this article, we explained deadlocks in SQL Server and then analyzed a real story that a development team experienced. An important part of generating a solution is to properly understand and interpret deadlock reports and graphs. Otherwise, it will be difficult to find the main cause of the problem.
Review resolution steps:
- Check the system_health session for deadlocks
- Create an extended event session to catch deadlocks
- Analyze deadlock reports and charts to find problems
- Whether queries involved in deadlocks can be improved or changed