SQL Server Analysis and Avoiding Deadlocks Interview Questions and Answers

FAQ: SQL server analysis and avoiding deadlocks

1. What is the relationship between SAP work processes and SQL server processes?

Each work process is connected to the MS SQL server database via the database interface. The system uses a series of connections to transfer the data. The SQL server designates a unique number (SPID) to every connection. You can go to the SAP database monitor to see the assignment that links the SPID and the SAP work process:

ST04 - > Detail Analysis Menu -> SQL Processes

and looking in the columns 'SPID', 'Application Server', 'Application program'.

2. What lock types are used with the SQL server?

Where several users are simultaneously accessing data in the database, the MS SQL server uses various types of lock to coordinate this process. The most important of these are Shared Locks (S), Update Locks (U) and Exclusive Locks (X).

• • Shared locks are used for read operations (SELECT) and mean that no changes can be made to the locked object. You may have many shared locks on the same object at the same time. However, no shared locks are used for read accesses in the SAP environment (dirty read).

• • Exclusive locks are used before changing operations (INSERT, UPDATE, DELETE) are executed and they prevent further accesses of any kind to the locked object. No other lock can be held at the same time as an exclusive lock.

• • When executing a SELECT FOR UPDATE, the system initially uses an update lock which, after the SELECT or before the UPDATE, is converted into an exclusive lock. An update lock also allows shared locks to be held in parallel, but does not allow any further update locks or exclusive locks.

3. How long is a database lock retained?

Database locks are requested for objects and are retained until the end of the relevant database transaction, which can also include several statements. At a COMMIT or a ROLLBACK, the locked objects are then released again.

4. What is the granularity of locks?

As of SQL Server Release 7.0, locks are retained for each table row (row level locking). If you need to lock very large parts of a table, the SQL server can also lock table pages (page locks), or escalate the locks to lock the entire table (table lock).

5. What are exclusive lockwaits? How are deadlocks created?

If a lock that you requested cannot be granted because a different database transaction already has an exclusive lock on the object, the process is blocked (blocking lock) until the lock is released again at the end of the database transaction. If a blocked database transaction itself has a blocking lock, this sets off a chain of lock situations. Such blocking lock situations reduce the maximum degree of parallelism and therefore generally impair the system performance as well, since there is a delay in executing database transactions and the application therefore waits. In addition, blocking locks are the first stage of a deadlock situation. As opposed to a deadlock, however, an exclusive lock is resolved by itself.
If the blocked database transaction is executed by an SAP work process on the database, this work process is also blocked and is not available to process other SAP programs or SAP transactions. See Note 806342 for detailed information.

6. How does a deadlock occur?

A deadlock situation occurs if a database transaction is blocked when it requests a database lock but it already has another lock. This other lock, in turn, (either directly or indirectly) blocks the database transaction that has the requested lock. This leads to a cyclical wait situation. Only two database transactions are involved in the vast majority of deadlocks.
The example works on the assumption that the database transactions are executed by SAP work processes. In work processes 1 and 2, two update operations should be executed in each case, more specifically on rows A and B, but with the sequence swapped around (so statement 1 and 4 on row A and statement 2 and 3 on row B). First, a database row from each transaction (first and second update) is locked, since you need to execute a changing operation. However, since no commit or rollback is executed, the locks remain. You are already unable to execute statement 3, since there is already a blocking lock on database row B. The deadlock situation now occurs if you want to lock row A from work process 2 in statement 4.

7. What happens during a deadlock?

The SQL Server automatically recognizes a deadlock and resolves it by canceling one of the database transactions and carrying out a rollback. This database transaction is called the 'victim' of the deadlock. The SQL Server sends database error 1205 to the application. You can repeat the database transaction at a later stage. The 'winner' of a deadlock situation is the database transaction that can be executed after the deadlock is resolved.

8. What is the effect of a deadlock in the SAP system?

