locks, deadlocks in sybase
Ajay Dankar
Hello there:
Question on locks.
1. Does sybase support row level and/ or column level locks? I scanned
the manuals and it seems that for what I want, it provides locks at
table level only. Any workarounds if one knows that queries will not
be be using overlapping rows?
2. Does anyone have suggestions for deadlock situations. The sybase server
aborted some of the processes that were involved in deadlock, says to
retry the operation if deadlock error has occured. What is the recomme-
nded procedure to take care of this, put a deadlock error handler in
the application code? I wonder if other vendors (oracle, informix and
ingres) provide some other mechanisms.
--
-------------------------------------------------------------
_ . _ _/ _ _ / _ _
(/ / (/ (_/ (/ (/ // /< (/ /
(/ ___/
Howie Michalski
>
>Hello there:
>
>Question on locks.
>
>1. Does sybase support row level and/ or column level locks? I scanned
> the manuals and it seems that for what I want, it provides locks at
> table level only. Any workarounds if one knows that queries will not
> be be using overlapping rows?
SQL Server uses PAGE level locking (1 page == 2048 bytes). The server will
begin with these page level locks, and escalate to a table level lock only
after many (~250) page locks have been acuqired on a single table. There
are some 'creative' ways to emulate row-level locks. One that is [not quite]
commonly used is to use the 'fill factor' value when creating a clustered
index on a particular table so that only one row of data fits on each disk
page. I've seen this actually have some positive performance effects with
'small-ish' reference tables who's rows are used 'deep down' inside of
complex queries (the specific example is a reference table of valid NYSE,
NASDAQ, and other CUSIP/Ticker Symbols in a financial database which were
frequently referenced but rarely updated, can you say HOLD LOCK :).
I think the Sybase FAQ that's been floating around (and due for release again
soon) contains a fairly well written description of the various types of
locks the server uses, and how the locking process, in general, 'works'.
>2. Does anyone have suggestions for deadlock situations. The sybase server
> aborted some of the processes that were involved in deadlock, says to
> retry the operation if deadlock error has occured. What is the recomme-
> nded procedure to take care of this, put a deadlock error handler in
> the application code? I wonder if other vendors (oracle, informix and
> ingres) provide some other mechanisms.
>
It really depends on the circumstances that brought you to a deadlock cond-
ition before a 'sensible' solution can be figured out. The only times I've
ever seen 'problematic' deadlocks of a noticeable frequency are in 'complex'
client programs that actually lock themselves out by holding resources with
one processes that are needed for a second (or 3rd, 4th...).
Cheers,
Howie
--
Howard Michalski Work: ho...@sybase.com
Professional Services Consultant Private: ho...@clark.net
Sybase, Inc. Phone: 301.564.5356
[My opinions are my own and NOT those of my employer.]
Dan Hepner
>2. Does anyone have suggestions for deadlock situations. The sybase server
> aborted some of the processes that were involved in deadlock, says to
> retry the operation if deadlock error has occured. What is the recomme-
> nded procedure to take care of this, put a deadlock error handler in
> the application code? I wonder if other vendors (oracle, informix and
> ingres) provide some other mechanisms.
Not a Sybase specific answer, but deadlocks are not a Sybase specific
problem ...
The main goal is to avoid the deadlocks in the first place. The basic
technique is to make sure that any transactions competing for two
or more locks must acquire them in the same order.
If you're sure that this has already been done, a common cause of
deadlocks is "read (getting read lock)" followed by "write (upgrade
to write lock)". Two transactions cannot competitively execute
this sequence without deadlock. Look for a way to effectively get
the write lock at read time if the write is going to happen later
(sometimes called an intent lock).
With some products under some conditions, a long delay (lock timeout) is
called a deadlock. Avoid transactions which hold locks while awaiting
user input.
Deadlocks are almost always completely avoidable by proper application
coding, and seeing them is an indication that the application behavior
should be analyzed to find out why they are happening. However, the
application should be prepared to handle a deadlock if it happens, by
automatically retrying the transaction. Other kinds of transaction
abort which suggest transitory failure can be handled with the same code.
Dan Hepner