Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Ninth Hell
    Posts
    19

    Question Unanswered: data locks in ASE 12.5.1

    I recently upgraded my database from 11.5 to 12.5.1 to use the new data row locking features. I implemented locking on all my tables that need it using alter table but I was wondering if there anything else I should do to ensure proper performance(I saw somewhere, something about row locking in a server level and maximum row size) because there doesn't seem to be any difference from before(my users seem to work even slower than before with the new locking scheme).

    Any help will be well appreciated.

    PS: The server runs on AIX 4.3.3

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    The row level locking only will solve the locking problems, not the slowness due to other problems (bad config, network, bad programmation, etc)

    Using DOL pages, you periodically must use the reorg rebuild command "defragmenting" your tables.

    This ksh will perhaps help you :

    Code:
    #!/bin/ksh
    
    ASE=$1
    DBList=$2
    USER=sa
    PWD=xxx
    WRKDIR=.
    
    for DB in $DBList
    do
    isql -U${USER} -P ${PWD} -S ${ASE} -o ${WRKDIR}/${ASE}_${DB}_reorg.sql -D ${DB}<< EOF
    set nocount on
    go
    SELECT "reorg rebuild "+name
    FROM sysobjects
    WHERE sysstat2 & 8192 = 0
    AND type="U"
    go
    EOF
    
    grep reorg  ${WRKDIR}/${ASE}_${DB}_reorg.sql > ${WRKDIR}/$$.sql
    echo go >> ${WRKDIR}/${ASE}_${DB}_reorg_final.sql
    
    isql -U${USER} -P ${PWD} -S ${ASE} -D  ${DB} -i ${ASE}_${DB}_reorg_final.sql -o ${WRKDIR}/${ASE}_${DB}_reorg.err
    done
    
    rm ${WRKDIR}/${ASE}_${DB}_reorg_final.sql ${WRKDIR}/$ASE_$DB_reorg.sql ${WRKDIR}/$ASE_$DB_reorg.err

  3. #3
    Join Date
    Feb 2004
    Location
    Ninth Hell
    Posts
    19
    Thanks, I will try it out. I was more concerned about the fact that the users block each other more often now with data row locking than before without. Is this because I might have locked the wrong tables or is there any more configuration on the server side to make it work properly for all tables.

  4. #4
    Join Date
    Sep 2003
    Location
    GENEVA
    Posts
    4

    Exclamation

    Hi,

    When you decide to migrate your tables from APL to DOL, you have to :

    - Check if your dataserver doesn't run out of locks. You can check that in your Sybase errorlog. It could be the reason why your the users are blocked.

    - Check the folowing config parameters :

    page lock promotion HWM
    page lock promotion LWM
    page lock promotion PCT

    row lock promotion HWM
    row lock promotion LWM
    row lock promotion PCT

Posting Permissions

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