The database interface receives database error 1205 for the victim of the deadlock. The SAP system reacts by canceling and rolling back the affected SAP Logical Unit of Work (LUW). The user generally receives a short dump 'DBIF_RSQL_SQL_ERROR' with the text 'SQL error 1205 occurred when accessing table "X" '. In addition to the short dump, the error is logged in the system log and in the developer trace file. The user must decide whether to repeat the transaction or whether the short dump occurred at a non-critical point.
An exception to this is if the victim is an SAP work process. In this case, the SAP system repeats the UPDATE several times, and only considers the transaction as failed once the database error has occurred repeatedly.
The winners of the deadlock can hold the requested lock that is now free. Apart from a short delay, you will not notice the deadlock situation. The SQL server decides which database transaction is the victim.
Deadlocks for the victims in the SAP system have different effects depending on the work process category:

• • D = Dialog: ABAP Report ends with a short dump (database error 1205). Transaction must be restarted.

• • B = Batch: Whether it can be restarted depends on the relevant program.

• • S = Spool: Spool request terminates. The spool request stops in the spool list and can be manually edited later.

• • U = Update: Since update requests are stored in full in the update tables, update processing enables you to automatically restart the update request. This is controlled by the rdisp/update_max_attempt_no SAP profile parameter, which specifies how often (when processing the same update request during a repeated deadlock) an automatic repetition occurs before finally being cancelled. During the automatic rollback attempts, the SAP enqueue locks are also retained, meaning that no inconsistencies can arise. If a final update termination occurs, the update program terminates with a short dump and the update request is included in the list of terminated updates.

9. What is meant by the deadlock priority of a connection?

It is possible to assign a deadlock priority to a database connection. This specifies whether a database transaction that is running on the corresponding connection should be a preferred deadlock victim. If a deadlock occurs, the SQL server then selects as a victim the connection that is marked as a preferred deadlock victim under the connections involved.
In the SAP system, every work process category can be preset as a preferred deadlock victim. This can be set by the dbs/mss/deadlock_priority SAP profile parameter (as of Kernel 6.XX. In 4.X kernels the parameter is called dbs/oledb/deadlock_priority, and in the 3.1I kernel it is called rsdb/mssql/deadlock_priority). The parameter contains a list of the work process type standard code letters. By default, the parameter is not set. Another useful setting for the parameter is the value U, which means that update work processes are preferred deadlock victims.

10. How do I recognize deadlocks in the SAP system?

A deadlock leaves traces at different points in the SAP system and in the database server:

• • SAP Monitor for deadlocks
    This monitor provides an overview of how often deadlocks occur in the system, and of the tables and programs that are affected.
    If a database transaction is cancelled due to a deadlock, the system always issues error message 1205. If the cancelled database transaction was started by an SAP work process, this error message leaves an entry in an SAP database monitor table. To see this, call the database transaction (ST04 -> Detail Analysis Menu -> Deadlock -> Selection screen). You do not need to make any entries in the selection screen. Simply select 'Execute' without making any changes. You only have to choose between 'Single statistics' and 'Count statistics' in the lower section of the screen.
    'Single statistics' displays a list of the deadlocks from the last 7 days. The system displays the time of the deadlock, the program concerned, the table, and the SQL statement (stored procedure). A data record is written for each deadlock, which is stored for 7 days. Note 75158 explains how to change this period.
    In 'Count Statistics', the SQL statement aggregates and displays the deadlocks. These aggregated statistics have been run since the SAP system was installed. For each deadlock type, the statistics list how often it has occurred, and the time it last occurred.

• • SQL Server Lock statistics (ST04 -> Detail Analysis Menu -> F5 'Server Detail')
    The SQL Server counts the number of deadlocks since the SQL Server Start, and for each object type (table, page, row or key) in the performance counters. This is the easiest way of checking whether deadlocks occur in the system at all. These values are displayed in the SAP database monitor in ST04 -> Detail Analysis Menu -> F5 (or up to 6.20, 'Server Detail') -> Lock Details.

