Deadlock

Deadlock occurs when you are inserting or updating a table and the lock is not released for some specific amount of time. To avoid this either use row level lock by creating lock objects, commit work to release the lock.

Locking Protocols
By using logical locks, you can lock single rows or generic areas of database tables. There are write locks and read locks. For one object you can set a maximum of one write lock, but multiple read locks. Read and write locks are mutually exclusive. For this reason, read locks are also called shared locks, and write locks are also called exclusive locks.The locking protocol determines which locks are set for which type of access and at which point in time. For locking to work properly, all application components must agree on the same locking protocol and apply it consistently.

Locking protocol 1
Only write locks are set. If you want to change an object in a transaction, the application requests a write lock for this object at the start of the transaction. If the object is already locked, the application cannot run in change mode, but in display mode only. Since no read locks can be set, objects can always be read (as long as the isolation level of the database allows this, which is usually the case – see above). This locking protocol guarantees the highest level of parallelism and scalability.

Locking protocol 2
Read and write locks are set. When an object is read by the database, a read lock is set. If you want to change an object, a write lock is requested.If a write lock cannot be set because a read or write lock has been set, the application has to decide whether to cancel the transaction or to try again later. The write lock can be requested immediately when you change the object, or shortly before the end of the transaction. The advantage of requesting a write lock at an early stage is that you can inform users in time. On the other hand, the advantage of requesting a write lock at a later stage is that the lock is set for a short time only, which enables a higher level of parallelism and scalability. This is due to the fact that by setting a write lock, you prevent the object from being read in another transaction. If you request a write lock at a later stage, make sure that you request a read lock when you change the object at the latest. This ensures that the object cannot be changed in another transaction.

All applications that access a particular table have to use the agreed locking protocol.
If two applications try to get locks for multiple objects simultaneously but in a different sequence, deadlocks occur. To avoid deadlocks, an exception is thrown if a lock cannot be set.
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.

Lock Types
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.


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.
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


NOTE : You can check for the OSS notes 631668, 860475 for more info.
A couple having to do with deadlocks are 84348, 631668, 751203