AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Sql server deadlock event log11/20/2023 This note sets several SQL Server trace flags and configures a SQL Server Profiler Trace. ) to setup a more detailed deadlock tracing for SQL Server. This is only done for the ABAP part of the system (no Java deadlocks are shown here) and it shows only the victims there is no information about the winning processes. The database monitor of the SAP system (Transaction DBACOCKPIT) shows the occurred deadlocks, the table on which the deadlock occurs and the work process information. When a deadlock occurs in a SAP system, we do not get much information in the ST22 dumps or within the SQL Server error log. The transaction on connection ConB can continue. Msg 1205, Level 13, State 51, Line 3 Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. The transaction is ended and rolled back with an error 1205: The SQL Server is able to detect such deadlocks and will resolve it by killing the transaction with the least amount of generated log, the transaction on connection ConA in our case. Connection ConB has an X lock on ‘Schulze’ and has to wait for to lock on ‘Miller’, which is held by ConA. Now we have a deadlock, the request ConA has changed ‘Miller’ and has a lock (X lock) on it, but has to wait on ‘Schulze’, which is blocked by ConB. Once the waiting on connection ConA is over, the request will try to update the row ‘Schulze’, but has to wait, as the request on connection ConB has already changed it and has an exclusive lock on it. It can update the row ‘Schulze’, but has to wait on the lock on row ‘Miller’, as the request on connection ConA has already changed it and has an exclusive lock on it. It will update the row where NAME = ‘Schulze’ and then the row where NAME = ‘Miller’. UPDATE SAPTABLE SET SPRSL = N'F' WHERE NAME = N'Miller' AND SPRSL = N'E' UPDATE SAPTABLE SET SPRSL = N'F' WHERE NAME = N'Schulze' AND SPRSL = N'D' Run both updates at once, starting with Schulze While the transaction on connection ConA is waiting, run this code on connection ConB: This transaction will update column SPRSL, where NAME equals to ‘Miller’, than it will wait for one minute and then it will try to update the row, where NAME = ‘Schulze’ UPDATE SAPTABLE SET SPRSL = N'E' WHERE NAME = N'Schulze' AND SPRSL = N'D' and then will execute the next Update here (now Schulze) We will wait in this connection for 1 minute. Now run the script for the deadlock partner UPDATE SAPTABLE SET SPRSL = N'D' WHERE NAME = N'Miller' AND SPRSL = N'E' Update the first row, starting with Miller INSERT INTO SAPTABLE(,) VALUES (N'Schulze',N'D',N'Deutscher Name.') INSERT INTO SAPTABLE(,) VALUES (N'Miller',N'E',N'English name') (70) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL DEFAULT N' ',ĬONSTRAINT PRIMARY KEY CLUSTEREDĬREATE NONCLUSTERED INDEX ON () ON (1) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL DEFAULT N' ', (40) COLLATE SQL_Latin1_General_CP850_BIN2 NOT NULL DEFAULT N' ', IF EXISTS (SELECT * FROM sys.databases WHERE name = N'DL_Analysis')ĪLTER DATABASE SET RECOVERY SIMPLE On connection ConA prepare a database and a table with some data with the script: Open two connections to the SQL Server (Connection ConA and ConB) You can easily create a deadlock with two nodes on SQL Server by the following procedure: Deadlock detection and cancellation is a database feature to prevent stale locks, which otherwise would never get resolved by their own. But deadlocks are happening in all database systems that are using locks to guarantee the logical integrity of the data. The most common misconception about deadlocks is that they are a problem of the database system - the database should solve them on its own and should not throw any error to the application. A deadlock has to contain at least two nodes, but can contain much more than that the biggest deadlock I saw had 13 nodes. All threads are waiting, for the other thread(s) to release their resources, which they will never do (as they wait for the other resources as well). Unless otherwise stated the provided information and scripts run on SQL Server 2005 or higher.Ī deadlock occurs, when multiple threads are waiting on each other’s resources. The last part takes the excitement to the extreme and will be an introduction and discussion of some in-depth SQL Scripts, which will help us to analyze and understand the deadlocks in more detail. The second part which will be more exciting will cover the basic analysis of deadlocks and the different approaches to solve and to avoid them. In the first part we will cover the basics about deadlocks, why and when they occur and how to capture them. Today I want to start a small blog series for the deadlock analysis with SQL Server.
0 Comments
Read More
Leave a Reply. |