• • SQL Server error log (ST04 -> Detail analysis menu -> Error logs)
    If the MS SQL Server is started with the 1204 and 3605 trace flags, a deadlock situation that occurs is logged in the database error log. This happens irrespective of the application whose database transaction is terminated.
    SQL Server 2005 also contains trace flag 1222, which writes further information to the error log (the same information as the deadlock graph in the profiler trace, including the names of the affected tables).
    The error log entry for trace flag 1204 begins with
       DEADLOCK DETECTED
    The information consists of a Deadlock Graph. It counts all 'nodes' that caused a deadlock (generally two). A node corresponds to a lock resource, for example, a table row. For each node, the system logs the participating processes (SPIDs), the resource, the type of granted and waiting lock, and the executing command. In this way, you can generally determine the database, the table name, an executed stored procedure or similar details. The victim is specified at the end of the deadlock graph.  All of the other SPIDs are the winners.
    Therefore, the database error log is the most reliable source of information about deadlocks. It is the only location where you can find information about deadlock winners. Up to and including SAP Basis Release 6.20, the following applies (not as of SAP Basis 6.40, Support Package 2, see Note 709262): If an SAP work process becomes the victim of a deadlock, the SAP kernel automatically sets the 1204 and 3605 trace flags. For all other deadlocks, the system writes the detailed information to the error log.
    As of SAP Basis Release 6.40, you should configure the trace flags as startup parameters of the MS SQL server. Note 32129 explains how to set the trace flag.
    The system displays the active trace flags in the initial screen of ST04 (however, these are not always current).
    The following is a sample extract from a deadlock graph from SQL Server 2000:
    
    Node:2
    KEY: 7:69588032:1 (6e0047a24e6d) CleanCnt:1 Mode: X Flags: 0x0
    Grant List::
    Owner:0x2940d1e0 Mode: X        Flg:0x0 Ref:1 Life:02000000 SPID:343 ECID:0
    SPID: 343 ECID: 0 Statement Type: UPDATE Line #: 2
    Input Buf: RPC Event: Y8R20000001A8VG1153TSP02;1
    Requested By:
    ResType: LockOwner Stype:'OR' Mode: S SPID:1026 ECID:0 Ec:(0xd8ac548) Value:0x62df5cc0 Cost:(0/0)
    
    KEY provides information about the locked resource on which SPID 343 holds an exclusive lock ('Mode: X') for an UPDATE statement (the Y8R20000001A8VG1153TSP02 stored procedure is executed. You can use sp_helptext to determine the relevant statement). SPID 1026 is waiting for a shared lock on the same resource.
    To assign the SPID of the winner to an SAP work process, you must go to ST04 -> Detail analysis menu -> SQL Processes to determine the application server that belongs to the SPID and the host PID. In the process overview of this server (double click in SM51), you can assign the host PID to a work process. (The prerequisite for this is that this SPID has not been interrupted since the deadlock.)

• • Short dump analysis (ST22)
    If an SAP work process terminated a database transaction, the system writes an ABAP short dump with the DBIF_RSQL_SQL_ERROR error. You can use transaction ST22 to view and analyze this short dump. In this transaction, there is a very detailed description of the deadlock situation with all parameters that are required for an in-depth analysis. In particular, you can find the terminated ABAP program and the affected program lines. This also reveals the table on which the deadlock occurred.

• • System Log (SM21)
    Transaction SM21 displays the SAP system log. The system logs a deadlock here as an error. The system specifies the SAP work process that is affected. The entry Deadlock occurred includes the text of the database error.
    
    15:47:13 SPO 35 000 SAPSYS  BYO Deadlock occurred
    15:47:14 SPO 35 000 SAPSYS   BY4 Database error 1205 at SEL access to table TSP02
    15:47:14 SPO 35 000 SAPSYS   BY0 > Transaction (Process ID 1026) was deadlocked on lock
    15:47:14 SPO 35 000 SAPSYS   BY0 > resources with another process and has been chosen as the
    15:47:14 SPO 35 000 SAPSYS   BY0 > deadlock victim. Rerun the transaction.
    15:47:14 SPO 35 000 SAPSYS   R68 Perform rollback

• • Developer trace file of the work process (ST11 or SM50)
    The work process that becomes the victim of a deadlock notes this database error in the developer trace file and includes information about the command that was executed. The affected work process is in the system log. This is an example of an entry:
    
    B  ***LOG BY4=> sql error 1205   performing SEL on table TSP02     [dbtran  6980 ]
    B  ***LOG BY0=> Transaction (Process ID 1026) was deadlocked on lock resources with another process and has been chosen as the deadl
    
    The 'Process ID' corresponds to the SQL server SPID that is also specified in the error log.

