- SAP Managed Tags:
Purpose
Deadlocks can occur in any SQL Server multiuser application. The occurrences of deadlocks are a problem of the application. It is not a SQL Server problem. SQL Server and SAP provide tools to detect, monitor and analyze the deadlocks. But in the end the solution must lie with the application development and process management.
Sometimes the deadlocks are handled properly by the application and sometimes not. Unexpected deadlocks can occur if performance problems cause transactions to hold locks longer than expected, or if wrong query plans are used, or if ad hoc statements interfere with the system. There are many reasons why deadlocks could cause system problems so it's vital to have a good tool to analyze them.
Below you can learn how to use the DBACOCKPIT -> Diagnostics -> Deadlock Monitor action in SAP NetWeaver which is available when SAP NetWeaver is running on SQL Server or a remote DBACOCKPIT connection to SQL Server is set up for monitoring.
Setup
The Deadlock Monitor is always available for the local system. If the DBACOCKPIT is used to monitor a remote SQL Server, some features may not be available.
Using the Deadlock Monitor
When you enter the Deadlock Monitor action for the local system, you will see two tabs:
Deadlocks Reported by ABAP Stack
The first tab is the old deadlock monitor. It is based on the MSSDEADLCK table. This table exists in the SAP NetWeaver dictionary and it is filled by DBSL (the DB interface of SQL Server) when SAP NetWeaver encounters deadlocks. This feature only tracks deadlocks generated by the SAP NetWeaver kernel. If deadlocks were encountered by any other software, they will not appear here.
The SAP NetWeaver system running on the schema to be monitored records the deadlocks in table MSSDEADLCK and it also deletes the records after one month using an SM37 job (report RSMSSDCN).
In this context only two features possible: Table detail and ABAP code. Those commands are available in the ALV toolbar. The table detail button navigates you to the table detail action for the table listed in the selected deadlock line. The table detail button is always enabled. The ABAP code button is only available when monitoring the local system. It opens an ABAP editor for the OPEN SQL statement where the error occurred.
Deadlocks Recorded in X-Events
There are two generations of this screen. the first is a simple list of deadlocks recorded in the SQL Server Systemhealth (syshealth) X-events session. The second is a more sophisticated summary list, with the original detailed list below.
First Release
First we will describe the earlier version:
Deadlock List
This shows that a deadlock has occurred, and it shows the "victim" of the deadlock, i.e. the session that was selected by SQL Server to trigger an error and reset the transaction. It also shows the "survivor" of the deadlock, i.e. the other process which was involved but was not affected by the deadlock. Using the toolbar buttons, you can view the details of the tables involved (owner object and waiter object), examine the ABAP code of either the victim or the survivor, and view the full XML graph in a browser. If SSMS (the SQL Server Management Studio) is installed on the local PC, the deadlock can be opened in SSMS showing a graphical view of the deadlock chain.
When you press the "Owner object detail" button, the DBACOCKPIT action "Single Table Analysis" is invoked. The same action can be called for the "Waiter object detail" which is the object that the process is trying to lock but has to wait because the resource is locked by another process.
New Version
The newer version is called "Deadlocks from X-Events Session". Instead of a simple list, the screen now has three sections:
- Deadlock Summary
- Inputbuffer of the session which encountered the deadlock
- Deadlock Details. This is the same list as the one described in the earlier version.
This new screen has several advantages over the old one. The top summary view is a grouping of the most common deadlocks that have occurred on the system, with the most common at the top. The list is ordered by the victim flag (victims at the top, survivors on the bottom) and the number of occurrences of that particular deadlock.
The reason this list was provided is that with the old simple list it was very difficult in some cases to determine which deadlock occurred most often in order to focus on the most problematic type of deadlock.
The middle section shows the full SQL statement. If the deadlock entry above is a victim of a deadlock, then it is the statement that failed, otherwise it is the statement that was executing in the surviving process when the deadlock occurred.
The bottom section is the same as before, but when you click on the "Details for selected deadlock" button in the summary list, this detail list will only show you the occurrences for that deadlock. This can be reverted by clicking on "All Deadlocks" in the detail list.
The X-Events Session
The older version of the X-Events tab used the SQL Server Systemhealth (syshealth) session. The new version introduces a new separate X-Events session named "SAP_Deadlocks". After the support package of the new version is applied to a system, a new SQL Server "Extended Events" session appears in the SQL Server Management Studio under Management -> Extended Events -> Sessions. This new session is more efficient because it records only deadlock events and nothing else. The data is also kept longer than in the syshealth session, since only deadlocks are recorded.
The old screen sometimes took a long time to read data from the syshealth session because the syshealth session grows very large on busy systems. The new screen should always appear much faster.
Usage Scenarios
The starting point for understanding deadlocks, how and why they occur and how they can be tracked and analyzed is SAP Note 32129. This note refers to other notes which provide detailed information about various aspects of handling deadlocks in SAP systems running on SQL Server. This information can sometimes be overwhelming, so we provide only a brief overview here.
Some reasons for deadlocks (not a complete list):
- Missing index to support a DML statement
This can cause the statement (UPDATE, DELETE) to lock many more rows in the table than are actually modified in the end. Usually there are scans and searches that lock a lot of rows. In this case, use the SQL DML statement WHERE condition to determine whether there is a fitting index. Use the object detail buttons to jump to single table analysis to view the existing indexes. - Application does not serialize execution of critical changes
This allows parallel execution, which can result in deadlocks. It is important to try to understand the application context in which the deadlock occurred. Use the "ABAP code" button to jump to the Open SQL statement and examine the surrounding code. Try to understand how the two processes interfered with each other, why they were running simultaneously (parallel jobs etc.) and how this can be avoided either by scheduling jobs at a different time or with a different data set, or by modifying the code to synchronize access to a critical path. - Long running statements
This can be a SELECT or any other statement. If a statement is slow due to system performance and the transaction remains open for a longer period, the system is at risk of deadlock. This is a common scenario. Long running statements are inefficient and consume a lot of resources and time, but they can cause even worse problems if they occur within a transaction which has already acquired some locks. This drastically increases the likelihood of deadlocks in some cases. So, resolving performance issues (adding indexes, hints, altering the Open SQL statement, etc.) can often magically reduce the occurrences of deadlocks in a process. - Application does not commit often enough
If there is a long running batch job that runs mass modifications within a single transaction, this leads to many open locks. This can then interfere with normal short user transactions that modify the same tables.
To monitor this, SAP has provided a new feature in DBACOCKPIT: Performance -> History -> Open Transaction History. This feature is available in the same support packages as the newer Deadlock Monitor user interface. We will not go into detail on this feature here. This will be covered in a separate blog post. A link will be added here soon.
Open transactions need to be avoided for two reasons. First to avoid holding locks too long, which increases the likelihood of deadlocks, and second because a long transaction consumes excessive log space and may cause the database to run out of log space.
To solve this problem, the application must be analyzed to either increase the frequency of COMMITs or use a smaller data set. - Different access order
In this case, the application modifies the data in more than one process, each of which uses a different access order. Or different indexes are used in different DML statements for the same table. Sometimes there is an ABAP internal table that contains a number of rows, and the content of the table is not consistently ordered the same way when the concurrent processes scan the table and execute modifications (perhaps in multiple tables) for each row.
In some cases, the solution can be as simple as adding a "sort" statement in ABAP to make the access more consistent.
To summarize: The Deadlock Monitor can be used to determine the various reasons for deadlocks and then one or more of the following actions can be taken:
- Add an index to get a better access plan for queries (to shorten a transaction) and/or DML operations (to have a smaller footprint).
- Optimize queries using database hints or update statistics on tables. SQL Server automatically updates statistics on tables, but in rare cases, manual updating of statistics is required.
- Recommend code changes in Z customer code or open an SAP case if the problem occurs in SAP code.
If this is not sufficient and a more detailed analysis is needed, we recommend three blogs by Clas Hortien from Microsoft Germany:
- Deadlock Analysis (basic)
- Deadlock Analysis (advanced)
- Deadlock Analysis (examples)
These blogs are tailored for SAP workload, but mainly describe native SQL Server tools. The blogs can be accessed here.
Release Information
The older version of the deadlock monitor, with the deadlocks recorded in ABAP stack and X-events have been part of DBACOCKPIT for many years. However, the new features (summary list, inputbuffer) were delivered in mid-2021. The SP levels of the new feature are as follows:
SAP NetWeaver 756 SP01
SAP NetWeaver 755 SP03
SAP NetWeaver 754 SP05
SAP NetWeaver 753 SP07
SAP NetWeaver 752 SP10
SAP NetWeaver 751 SP13
SAP NetWeaver 750 SP23
SAP NetWeaver 740 SP27
SAP NetWeaver 731 SP30
The new features are not supported in earlier releases than SAP NetWeaver 7.30 EhP1 (SAP_BASIS 731).
Conclusion
I hope this overview of the Deadlock Monitor, and how to use it to address various types of deadlock issues, proves helpful to you. If you have further questions about the Monitor, or about a deadlock problem you are encountering, I encourage you to ask your questions in our SAP Community using this link.
If you want to read more about SAP on SQL server then I invite you to view our topic area here.
