Tuesday, June 14, 2005

 

Deadlocks in Sybase ASE

Normally deadlock information is written to the Sybase error log.

However, that may not include enough information for your analysis. You can enable more information to be written to the error log using the "print deadlock information" parameter.

1> sp_configure "print deadlock information" 1
2> go

This parameter should be disabled after the detailed information has been collected because this setting degrades Sybase ASE performance.

Please check the Sybase manual for more information on this parameter, and deadlocks in general.

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/ptallbk/@Generic__BookTextView/17268

On older versions of Sybase ASE you would get this deadlock information by using certain Trace Flags. Look at 1204 and 3605.

Actually handling deadlocks is a topic for another day. For a potentially quick fix, consider increasing the number of locks.

Or, reduce the locking on index pages by altering affected tables from allpages lock to datapage lock. This rebuilds all the indexes, so it may take awhile.

1> alter table [name] lock [locktype]
2> go

I will leave you with the two best links I have on deadlocks:

Peter Sap's Sybase ASE tip: Investigation of deadlocks reported in the errorlog
http://www.petersap.nl/documents/deadlock.html

Todd Boss's technical notes on deadlocks (Edited)
http://www.bossconsulting.com/sybase_dba/sublevels/
deadlock.info

Comments:
Robert ,
Appreciate the effort you are putting here , this is a great page. This gives us step by step process which really helps me like any other newbies. Please keep up the great work.

Thanks
AJ
 
Fyi; if you want the real source of my sites, go directly to www.bossconsulting.com/sybase_dba instead of this blacksheepnetworks.com site, who has blatantly copied all my content to their site. --Todd Boss
 
Todd,

My apologies - I was unaware that you did not endorse Black Sheep's use of your article. I will correct this post immediately.

Robert
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?