11. How do I monitor deadlocks with the SQL Server profiler?

The profiler from SQL Server 2005 allows you to save the deadlock graph as an XML file. As well as the information that is also in the SQL Server error log (see above), this graph contains a lot of details (that are important for the analysis) about the processes and resources that are involved (for example, the isolation level of the processes and the names of the tables that are involved). Note 32129 describes how to set up this type of monitoring in an SAP system.
The section "Analyzing Deadlocks with SQL Server Profiler" in SQL Server Books Online ( ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/72d6718f-501b-4ea6-b344-c0e653f19561.htm ) describes in detail how to create such a profiler trace. In the category 'Locks', you should select the 'Deadlock Graph', 'Deadlock Chain' and 'Deadlock' events. If you select 'Deadlock Graph', an additional tab ('Events Extraction Settings') appears, on which you can choose to save the graph as an XML file. However, after you create the trace in the profiler, you can still create such an XML file. To do this, you select a line with a 'Deadlock Graph' event and hit the right mouse button.
The easiest way to analyze the information in the 'Deadlock Graph' is in the visual display of the profiler itself. However, you can also use the XML file to transfer the information.
In SQL Server 2000, you cannot record the 'Deadlock Graph' in the profiler.

12. How can I avoid deadlocks?

Basically, the application must ensure that it locks the database tables as little as possible, and only in a standard sequence, so as to avoid deadlocks. Note 565710 describes some known cases and offers corrections.
In rare cases, deadlocks are caused not only by the application; the architectural details of the SQL server are also significant. Such deadlocks occur only when you process particularly extensive SQL queries, perform large-scale parallel processing (also parallel execution plans), or implement (parallel) mass changes. In the error log entry for the deadlock, you usually recognize this situation by the fact that the resource on which the deadlock occurs is not a table row (as is usually the case), but instead an internal SQL Server resource that is required while processing the SQL query.

13. How can the index design prevent deadlocks?

The locked resources on which a deadlock occurs may be index lines. In specific cases, changes to the index design may prevent deadlocks (Note 565710 describes some of these cases).

• • Clustered Index: The occurrence of deadlocks may be influenced by which table index is created as a clustered index.

• • Covering Index: Creating an additional index, especially a covering index (that contains all fields for answering an SQL request), may prevent deadlocks.

14. How can I use SQL server settings to prevent deadlocks?

The SQL server cannot prevent most deadlocks. Instead, you must use the application to prevent them. The following exceptions may occur:

• • If several rows of a table are locked simultaneously, the SQL server may decide to set locks on page level instead of row level. You can identify this in the error log by the fact that the system has specified 'PAG' instead of 'KEY' or 'RID' as the lock resource.
    In this type of case, you can configure the table in such a way that the SQL server cannot use locks on pages and must instead use row level locks.
    There are no known examples of this.

• • The SQL Server may execute costly SQL statements on several CPUs in parallel. This may lead to deadlocks on SQL Server internal resources. The Deadlock Graph contains expressions such as 'Exchange Wait Type'. By setting the
    max degree of parallelism = 1
    SQL Server parameter, the system may suppress parallel execution plans and therefore suppress this type of deadlock. (See also Microsoft Knowledge Base Article 837983).

15. How can a deadlock occur that is not triggered by the SQL server?

The SQL server recognizes and removes deadlocks at which SPIDs (SQL server processes) block. In principle, however, lock situations may also occur at a higher application level, in other words between two database applications. This is possible if database applications open several connections to SQL servers and hold, or request, various locks on them. A deadlock could then arise between two of these applications. If the application does not react to this type of lock itself, but instead waits indefinitely, you can only resolve the situation by manually terminating one of the programs. The SQL server cannot recognize or cannot react to this kind of situation since the application program should deal with this.
In the ABAP, you can open additional database connections. Therefore, in principle, this means it is possible to cause the deadlock situation described above.
As long as the database interface opens several connections to the SQL server by default, the system ensures in the interface that deadlocks do not occur between these connections.