Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Posts
    61

    Unanswered: Help. Trouble in Locks

    Hello..

    I'm facing one problem in my database informix 11.50.

    I don't know why, but sometimes some tables turn locked and I need restart database to my aplication work fine again.

    I try to find the user or process that locked the table in syslocks, but not apear there.... I'm 100% my table's locked.. but I don't know who locked it and how I can free it...

    Someone can help me ??

  2. #2
    Join Date
    Jul 2009
    Posts
    37
    This should show you who's locking a table.

    database sysmaster;
    set lock mode to not wait;
    set isolation to dirty read;

    select dbsname[1,12], tabname[1,12],username[1,12], tty[1,12],
    connected ,
    type
    from syssessions, syslocks
    where sid = owner
    and tabname = ?

    -----
    otherwise, you'll have to dig through onstat output to find it.
    -----

    If you're trying to alter that table, then any access to that table will prevent modification. You then need a rather complex script to find it (!) I have such a script, but let me know if the above doesn't work for you first.
    Last edited by bigcalm; 07-07-09 at 13:05.

  3. #3
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi, if you're working with a Linux system: this script digs through the onstat output to find it.
    Code:
    #!/bin/bash                                                                     
                                                                                    
    # Input:	tablename                                                           
    # Output:	stdout                                                              
                                                                                    
    #------------------------------FUNCTION chk_excl---------------------------------
    # Check for sessions that currently access the table
    chk_excl()                                                                      
    {                                                                               
        TBL="${1}"                                                                  
        USR=""                                                                      
        RES=""                                                                      
        for SES in `onstat -g sql | awk 'NR > 5 {print $1}'`; do                    
            onstat -g sql $SES | grep "${TBL}" >/dev/null 2>&1                      
            if [ $? -eq 0 ]; then                                                   
                USR="${USR} `onstat -g ses | awk '$1 == ses {print $2}' ses=${SES}`"
            fi                                                                      
        done                                                                        
        if [ ! -z "${USR}" ]; then                                                  
            echo -e "Table '${1}' is in use by:${USR}"                        
        fi                                                                          
    }                                                                               
    #------------------------------------------------------------------------------ 
                                                                                    
    chk_excl $1
    This doesn't show all the locks in all situations is my experience but I couldn't find the alternative way in the sysmaster DB. Maybe bigcalm's query fills the gap for me?

    Good luck,
    Hans
    Last edited by Tyveleyn; 07-07-09 at 17:22.

  4. #4
    Join Date
    Jul 2009
    Posts
    37
    #/usr/bin/sh
    #
    # Author: Jonathan Daniel
    #
    # This script will tell you who is accessing a particular table.
    # It can be useful when attempting to alter/drop/rename a table
    # and you're experiencing the 106 ISAM error - eg
    # 106: ISAM error: non-exclusive access.
    #
    #
    # Derived mainly from information from here:
    # Informix technology: When 'Exclusive' is not really exclusive...
    # IBM Informix Dynamic Server v11.50 Information Center
    #
    # Warning: will only work for dba, probably only informix.
    # Tested on informix 9.40.FC3, AIX 5.3
    #
    # Requires dbaccess and awk derivatives (awk,sed,grep)
    if [ ! "$1" ]
    then
    echo "Usage: $0 <database> <table> [-V]"
    exit 0;
    fi

    if [ ! "$2" ]
    then
    echo "Usage: $0 <database> <table> [-V]"
    exit 1;
    fi
    VERBOSE=""
    if [ "$3" == "-V" ]
    then
    VERBOSE="ON"
    fi

    dbaccess $1 - >/dev/null 2>/dev/null <<EOT1!
    set lock mode to not wait;
    set isolation to dirty read;
    unload to /tmp/hex1.unl
    select lower(hex(partnum)), tabname, dbsname
    from sysmaster:systabnames
    where dbsname = "${1}"
    and tabname = "${2}"
    ;
    EOT1!

    if [ ! -s /tmp/hex1.unl ]
    then
    echo "Cannot find table $1/$2"
    exit 0;
    fi

    hexno=`head -1 /tmp/hex1.unl | sed 's/|.*//g'`
    if [ "${VERBOSE}" ]
    then
    echo "part number of table - ${hexno}"
    fi

    opensessions=`onstat -g opn | grep ${hexno} | awk '{print $2}' | sed 's/^0x//' | sed 's/^0//g' `
    if [ $? -ne 0 ]
    then
    echo "No-one is currently accessing $1/$2";
    exit 0;
    fi
    if [ ! "${opensessions}" ]
    then
    echo "No-one is currently accessing $1/$2";
    exit 0;
    fi
    echo "User \tTerminal \tHost \tSessionID"
    for sessionid in ${opensessions}
    do
    if [ "${VERBOSE}" ]
    then
    echo "Session Id: ${sessionid}"
    fi

    splitme=`onstat -u | grep ${sessionid} | awk '{printf("%s %s %s",$3,$4,$5);}' `
    if [ ! "${splitme}" ]
    then
    # Has probably already disappeared
    continue;
    fi
    sqlid=`echo ${splitme} | awk '{printf("%-10s",$1);}' `
    userid=`echo ${splitme} | awk '{printf("%-10s",$2);}' `
    terminal=`echo ${splitme} | awk '{printf("%-10s",$3);}' `

    if [ "${VERBOSE}" ]
    then
    echo "SQL ID: ${sqlid}..."
    onstat -g sql ${sqlid}
    echo "User: ${userid}. Terminal ${terminal}"
    fi
    # Get hostname
    orighost=`onstat -g ses | grep ${sqlid} | awk '{printf("%-10s",$5);}'`
    # I can't get the host IP. For some reason, only 8 characters are
    # available to me in onstat -g ses
    echo "${userid}\t${terminal}\t${orighost}\t${sqlid} "
    done

    echo "\nYou may kill the sessions listed using: onmode -z <SessionID>"
    echo "You can view the sql currently being run using: onstat -g sql <SessionID>"

  5. #5
    Join Date
    Oct 2007
    Posts
    61

    Solved.

    Thanks for all.

    My problem was in disk space in chunk... it was full.


    Anyways thanks for scripts to check locks.


    Yours
    Fernando.

Posting Permissions

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