如何将数据库中表的锁方式改为行的方式？

vigor20

如何将数据库中表的锁方式改为行的方式？

lmtok

alter table tablename lock mode(row);

play123456

下列shell把数据库中所有用户表改为行锁模式：

#!/usr/bin/ksh
###############################################################################
#
#       Module:         page_locking.sh
#       Author:         Peter R. Schmidt
#       Description:    Change all tables to row level locking
#
#        Argument 1        Database Name
#
#       Change Log
#
#         Date    Name               Description.................
#       07/14/99  Peter R. Schmidt   Start Program
#
###############################################################################

OUTPUT=row_locking.out
TMPFILE=row_locking.tmp
FLAG_FAILED=false
FLAG_RETRY=false
MAX_RETRIES=50

if [ $# != 0 ]
then
        DATABASE=$1
else
        echo "Usage: row_locking.sh [database name] [Retry #]"
        exit 1
fi

if [ $# = 2 ]
then
        FLAG_RETRY=true
        NUM_RETRY=$2
else
        FLAG_RETRY=false
        NUM_RETRY=0
fi

if [ -f $OUTPUT -a $FLAG_RETRY = false ]
then
        rm -f $OUTPUT
fi

if [ -f $TMPFILE ]
then
        rm -f $TMPFILE
fi

UDATE=`date`
echo "----------------------------------------------------------------------" | tee -a $OUTPUT
echo "Convert Informix tables to ROW level locking - $UDATE" | tee -a $OUTPUT
echo "----------------------------------------------------------------------" | tee -a $OUTPUT

if [ $FLAG_RETRY = true ]
then
        UDATE=`date` | tee -a $OUTPUT
        echo "RETRY $NUM_RETRY on $UDATE" | tee -a $OUTPUT
        echo "----------------------------------------------------------------------" | tee -a $OUTPUT
fi

echo "Collecting table info from the systables table..." | tee -a $OUTPUT
        
dbaccess <<-EOF1 2>&1 | tee -a $OUTPUT
        database $DATABASE;
        unload to '$TMPFILE' delimiter "|"
        select tabname
        from systables
        where locklevel <> 'R' and tabid > 99 and tabtype = 'T'
        order by 1;
EOF1

echo "unload completed" | tee -a $OUTPUT

echo "----------------------------------------------------------------------" | tee -a $OUTPUT

for TABLE in `awk '{ print $1 }' FS='|' $TMPFILE`
do

        UDATE=`date +%D-%T`
        echo | tee -a $OUTPUT
        echo "Attempting to alter table $TABLE on $UDATE" | tee -a $OUTPUT
        echo | tee -a $OUTPUT
       
        dbaccess >> $OUTPUT 2>&1 <<-EOF2
                database $DATABASE;
                alter table $TABLE lock mode (ROW);
        EOF2
       
        if [ $? != 0 ]
        then
                        echo "Note: alter on table $TABLE FAILED" | tee -a $OUTPUT
                FLAG_FAILED=true
        else
                        echo "Note: alter on table $TABLE SUCCESSFUL" | tee -a $OUTPUT
        fi

        echo | tee -a $OUTPUT
        echo "----------------------------------------------------------------------" | tee -a $OUTPUT
        done

rm -f $TMPFILE

echo
echo "Program completed - results in $OUTPUT"

if [ $FLAG_FAILED = true ]
then
        UDATE=`date +%D-%T`
        echo "Note: some tables did not get converted to row locking" | tee -a $OUTPUT
        if [ $NUM_RETRY -ge $MAX_RETRIES ]
        then
                echo "Exceeded maximum number of retries of $MAX_RETRIES" | tee -a $OUTPUT
                echo "No more attempts will be made" | tee -a $OUTPUT
        else
                echo "Sleep 10 seconds on $UDATE..." | tee -a $OUTPUT
                sleep 10
                NUM_RETRY=`expr $NUM_RETRY + 1`
                exec $0 $DATABASE $NUM_RETRY
        fi
fi