ENQUEUE and DEQUEUE in a loop

[Former Member]

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

[Former Member]

Hi,

Try using 'COMMIT WORK' after your update and

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

Hope this may be helpful.

Regards,

Sharin

[Former Member]

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.