Deadlocks in Sybase

[Joseph A. Knapka]

Hello, all,

[Sorry for the reference to such an antique version of
Sybase; what I really need to know is, do later versions
fix the problem described? My client is attempting to
appropriate funds for a DB upgrade, and I need to
know whether to recommend a later version of SQL
Server, or something else.]

It appears to be the case in Sybase Server v. 4.9 that a
single-row update can cause a deadlock, if the row in
question is split across multiple pages and there
is a concurrent read in progress on a row that spans
the same set of pages. Attached Sybase error logs demonstrate
the problem: a reader is acquiring shared page locks on a
pair of pages in the order X,Y, while a concurrently-
executing updater is acquiring exclusive page locks on the
same pair of pages, but in the order Y,X.

My question is, do later versions of Sybase fix this
problem (i.e. by enforcing a page-locking discipline
in which pages are locked in increasing numeric
order, or something similar)? Is there a well-known
workaround for this problem? Or should I just tell
my client to switch to Oracle?

On page 7-25 of the v4 Sybase System Administration Guide,
it says: "In a multi-user situation, each user's application
should check *every transaction* [my emphasis] for
message 1205." Is this intended to mean that even a single-
statement select or update on a single row can invoke
deadlock? This would seem to impose an onerous burden on
the developer, since it essentially requires even the most
trivial transactions to be wrapped in something similar
in spirit to "while @@error=1205".

Here's the aforementioned error log excerpt, edited to
obfuscate table names, but otherwise intact. The stored
procs in question do only simple selects, plus (in the
case of "update_table_X") an update to a single row
in table X. In neither case is a user transaction
(begin tran/commit) involved; the default
transaction-per-statement discipline is used.

> Deadlock Id 4 detected. 2 deadlock chain(s) involved.
>
> Process 23 was executing a UPDATE command in the procedure 'update_table_X'.
> Process 19 was executing a SELECT command in the procedure 'select_table_X'.
> Process 19 was waiting for a 'shared page' lock on page 8695 of the 'X'
> table in database 4 but process 23 already held a 'exclusive page' lock\
> on it.
> Process 23 was waiting for a 'exclusive page' lock on page 8693 of the 'X'
> table in database 4 but process 19 already held a 'shared page' lock\
> on it.
>
> Process 23 was executing a UPDATE command in the procedure 'update_table_X'.
> Process 19 was executing a SELECT command in the procedure 'select_table_X'.
> Process 19 was waiting for a 'shared page' lock on page 8695 of the 'X'
> table in database 4 but process 23 already held a 'exclusive page' lock\
> on it.
> Process 23 was waiting for a 'exclusive page' lock on page 8693 of the 'X'
> table in database 4 but process 19 already held a 'shared page' lock\
> on it.
>
> Process 19 encountered multiple deadlocks.
> Process 19 was chosen as the victim. End of deadlock information.

Thanks for any advice or comments,

-- Joe

* Joseph Knapka / Systems Engineer / TransCore, an SAIC company
* voice://(770)447-6831/3062 fax://(770)449-7268
*
* I speak only for myself, not for my employer, except when the
* little transceiver in the base of my skull is activated...

[br...@sybase.com]

In article <35CF5302...@jhk.com>,

Hi Joe,

Versions of SQL Server up to ASE 11.5.x do suffer from this form of deadlock
to some degree or another.

The new datapages (and datarows) locking schemes in the 11.9 release
do eliminate these chronic deadlocks caused by locks on index pages being
aquired in different orders (because these schemes do not hold
transaction-duration locks on index pages).

11.9 is currently out in a limited release (for use with particular
applications). 11.9.2 is expected to be released for general availability
later this quarter. Details on the 11.9.2 version and information on an early
release program (for testing only, not production use) is available at
http://www.sybase.com/adaptiveserver/ase_upgrade.html

Give it a try, I think you will like it.

--
Bret Halford
Sybase, Inc.
3665 Discovery Drive
Boulder, CO 80303

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

[Joseph A. Knapka]

OK, cool. Then I need to know this:

