Re: Minimizing lock usage with $DELETE
$LOCK table foo in exclusive mode; and then $DELETE FROM foo WHERE (a_date < :ctl_jdate) AND (b_date < :ctl_jdate); Then the above transaction will consume only ONE lock. Watch out your system's log size to avoid log overflow or long TX if the numbers of rows affected is too large. Good luck Dong >From: Randy Galbraith <rgalbrai@rez.com> >Reply-To: Randy Galbraith <rgalbrai@rez.com> >To: informix-list@iiug.org >Subject: Minimizing lock usage with $DELETE >Date: Tue, 06 Apr 1999 16:26:06 -0700 > >Consider these ESQL/C statement: > >$BEGIN WORK; >$DELETE FROM foo WHERE (a_date < :ctl_jdate) AND (b_date < :ctl_jdate); >$COMMIT WORK; > >Table "foo" has 1.2 million rows. Our DBA has warned me that I may blow >out the available number of locks if I attempt this statement. How does >one deal with this? > >- Randy Galbraith > Spammers please note: UCE/Spam is not welcome at my inbox. > > _______________________________________________________________ Get Free Email and Do More On The Web. Visit http://www.msn.com
Thanks again for everyone's help. Here is how I finally did this...
$DELCARE del_cur CURSOR FOR
SELECT a_date, b_date INTO :ta_date, :tb_date FROM foo
WHERE (a_date < :ctl_jdate) and (b_date < :ctl_jdate)
FOR UPDATE;
$BEGIN WORK;
$OPEN del_cur;
for(lock_recs = 0;;)
{
$FETCH NEXT del_cur;
if (sqlca.sqlcode != 0)
break;
$DELETE FROM foo WHERE CURRENT OF del_cur;
lock_recs++;
if (lock_recs > 999)
{
$CLOSE del_cur;
$COMMIT WORK;
$BEGIN WORK;
$OPEN del_cur;
lock_recs = 0;
}
}
$CLOSE del_cur;
$COMMIT WORK;
- Randy Galbraith
Spammers please note: UCE/Spam will not be tolerated at my inbox.
Dong Xiao wrote:
> $LOCK table foo in exclusive mode; and then
> $DELETE FROM foo WHERE (a_date < :ctl_jdate) AND (b_date <
> :ctl_jdate);
>
> Then the above transaction will consume only ONE lock.
>
> Watch out your system's log size to avoid log overflow or long TX if
> the numbers of rows affected is too large.
>
> Good luck
> Dong
>
> >From: Randy Galbraith <rgalbrai@rez.com>
> >Reply-To: Randy Galbraith <rgalbrai@rez.com>
> >To: informix-list@iiug.org
> >Subject: Minimizing lock usage with $DELETE
> >Date: Tue, 06 Apr 1999 16:26:06 -0700
> >
> >Consider these ESQL/C statement:
> >
> >$BEGIN WORK;
> >$DELETE FROM foo WHERE (a_date < :ctl_jdate) AND (b_date <
> :ctl_jdate);
> >$COMMIT WORK;
> >
> >Table "foo" has 1.2 million rows. Our DBA has warned me that I may
> blow
> >out the available number of locks if I attempt this statement. How
> does
> >one deal with this?
> >
> >- Randy Galbraith
> > Spammers please note: UCE/Spam is not welcome at my inbox.
> >
> >
>
> _______________________________________________________________
> Get Free Email and Do More On The Web. Visit http://www.msn.com