Results 1 to 5 of 5

Thread: Table locks

  1. #1
    Join Date
    Apr 2003
    Posts
    10

    Unanswered: Table locks

    Hello.

    We have a problem with table-locking. Ever so rarely (about once a month) Sybase "forgets" to unlock a table causing all programs that access that table to block (and eventually timeout). Quite unfortunate.

    We have programs that AGGRESIVELY read and write to said table at the same time. The (concurrent) programs that write to the table are using transactions. Sometimes, these programs are killed (the hard way using "kill"), possibly in mid-transaction (by crappy legacy code...).

    The fix is to manually remove the table lock using Sybase Central. After a restart of the programs, they work again.

    We are using Adaptive Server Enterprise 11.9.2.4 from 14. December 2000. On a Linux 2.0 system.

    Any ideas on how to solve this so that Sybase "remembers" to unlock the database tables (preferably regardless of strange legacy code behavior)?

    Morten

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    with 11.9. you can alter heavily and frequently used tables to row level locking ...

    there is an option within sybase central within the table properties ..

  3. #3
    Join Date
    Apr 2003
    Posts
    10
    Under the Locking tab it has a "Locking Scheme" field group. For most heavily accessed tables (read+write), it was set to "Data Row"; for one table it was set to "Data Page", and for another to "All Pages". We have now set it to "Data Row" for all heavily accessed tables.

    But what is the catch in doing that? Increased memory usage? And will it solve the problem?

  4. #4
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159

    Re: Table locks

    Originally posted by mhelles

    We have programs that AGGRESIVELY read and write to said table at the same time. The (concurrent) programs that write to the table are using transactions. Sometimes, these programs are killed (the hard way using "kill"), possibly in mid-transaction (by crappy legacy code...).

    Morten
    Why do you kill the programs? Is this because of some other problem, or do you do this when users complain "It's taking too long."

    Using data rows locking may not solve the problem if it causes exclusive table locks. Have you considered raising the HWM for lock promotion. This will require an increase in the number of locks before a table lock will be requested. Obviously this doesn't solve the original problem, why stuff grabs a table lock and never releases it.

    The obvious question I tend to ask, concerns long transactions. When this problem occurs look in syslogshold to see the longest running transaction.

    select * from syslogshold order by starttime asc

    Hopefully you've named the transactions to have sensible names that might indicate the point in the application code that's causing the long running transaction.

    The version of ASE you're running is quite old. Have you considered migrating to ASE 12 or 12.5. Both are supported on Linux, though you may need to update your kernel, glibc etc. I have sucessfully run ASE 12.0 and ASE 12.5 on Redhat 8.0 without any problems, though I don't ask it to do much, just be there when I need to fire some SQL.

    HTH Richard

  5. #5
    Join Date
    Apr 2003
    Posts
    10
    I kill the programs because sometimes they hang. This is, however, a problem of the program software, and is not related to ASE. But I am wondering whether this program killing *sometimes* results in ASE "forgetting" to unlock a table, e.g. if the program is killed during a transaction.

    I have no idea whether the transactions are properly names (we use some code - for which we don't have the source code - for handling db-access, including the transactions), but I'll have a look in the syslogshold the next time the problem occur. Thanks for the hint.

    Personally, I would like to upgrade to a never version. But its quite a complex and daunting task that I am quite unwilling to undertake until I am pretty sure that is going to solve the problem.

    Morten

Posting Permissions

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