In any version prior to 11.9, what is the correct action to
take to recover from deadlock inside of a stored procedure? The
sys-admin guide says, "restart the transaction". Can I take that
to mean that the transaction has been rolled back, or not?

So: this?

select @@error = 1205 /* Legal? */
while @@error = 1205
begin
begin transaction
/* Do stuff. */
end
if @@error != 0 rollback
else commit

or this?

select @@error = 1205
while @@error = 1205
begin
begin transaction
/* Do stuff. */
if @@error = 1205 rollback
end
if @@error != 0 rollback
else commit

And if the /* Do stuff */ consists of multiple queries or
updates, I imagine I need to check for @@error = 1205
after each one, and if so continue... right?

Thanks in advance,

- Joe

br...@sybase.com wrote:
>
> In article <35CF5302...@jhk.com>,
> "Joseph A. Knapka" <jak_spa...@jhk.com> wrote:

--snip--

> > It appears to be the case in Sybase Server v. 4.9 that a

> > single-row update can cause a deadlock, yada yada yada

--crumple--

> Versions of SQL Server up to ASE 11.5.x do suffer from this form of deadlock
> to some degree or another.

--shred--

> --
> Bret Halford
> Sybase, Inc.
> 3665 Discovery Drive
> Boulder, CO 80303
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum

--

[Chris Curvey]

Oh, that's easy. You *can't* handle deadlocks within a stored
procedure. When the
server identifies a deadlock and picks you as the victim, it just aborts
the entire
batch at that point. You have to have error handling in your client
program to
resubmit the transaction when it gets error 1105.

-Chris

[Joseph A. Knapka]

Chris Curvey wrote:
> Oh, that's easy. You *can't* handle deadlocks within a stored
> procedure. When the

Uh oh. Can you point me to a Fine Manual where this is
documented? I hate to tell my boss that the problem isn't
fixable without some kind of authoritative reference...

Thanks,

-- Joe

> Joseph A. Knapka wrote:
> >
> > In any version prior to 11.9, what is the correct action to
> > take to recover from deadlock inside of a stored procedure? The

--

[Chris Curvey]

Joseph A. Knapka wrote:
>
> Chris Curvey wrote:
> > Oh, that's easy. You *can't* handle deadlocks within a stored
> > procedure. When the
>
> Uh oh. Can you point me to a Fine Manual where this is
> documented? I hate to tell my boss that the problem isn't
> fixable without some kind of authoritative reference...
>
> Thanks,

What? Seven years at Sybase doesn't count? :)

Look in the T-SQL users manual. I think it's in the section on batches,
or maybe the section on transactions. Somewhere, it authoritatively
states
that "when you are chosen as a deadlock, your batch is cancelled and
your
transaction is rolled back."

The rub is that since your batch is cancelled, you never get to the
error handling code in your T-SQL. For instance:

begin tran

-- assume this statement is the victim of a deadlock.
update foobar
set a = 1
where b = 2

-- this statement will NOT be executed, 'cause the batch has been
cancelled
if @@error = 1205
begin
print "yikes! I'm a victim. Somebody sue the DBMS!"
end

commit tran

[Andrew Prosser]

Troubleshooting and Error Messages Guide;

Error 1205

Severity Level

13

Error Message Text

Release 11.1 and Later

