Re: Lock problem
At 04:25 PM 3/10/1998 -0500, you wrote:
>Last weekend I needed to drop one index and add another on a table. We
>are a 24X7 shop, so I just wait until the wee hours of the morning and
>try to grab the table. It kept giving me a 'non-exclusive access'
>error. So...
>
>Instead of continuing to try to run the drop and create commands, I
>added a 'lock table xxx in exclusive mode;' line. I got the lock -- but
>then it still errored on the drop and create statements with the same
>non-exclusive error. I'm confused -- if I got an exclusive lock on the
>table -- why was it telling me I couldn't get exclusive access when I
>tried to do the index commands?
>
>We're running 7.22UC2 on an HP9000 - 10.20.
>
>Any insights would be appreciated.
>Tom Grenier
>DBA, Biztravel.com
>
>
We opened case #694216 with Informix Technical Support over the same
issue. After research they responded that an exclusive lock did not
guarantee exclusive access. It only prevents new threads from
gaining access to the table.
They recommended performing onstat -g opn commands and waiting until
no other user was accessing the table. To accomplish this, we have
added a call to the following UNIX shell script to SQL scripts that
drop and create indexes.
#!/usr/bin/ksh
#==========================================================================
# Script: /dw/bin/lock_test.sh
#
# PURPOSE: Wait until requested table is not in-use by other processes
#
# USAGE: lock_test.sh arg1 arg2
#
# where: arg1 = Database name
# arg2 = Table name
#===========================================================================
integer exitcode # Exit code from awk
integer counter # Loop counter
if [[ $# -lt 2 ]] # Two arguments are required
then
print "usage `basename $0` <database-name> <table-name>"
exit 16
fi
# Determine part number in hex
VAR=`dbaccess $1 <<-EOF!
SELECT hex(partnum) p
FROM systables WHERE tabname = '$2'
EOF!`
VAR2="`echo $VAR | cut -c 3- `" # VAR2 = part number in hex
COLS="`echo $VAR | cut -c 1,3-4 `"
if [[ $COLS != 'p0x' ]] # Was a part number found?
then # .. No,
print -u2 "Error: Table ${1}:${2} not found"
exit 12
fi
# Convert table number to lower case
TRUL='tr "ABCDEF" "abcdef"' # Translate hex characters
TABNO="`echo $VAR2 | $TRUL`" # Table number in lower case
# echo '$TABNO=' $TABNO '$COLS=:' $COLS '$VAR=' $VAR # (For debugging)
# Loop until number of processes for this table is 1
exitcode=1 # Initialize exitcode
until [[ $exitcode -eq 0 ]] # Loop until awk exitcode = 0
do # Count processes with locks on table
onstat -g opn | awk \\
'
(NF > 5 && $6 == "'${TABNO}'") { CNT=CNT+1 }
END {
if(CNT > 1 ) { exit 2 }
}
' exitcode=$? # Save exit code from awk
if [[ $exitcode -gt 0 ]] # Do we have exclusive access yet?
then # ..No,
sleep 10 # wait for 10 seconds
counter=counter+1 # Increment loop counter
if [[ $counter -ge 60 ]] # Have we waited for 10 minutes?
then # ..Yes,
counter=0 # Reset counter and write message
print "`date` Waiting for exclusive access to ${1}:${2}" \\
1>>$LOGFILE 2>&1
fi
fi
done
exit 0
Below is a sample SQL script, which locks the table within a transaction:
SET LOCK MODE TO WAIT 7200; BEGIN WORK;
LOCK TABLE buy_group IN EXCLUSIVE MODE;
! lock_test.sh sales buy_group # Wait for exclusive access
ALTER TABLE buy_group
DROP CONSTRAINT buyg_pk;
DROP INDEX buyg_ix_grp;
COMMIT WORK;