SYBASE - Deadlock Id 1 detected. 1 deadlock chain(s) involved

Symptom

You found below message in the server error log.

Deadlock Id 1 detected. 1 deadlock chain(s) involved.
Deadlock Id 1: Process (Familyid 0, Spid 40, Suid 6) was executing a UPDATE command at line 1.
Deadlock Id 1: Process 40 was involved in application ‘R3D002 01 comm rd ODBC’.
Deadlock Id 1: Process 40 was involved on host name ‘HOSTNAME’.
Deadlock Id 1: Process 40 was involved in transaction ‘$chained_transaction’.
Executing procedure: *sq1277569622_1429614395ss*
SQL Text: UPDATE “TGN_WLIST” SET “TIMESTAMP” = ? WHERE “REPOSOBJ” = ? AND “APPLICATION” = ? /* R3:CL_GN_WORK_ITEM_FAB===========CP:697 T:TGN_WLIST M:001 */
Deadlock Id 1: Process (Familyid 0, Spid 29, Suid 6) was executing a UPDATE command at line 1.
Deadlock Id 1: Process 29 was involved in application ‘R3B011 01 comm rd ODBC’.
Deadlock Id 1: Process 29 was involved on host name ‘HOSTNAME’.
Deadlock Id 1: Process 29 was involved in transaction ‘$chained_transaction’.
Executing procedure: *sq0422614581_0807480910ss*
SQL Text: UPDATE “TGN_WLIST” SET “PROCESS_ID” = ? WHERE “REPOSOBJ” = ? AND “GENGROUP” = ? AND “GENERATOR” = ? AND “APPLICATION” = ? /* R3:CL_GN_WORK_ITEM_FAB===========CP:1787 T:TGN_WLIST M:001 */
Deadlock Id 1: Process (Familyid 0, Spid 29) was waiting for a ‘exclusive row’ lock on row 121 page 2418793 of the ‘TGN_WLIST’ table in database ‘SID’ but process (Familyid 0, Spid 40) already held a ‘exclusive row’ lock on it.
Deadlock Id 1: Process (Familyid 0, Spid 40) was waiting for a ‘exclusive row’ lock on row 105 page 2418793 of the ‘TGN_WLIST’ table in database ‘SID’ but process (Familyid 0, Spid 29) already held a ‘exclusive row’ lock on it.
Deadlock Id 1: Process (Familyid 0, Spid 29) was chosen as the victim.
Victim process host = `HOSTNAME’, user = `SAPSR3′ program name = `R3B011 01 comm rd ODBC’ host processes = `3448′ .
End of deadlock information.

Solution

1. In our case, the affected table is TGN_WLIST which is taken from the following line,

Deadlock Id 1: Process (Familyid 0, Spid 29) was waiting for a ‘exclusive row’ lock on row 121 page 2418793 of the ‘TGN_WLIST’ table in database ‘SID’ but process (Familyid 0, Spid 40) already held a ‘exclusive row’ lock on it.

2. To get rid this, execute following command,