Printing deadlock information to the error log

Adaptive Server detects server-side deadlocks to the application and reports them in the server’s error log. The error message sent to the application is error 1205.

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

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

However, setting print deadlock information to 1 can degrade Adaptive Server performance. For this reason, use it only to determine the cause of deadlocks.

Disabling print deadlock information (setting it to 0) means that Adaptive Server does not send any information about deadlocks to the error log.

The deadlock messages contain detailed information, including:

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. SQL Text select * from authors where au_id like '172%'
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.