- 论坛徽章:
- 0
|
各位大侠，今天有个问题求教：
现有两套INFORMIX数据库系统，每套上都有一张表tmp_basetab,（表结构见附件一）因为业务需要的原因，这个表的数据都是每天更新的，而且要使用存储过程sp_prestate（存储过程见附加二）进行细化更新。以前一直好好的，直到前几天，其中一套突然在运行存储过程sp_prestate时只能更新前一条（即只能把accounstate=3更新为state=5)，其他更新都报失败，失败日志如下：
trace on
update tmp_basetab set
(state) = ("5"
where (= accountstate, 3);
update tmp_basetab set
(state) = ("1"
where (and (and (or (= accountstate, 1), (= accountstate, 2)), (>= callservice
stop, i_ttime)), (= lock, "1");
exception : looking for handler
SQL error = -244 ISAM error = -134 error string = = "tmp_basetab"
exception : no appropriate handler
然后我使用finderr查找原因：具体情况如下
-244 Could not do a physical-order read to fetch next row.
The database server cannot read the disk page that contains a row of a
table. Check the accompanying ISAM error code for more information. A
hardware problem might exist, or the table or index file might have
been corrupted. Unless the ISAM error code or an operating-system
message points to another cause, run the bcheck or secheck utility to
verify file integrity.
-134 ISAM error: no more locks.
The ISAM processor needs to lock a row or an index page, but no locks
are available. The number of locks that an operation requires depends
primarily on the number of rows that a single transaction modifies. You
can reduce the number of locks that an operation needs by doing less in
each transaction or by locking entire tables instead of locking rows.
Depending on the implementation that you are using, the number of locks
that is available is configured in one of three places: the
operating-system kernel, the shared-memory segment, or the database
server. Consult your database server administrator about making more
locks available.
好像是表的锁不够，可我的数据库水平太差，实在不知道怎么解决这个问题，怎么才能让这个存储过程，正确的在此表上使用？谢谢各位的尽快回复，谢谢了！
附件一：tmp_basetab的表结构：
{ TABLE "scu".tmp_basetab row size = 65 number of columns = 11 index size = 0 }
create table "scu".tmp_basetab
(
msisdn char(10) not null ,
usertype integer,
accountstate char(1),
servicestart char( ,
servicestop char(,
callservicestop char(,
preaccountstop char(,
accountstop char(,
prefreezestop char(,
lock char(1),
state char(1)
) extent size 16 next size 16 lock mode row;
revoke all on "scu".tmp_basetab from "public";
附件二：存储过程sp_prestate：
CREATE PROCEDURE "scu".sp_prestate(
i_ttime varchar(
)
RETURNING
int;
BEGIN
set debug file to '/tellin/scu/eform/log/sp_Prestate.log';
trace on;
--PIB state
update tmp_basetab set state='5'
where accountstate=3;
--active state
update tmp_basetab set state='1'
where
(accountstate=1 or accountstate=2)
and
CallServiceStop>=i_ttime
and
lock='1';
--incoming state
update tmp_basetab set state='2'
where
(accountstate=1 or accountstate=2)
and
CallServiceStop<i_ttime and PreAccountStop>i_ttime
and
lock='1';
--balance state
update tmp_basetab set state='3'
where (accountstate=1 or accountstate=2)
and
PreAccountStop<=i_ttime and AccountStop>i_ttime
and
lock='1';
--subscriber state
update tmp_basetab set state='4'
where
(accountstate=1 or accountstate=2)
and
((AccountStop<=i_ttime and PreFreezeStop>i_ttime)or
(AccountStop[1,4]=2005 and PreFreezeStop>i_ttime))
and
lock='1';
--lock state
update tmp_basetab set state='6'
where
(accountstate=4 or
lock='0');
--free
update tmp_basetab set state='7'
where
(accountstate=1 or accountstate=2)
and
PreFreezeStop<=i_ttime
and
lock='1';
trace off;
END
END PROCEDURE; |
|
免费注册
发表于 2007-01-04 17:12