Your server command (family id #%d, process id #%d) was deadlocked with
another process and has been chosen as deadlock victim. Re-run your command.

Release 11.0.x and Earlier

Your server command (process id #%d) was deadlocked with another process and
has been chosen as deadlock victim. Re-run your command.

Explanation

This error occurs when a process tries to acquire a lock on an object that
is locked by a second process when the second process is waiting for a lock
on an object that has been locked by the first process. This situation is a
deadlock, and can involve more than two processes.

SQL Server detects this situation, rolls back the transaction that has
accumulated the least amount of CPU time, and notifies the application
program of this action with Error 1205. This allows the other users'
processes to move forward.

Deadlocks are caused by a number of situations, including:

·Transactions modify tables in different orders. There is a greater chance
of deadlock between two transactions if one is processing in the sequence
A - B - C while the other runs C - B - A.

·Transactions access tables via a nonclustered index. If the optimizer
chooses a different nonclustered index for the same table for two different
queries, a nonclustered index is not in the physical data sequence and the
two processes are acquiring page locks in a random order. Thus, there is a
greater chance that one process will lock a page that the other needs.

·Transactions that use the keyword holdlock or use the set isolation level
command to hold shared locks. When holdlock is appended to a select
transaction it holds the shared lock for the remainder of the transaction.
This increases the risk of deadlock.

·Transactions that require a long time to run. The longer a transaction
runs, the more likely it is that another user will require a resource held
by the transaction.

Action

Restart the transaction that has been rolled back.

To minimize future occurrences of deadlocks, use any of the following
procedures that apply to your site.

Application Error Handling

Each application should have deadlock handling routines. Refer to the
dbsetuserdata entry in the Open Client DB-Library Reference Supplement for a
sample deadlock handling routine.

Use Well-Constructed Transactions

Using transactions constructed to avoid deadlocks greatly reduces their
occurrence. Some techniques for writing transactions that avoid deadlock
include:

·Access tables in the same order in each transaction. Use coding conventions
that require all transactions that access several tables to process them in
the same order.

·Access tables via a clustered index when possible. If it is not possible to
change a nonclustered index to a clustered index to minimize deadlocks, then
trap the deadlock error in the application and provide appropriate recovery
routines.

·If you are using holdlock, decide whether you really need to be using it.
Use holdlock only when you require repeatable reads within a transaction.

·Avoid long-running transactions. Some ways to avoid long-running
transactions are:

- Never allow user interaction within a transaction.

- Separate logical units of work into transactions. For example, acquiring
a sequential key from a key table for use in an insert statement can be
separated into transactions similar to the following (in the first set of
commands, you acquire the key; in the second set of commands, you use the
key to do the insert):

1> declare @key int

2> begin transaction

3> update key_table set key = key + 1

4> select @key = key

5> commit transaction

6> go

1> insert mytable

2> values (@key, ...)

3> go

- Check whether client applications, including third party tools, allow
users to accidentally create long running transactions.

Additional Information

Refer to "Lock Manager" in the System Administration Guide.

Releases in Which This Error Is Raised

11.0 and later

Joseph A. Knapka wrote in message <35D84D3E...@jhk.com>...

>Chris Curvey wrote:
>> Oh, that's easy. You *can't* handle deadlocks within a stored
>> procedure. When the
>
>Uh oh. Can you point me to a Fine Manual where this is
>documented? I hate to tell my boss that the problem isn't
>fixable without some kind of authoritative reference...
>
>Thanks,
>

[mfin...@eds.com]

Chris,

A question/comment. Doesn't @@error get populated w/ 1205 anyway? In that
case, the client could possibly handle the error.

We currently have some deadlock problems that appear to be happening in
trigs and trigger fired sps, which are in turn part of a Powerbuilder based
client side transaction. We can get around it for now with a band aid.
Because we don't use autocommit, when the 1205 happens the client picks up
the 1205 (in sqlca.sqldbcode which comes from @@error) when the tran blows
up, so we can do the appropriate stuff. We enter a loop to retry the update x
times while (sqldbcode <> 1205) and (other error stuff).

Not pretty, but we have miles and miles of trigger code to interface with
another app's database (which isn't pretty either BTW) that we will have to
trace through to find the update problem and have not been able to find our
problems. Until we can find it, we will have to live with the looped updates.

Not sure if the original poster can benefit from this or not, but thought I'd
offer my $0.02. Keep the change.

Mike

In article <35D8CA...@nwaf.com>,
scu...@nwaf.com wrote:

> Joseph A. Knapka wrote:
> >
> > Chris Curvey wrote:
> > > Oh, that's easy. You *can't* handle deadlocks within a stored
> > > procedure. When the
> >
> > Uh oh. Can you point me to a Fine Manual where this is
> > documented? I hate to tell my boss that the problem isn't
> > fixable without some kind of authoritative reference...
> >
> > Thanks,
>

> What? Seven years at Sybase doesn't count? :)
>
> Look in the T-SQL users manual. I think it's in the section on batches,
> or maybe the section on transactions. Somewhere, it authoritatively
> states
> that "when you are chosen as a deadlock, your batch is cancelled and
> your
> transaction is rolled back."
>
> The rub is that since your batch is cancelled, you never get to the
> error handling code in your T-SQL. For instance:
>
> begin tran
>
> -- assume this statement is the victim of a deadlock.
> update foobar
> set a = 1
> where b = 2
>
> -- this statement will NOT be executed, 'cause the batch has been
> cancelled
> if @@error = 1205
> begin
> print "yikes! I'm a victim. Somebody sue the DBMS!"
> end
>
> commit tran
>

-----== Posted via Deja News, The Leader in Internet Discussion ==-----

[Anthony Mandic]

Andrew Prosser wrote:
>
> Troubleshooting and Error Messages Guide;
>
> Error 1205

[snip]

> Joseph A. Knapka wrote in message <35D84D3E...@jhk.com>...
>

> >Uh oh. Can you point me to a Fine Manual where this is
> >documented? I hate to tell my boss that the problem isn't
> >fixable without some kind of authoritative reference...

I hope Andrew's quote from the Error Messages manual was
authoritative enough for you. You should be able to look
this up for yourself and verify. Follow some of its suggestions
and guidelines for good application design and methodology.
However, note that the problem IS fixable in an application.
As the quote says you have to code error and message handlers
in your application. When the code detects this error it should
resubmit the query. Note that this isn't quite as easy as it
sounds, but it is doable.

-am

[Joseph Knapka]

Andrew Prosser wrote:
>
> Troubleshooting and Error Messages Guide;
>
> Error 1205

SNIP

> Releases in Which This Error Is Raised
> 11.0 and later

Interesting; I'm seeing it in 4.9!

Uh, thank you sincerely for the extensive quote. Really, all
I was looking for was, "Troubleshooting and Error Messages Guide".
Unfortunately, the only documentation I have is the System
Administration guide and the TSQL user's guide -- the rest of
it is at the client's site on the opposite side of the continent,
and they've probably lost it :-) So I just wanted to be able
to say, "Boss, I have it on good authority that the
'Troubleshooting Guide' says we can't fix it in the SPs".

Thanks again,

-- Joe

> Joseph A. Knapka wrote in message <35D84D3E...@jhk.com>...

> >Chris Curvey wrote:
> >> Oh, that's easy. You *can't* handle deadlocks within a stored
> >> procedure. When the
> >

> >Uh oh. Can you point me to a Fine Manual where this is
> >documented? I hate to tell my boss that the problem isn't
> >fixable without some kind of authoritative reference...
> >

[Joseph Knapka]

Ah hah!

We have very similar problems. Among other things, we're seeing
deadlocks
inside of triggers that appear to be written in proper deadlock-avoiding
manner. We're using DB-Library, and the references I have available only
cover handling of server messages via callbacks, which I don't want to
mess with if I can avoid it. It does my heart good to know that it's
possible to handle these things inline.

Thanks,

-- Joe

mfin...@eds.com wrote:
>
> Chris,
>
> A question/comment. Doesn't @@error get populated w/ 1205 anyway? In that
> case, the client could possibly handle the error.
>
> We currently have some deadlock problems that appear to be happening in
> trigs and trigger fired sps, which are in turn part of a Powerbuilder based
> client side transaction. We can get around it for now with a band aid.
> Because we don't use autocommit, when the 1205 happens the client picks up
> the 1205 (in sqlca.sqldbcode which comes from @@error) when the tran blows
> up, so we can do the appropriate stuff. We enter a loop to retry the update x
> times while (sqldbcode <> 1205) and (other error stuff).
>

[Supak Lailert]

Joseph Knapka <j...@jhk.com> writes:
>Interesting; I'm seeing it in 4.9!
>
>Uh, thank you sincerely for the extensive quote. Really, all
>I was looking for was, "Troubleshooting and Error Messages Guide".
>Unfortunately, the only documentation I have is the System
>Administration guide and the TSQL user's guide -- the rest of
>it is at the client's site on the opposite side of the continent,

Don't worry. Check out http://sybooks.sybase.com/

Supak
--
--
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
>> Supak Lailert -- s...@yipintsoi.com >>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

[Chris Curvey]

mfin...@eds.com wrote:
>
> Chris,
>
> A question/comment. Doesn't @@error get populated w/ 1205 anyway? In that
> case, the client could possibly handle the error.

Yup. The client can handle the error, but the server batch can't.

>
> We currently have some deadlock problems that appear to be happening in
> trigs and trigger fired sps, which are in turn part of a Powerbuilder based
> client side transaction. We can get around it for now with a band aid.
> Because we don't use autocommit, when the 1205 happens the client picks up
> the 1205 (in sqlca.sqldbcode which comes from @@error) when the tran blows
> up, so we can do the appropriate stuff. We enter a loop to retry the update x
> times while (sqldbcode <> 1205) and (other error stuff).
>

That's exactly the right thing to do. I'm not a PowerBuilder person, so
I'm glad
that there's a way to handle these things properly in PB.

> Not pretty, but we have miles and miles of trigger code to interface with
> another app's database (which isn't pretty either BTW) that we will have to
> trace through to find the update problem and have not been able to find our
> problems. Until we can find it, we will have to live with the looped updates.
>

