Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Location
    Sydney, Australia
    Posts
    66

    Unanswered: experience with Lock DataPAGES

    Hi,

    I'm upgrading an old system from Lock AllPages. It had a few deadlocks recently. The system inserts records slowly, one by one, outside transactions, so I suspect the deadlocks were due to Index pages.

    I made a few test with the "new" locking schemes. It appears the system works best if I convert the larger tables (2 million rows) at Lock DataPages, while smaller tables (40K rows) convert to Lock DataRows. When I tried DataRows on the large tables as well, the system slowed down by 20%, especially at the overnight functions which must execute a few table scans. This all makes sense.

    There was a lot of discussion about the merits of row-level locking. I have heard less about page-level locking without index locks (perhaps I was just asleep).

    What is the experience with Lock DataPAGES ? Any issues to watch for, when moving from Lock AllPages ?

    Regards,

    Andrew

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Theres no major issues with data pages locking although many still havent started using it.

    For row level locking, it has to be pure OLTP. i.e. You are just inserting a row, or updating a row or a few rows... If you are doing batch jobs, its going to be a performance hit.

    Also, the 20% problem you had could be maybe with lock waits... So, run a sp_sysmon and see if thats the case.

    The only thing you have to consider when moving to DOL is you bump up the LWM/HWM from default to say 8k and 10k respectively. That way ASE doesnt escalate to table scan sooner.

    Also, you need more number of locks.

  3. #3
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    Sorry, forgot to add one thing, which is in 11.9.x.

    If you change the locking scheme in 11.9.x, you need to rebuild all triggers, stored procs OR it will be a serious performance issue.

Posting Permissions

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