topic ENQUEUE and DEQUEUE in a loop in Application Development and Automation Discussions

ENQUEUE and DEQUEUE in a loop

Former Member — Wed, 30 Nov 2011 05:48:31 GMT

Hi All,

I have a program where I am doing locking and unlocking at the record level.

This ENQUEUE and DEQUEUE is being done in a loop. So, the flow of the code in a single loop pass is as follows:

ENQUEUE-> Update-> DEQUEUE.

Since I am trying to update close to 100000-150000 records, the lock table gets overflowed and program is terminated.

MY question:

If i am doing a DEQUEUE in the loop pass itself, ideally the lock entry should be deleted from the lock table. Why does the entries dont get deleted and the lock table overflow appears?

Note:

1. I am correctly doing a locking and unlocking at the record level.

2. I am aware that we should do mass updation by using table level lock rather than record level lock.

Thanks & Regards,

Swati Dogra

Re: ENQUEUE and DEQUEUE in a loop

Former Member — Wed, 30 Nov 2011 05:57:48 GMT

Hi,

Try using 'COMMIT WORK' after your update and

'WAIT' after your 'DEQUEUE' to avoid lock overflow.

Hope this may be helpful.

Regards,

Sharin

Re: ENQUEUE and DEQUEUE in a loop

Former Member — Wed, 30 Nov 2011 06:01:03 GMT

DEQUEUE function works asynchronously (delegated to system) unless specified using _SYNCHRON parameter. That is, the locks are not guaranteed to be removed after the DEQUEUE function returns to the calling program, if _SYNCHRON parameter is not set.

You need to call DEQUEUE function with parameter _SYNCHRON = 'X', so that it waits till the lock is removed from the lock table.

However, as you realize, locking and unlocking tables at record level for so many records will hit performance very badly.