Why am I locking here?
Got a strange problem with one of our tables.
For some odd reason, during a conversion, these statements:
begin work;
lock table mytable in exclusive mode;
delete from mytable where doc_no in (select doc_no from lotsofdocnos);commit work;
This little script failed because we ran out of locks. This is on ODS 7.3
on HPUX 10.2.
Normally, locking the table prevents the generation of Lots -O- Locks
(which is one reason we do it).
Is this no longer the case? (of course, you don't realize this happens
until at least 90% of your rows are deleted, and then they are all rolled
back...).
Currently, we're deleting in little pieces now, but that doesn't answer the
question.
Thanx for any thoughts.
Regards,
Will Hartung
(infosys@ecke.com)
Have you verified that you are holding the locks?
Could it be possible that you would need to lock table lotsofdocnos
also? Transactions, and all that stuff . . . .
John Carlson
Informix DBA
WHSmith USA
Kimberly Dicken wrote:
>
> Got a strange problem with one of our tables.
>
> For some odd reason, during a conversion, these statements:
>
> begin work;
> lock table mytable in exclusive mode;
> delete from mytable where doc_no in (select doc_no from lotsofdocnos);> commit work;
>
> This little script failed because we ran out of locks. This is on ODS 7.3
> on HPUX 10.2.
>
> Normally, locking the table prevents the generation of Lots -O- Locks
> (which is one reason we do it).
>
> Is this no longer the case? (of course, you don't realize this happens
> until at least 90% of your rows are deleted, and then they are all rolled
> back...).
>
> Currently, we're deleting in little pieces now, but that doesn't answer the
> question.
>
> Thanx for any thoughts.
>
> Regards,
>
> Will Hartung
> (infosys@ecke.com)
> > This little script failed because we ran out of locks. This is on ODS 7.3 > on HPUX 10.2. > > Normally, locking the table prevents the generation of Lots -O- Locks > (which is one reason we do it). > Hi Kimberly, do you have a delete trigger on that table ? If so: maybe you have to lock the affected table, too. Which transaction mode are you using ? Maybe you have to SET ISOLATION TO DIRTY READ reading tha table "lotsofcosnos".. Hth, Chris