Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2006

    Unanswered: Reducing dead locks

    We are running ASE 12.5 on linux kernel 2.6.

    Previous we have had som problems with the error message "Server has run out of locks". We solved the "Server has run out of locks" error by increasing the "number of locks" to 200 000 (100 000 was not enough).

    Instead of the lock error we now have problems with the error message "Your server command encountered a dealock situation".

    What would you recommend to reduce the risk of deadlocks. Should I change the Lock scheme (today I use Allpages on all tables)?

    / Per

  2. #2
    Join Date
    Sep 2002
    Sydney, Australia
    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
    Derek Asirvadem
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts