Cure vs Prevention
1 Don't believe everything you read, there are no cures for deadlocks. Changing an APL table to DOL or changing a DOL table to row-level locking will not cure the problem, it will just push the problem into the background for a while. Eventually, as the load or volume increases, the exact same deadlocks will reappear in the "new" table.
2 Deadlocks fall into three identifiable (print deadlock info, sp_sysmon, sql text) categories. Deadlocks, these categories and their address predate Sybase and relational dbms:
(a) those that can be prevented
(b) those that cannot be prevented but can be reduced
(c) those that cannot be prevented or reduced.
This last category is internal, some call them "server-induced" but I dislike the term. The point is (c) is a function [small percentage] of (a) and (b), therefore if you prevent (a) and reduce (b), thus making the two categories that you have control over small, the (c) you are left with is insignificant. Some systems simply do not experience deadlock "problems", and others are full of them.
Prevention and reduction is implemented by application code design and standards, not by tweaking the physical table. Within a transaction, always access any group of tables in the same sequence. Where you are accessing a table more than once, always access the rows in the same sequence.
The two most common causes are:
(i) apps that open more than one connection to the server, and do not manage the connections properly, thus one connection deadlocks another connection
(ii) apps that are clueless re (a) and (b), written like they were using the server in single-user mode.
Sybase will keep reacting to customer pressure and keep coming up with features to reduce the granularity of locks, etc. Eg row level locking. As evidenced, this will not fix the problem, it merely delays the pain. prevention at least can be implemented for a known price.
I have two customers who experienced increased deadlocks in production when they moved from 11.9.2 to 12.5.x. This was due to 12.5.x being so much faster than 11.9.x. Refer (1), the speed brought the issue into the foreground, the deadlocks were always there, they just had not approached race conditions until 12.5.x. One customer actually wanted me to slow the server down (?!?!). Instead, I reduced the connections on the one app that opened multiple connections.
On the basis of getting Sybase to delay before identifying blocking locks as deadlocks, so that you can identify the cause, you can also try TEMPORARILY increasing:
- deadlock retries (no more than 20) and
- deadlock checking period (no more than 1000)
The result will be that you will see the blocking locks (not livelocks, please) for longer periods, and more tasks blocked, which will help you identify the app code that begs attention.
Last edited by DerekA; 08-29-06 at 00:48.
Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTau
Anything worth doing is worth doing Right The First Time
Spend your money on standards-compliant development or spend 10 times more fixing it