Server-side deadlocks are detected and reported to the application by Adaptive Server and in the server’s error log. The error message sent to the application is error 1205.
The message sent to the error log, by default, merely identifies that a deadlock occurred. The numbering in the message indicates the number of deadlocks since the last boot of the server.
03:00000:00029:1999/03/15 13:16:38.19 server Deadlock Id 11 detected
In this output, fid 0, spid 29 started the deadlock detection check, so its fid and spid values are used as the second and third values in the deadlock message. (The first value, 03, is the engine number.)
To get more information about the tasks that deadlock, set the print deadlock information configuration parameter to 1. This setting sends more detailed deadlock messages to the log and to the terminal session where the server started.
However, setting print deadlock information to 1 can degrade Adaptive Server performance. For this reason, you should use it only when you are trying to determine the cause of deadlocks.
The deadlock messages contain detailed information, including:
The family and server-process IDs of the tasks involved
The commands and tables involved in deadlocks; if a stored procedure was involved, the procedure name is shown
The type of locks each task held, and the type of lock each task was trying to acquire
The server login IDs (suid values)
In the following report, spid 29 is deadlocked with a parallel task, fid 94, spid 38. The deadlock involves exclusive versus shared lock requests on the authors table. spid 29 is chosen as the deadlock victim:
Deadlock Id 11: detected. 1 deadlock chain(s) involved. Deadlock Id 11: Process (Familyid 94, 38) (suid 62) was executing a SELECT command at line 1. Deadlock Id 11: Process (Familyid 29, 29) (suid 56) was executing a INSERT command at line 1. SQL Text: insert authors (au_id, au_fname, au_lname) values (’A999999816’, ’Bill’, ’Dewart’) Deadlock Id 11: Process (Familyid 0, Spid 29) was waiting for a ’exclusive page’ lock on page 1155 of the ’authors’ table in database 8 but process (Familyid 94, Spid 38) already held a ’shared page’ lock on it. Deadlock Id 11: Process (Familyid 94, Spid 38) was waiting for a ’shared page’ lock on page 2336 of the ’authors’ table in database 8 but process (Familyid 29, Spid 29) already held a ’exclusive page’ lock on it. Deadlock Id 11: Process (Familyid 0, 29) was chosen as the victim. End of deadlock information.
