The default for "no of locks" is 5000. 200,000 is ridiculous, the server will be spending an awful lot of time examining the up-to 200,000 locks on every query !
Deadlocks fall into three categories:
1 Those that can be eliminated
2 Those that can be reduced (but not eliminated)
3 The remainder is internal. This is neither (1) nor (2) but it is a function of (1) and (2), ie. if (1) & (2) are small, (3) will be small; if (1) & (2) are large, (3) will be large.
Deadlocks (1) & (2), and indeed locks in general, are easy to address by app design, and conversely impossible to address without dealing with the app (the cause).
You can also try playing with the config parms (eg reducing parallelism, which of course intelligent apps can use and stupid apps cannot; increasing deadlock checking period), but you have to have a decent handle on the parms, as they are grouped and related, you cannot just change one parm.
Sybase has a very effective locking subsystem, but you need to understand it. It holds a shared lock on pages while SELECTing for ANSI ISOLATION LEVEL purposes. For a quick-and-dirty fix, to execute your reports ala Oracle (which does not lock on SELECT), use ISOLATION LEVEL 1 on all reports.
ALLPAGES locking scheme certainly holds "less" locks, but in my experience (many cust sites) it just delays the symptom by pushing it a bit further into the background, and eventually you will hit that wall. ALLPAGES is not nec. for all tables, just for the contentious ones. ALLPAGES locking only effects (3), not (1) & (2).
Sybase spent an awful lot of money making the oracular and microshifty types happy by implementing DATAROW locking, for porting those apps to Sybase, but it did not provide the hoped-for results. There is no substitute for good app design, the server does not perform magic and compensate for a bad app. DATAROW locking merely reduces the granularity (row vs page) of the locks, but is not a valid address of (1) & (2). Certainly look at the changing to DATAROW for just the few most contentious tables but do not expect much from it.
You are best advised to look at the app and reduce deadlocks and locks by design; reduce you 'no of locks' back to 5 or 10 thousand; and separately ensure you have a fast server (resulting in transient locks held for a shorter duration), rather than crippling it to fit the single-threaded capability of the app.
Last edited by DerekA; 11-20-06 at 22:48.
Reason: A bit more clarity
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