| Home |
Download link: deadlock.zip (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
This is an (abbreviated) sample of the information shown in the errorlog when a deadlock has been detected: 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.
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: deadlock.zip (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.
Disclaimer. |