[Chris Curvey]

One parting shot....

Joseph Knapka wrote:
>
> Ah hah!
>
> We have very similar problems. Among other things, we're seeing
> deadlocks
> inside of triggers that appear to be written in proper deadlock-avoiding
> manner.

Remember, you can minimize deadlocks, but you can't eliminate them. The
server can cause deadlocks just by having two statements traverse the
table table in different ways (scan vs. index). You can also get
deadlocks in your index pages.

>We're using DB-Library, and the references I have available only
> cover handling of server messages via callbacks, which I don't want to
> mess with if I can avoid it. It does my heart good to know that it's
> possible to handle these things inline.
>

Just out of curiosity, why not use callbacks?

> Thanks,
>
> -- Joe
>

[Joseph Knapka]

[Warning: Strong opinions are expressed below, but no animosity or
flaming is intended.]

Chris Curvey wrote:
>
> One parting shot....
>
> Joseph Knapka wrote:
> >
>
> Remember, you can minimize deadlocks, but you can't eliminate them. The
> server can cause deadlocks just by having two statements traverse the
> table table in different ways (scan vs. index). You can also get
> deadlocks in your index pages.

[... keeping in mind that br...@sybase.com says this is no longer
true in the latest release...]

So: it is *impossible* for a careful designer to ensure that the DB
code is deadlock free. IMO, this is evil, and Sybase should be ashamed
of itself for allowing such behavior to persist for three major
revisions of its server product. Other RDBMSs I have worked with
(Oracle, DB/2) do not suffer from this problem. This in itself would
be enough for me to recommend against using Sybase on future projects,
were it not for bret's assurance that it's soon to be fixed.

> >We're using DB-Library, and the references I have available only
> > cover handling of server messages via callbacks, which I don't want to
> > mess with if I can avoid it. It does my heart good to know that it's
> > possible to handle these things inline.
> >
>
> Just out of curiosity, why not use callbacks?

I am maintaining a cross-platform C++ data-access library and its
associated stored procs. The library is used by a whole bunch of
fairly high-availability server-side applications, many of which
use a proprietary asynchronous event-handling library. Since I do
not know how the DB-Library callbacks would interact with all these
things, I would prefer not to find out the hard way :-)

The real reason, of course, is that it looks complicated and hard to
get right the first time. I want to do things the simplest possible
way unless performance demands dictate otherwise. I don't think you
can convince me that using the callback mechanism provided in DB-Library
is simpler than "do { /*...*/ } while (dbretstatus(proc) == -3);"
(although you are welcome to try :-)

> > Thanks,
> >
> > -- Joe
> >

Ditto.