Sybase ASE tip: Investigation of deadlocks reported in the errorlog

Notice: This is an old version of the website, please click here to enter the new website.


About Peter Sap

SQL Syntax tool

Reference Guide


Download link: (8 Kb)

When the configuration option "print deadlock information" has been switched on, an informational
message is put into the Sybase ASE errorlog when a deadlock is detected. The information can be
used to see if the tables involved in a deadlock can benefit from a change of their locking schema.
A technique is described here to investigate the deadlock information and with a tool this task is
automated and statistics can be gathered.

Content Sample deadlock message
This is an (abbreviated) sample of the information shown in the errorlog when a deadlock has been

The highlighted text shows in which database, object and page number the deadlock was encountered.

Sybase ASE locking schemas
Sybase ASE (version 11.9.2 and later) uses three different locking schemas: AllPages, DataPages
and DataRows. Sybase stores rows for a table or index within pages. Depending on the locking
schema, information is locked at the page or at the row level. The following table shows how
each locking schema locks information.

Locking SchemaLocks on IndexLocks on Data
All PagesPagePage
DataPagesNot lockedPage
DataRowsNot lockedRow

The table shows that, for instance, the DataPages locking schema does not lock indexes but data is
locked at the page level. When a single page is locked, all rows on that page are locked with it.
(NB: with the DataPages and DataRows locking schema indexes are locked through latches, a non-transactional
locking method)

Moving from AllPages to DataPages to DataRows
Deadlock information can be used to tune the locking schema for a table. When a table uses the
AllPages schema and deadlocks occur mostly on pages that contain rows for indexes, a move to
DataPages might reduce the change of hitting a deadlock. Also, when deadlocks are frequently
occurring on the data level, a move to the DataRows locking schema is an option.
When a table is already using the DataRows locking schema other techniques must be used to eliminate
deadlocks. Be aware of the fact that deadlocks cannot always be avoided and the application logic
should deal with it by re-submitting the transaction.
Large and busy systems might want to avoid moving all tables to the DataRows locking schema
because of the overhead involved. It is not uncommon to see configurations with "number of locks"
configured to a value of several million and still hitting that limit. This situation can easily
occur through a combination of a high transaction isolation level and the DataRows locking schema.
Try to avoid these situations by tuning the locking schema, the transaction isolation level, lock
promotion thresholds or other settings.
Servers short of physical ram should be aware of the fact that locks consume memory (approx.
140 Mb per 1 million locks).

Index or Data?
The deadlock information in the errorlog doesn't show if the deadlock was related to an index or
to data. However, with the undocumented dbcc page command, this can be determined easily. For
instance, dbcc page(4,652391) will tell what type of page is involved, through the indid.
Below is an example of the dbcc page(4,652391) output:

The output shows that indid has a value of 0, and so this page is used to store data. Remember that
0 is used for data, values > 0 and < 255 are used for indexes and 255 itself is used for
off-row-objects including text and image data.

The deadlock analyzer tool
The output of a single deadlock is by far not enough to make a decision on. The tool provided here
reads through the errorlog, makes the required dbcc page commands and scans the output. At the end
several statistics are shown. The tool is a Unix/Linux shell script, executing statements with isql
and using bcp.
Download link: (8 Kb)

Sample output
Below is a sample output from the analyzer. The first report shows deadlocks occurring at the table
level, the second report shows the total number of deadlock per table at page and or row level and
the last one shows the number of deadlocks per table and indid.

ObjectDeadlocks on table
----------------------------------------------------------- -------------------
(2 rows affected)

ObjectPage/Row Deadlocks per table
----------------------------------------------- ---------- ---------------------
(4 rows affected)

ObjectindidDeadlocks per index
---------------------------------------------- ----------- ---------------------
(5 rows affected)