If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Deadlock on row locking table

I am running on 12.5.0.3 and I am getting deadlocks on a table (one update and one select on the same table are colliding) and that table is set for row-level locking. It does not happen very often but.... everything I read about deadlocks says to cure it, change the table to row-level locking.... my table is already set up for that. Any ideas why this would be happening?

All tables have lock thresholds. Once it crosses the thresholds, locks get escalated to APL. So either, you are doing a update and SELECT to the same row or you are consuming too many locks causing the lock to be escalated to APL for that transaction time.

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.

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

Cheers

Last edited by DerekA; 08-29-06 at 01:48.

Derek Asirvadem
Senior Sybase DBA/Information Architect derekATsoftwaregemsDOTcomDOTauAnything worth doing is worth doing Right The First Time
Spend your money on standards-compliant development or spend 10 times more fixing it