Unexpected lock table overflow

[bohrmann]

Hi,
Our application updates the database records based on a flat file. This file contains a long char chain, containing the code of the update (C<reate>, M<odify>, D<elete>), the table to be updated and the field values in succession. This application is running in every 10 minutes in working hours and operating for several years. Some times we received the lock table overflow error message, but then usually it was caused by other users or batches locking too many records. Yesterday evening it seemed there was neither other batch running nor a user connecting to the database, however, this message appeared. Based on the archived files it seems that in that particular case the application had to process a huge amount of lines, that would explain why the error occurred.
But what I'd like to know is why the locks are not released in such a situation, if the code is edited well (I suppose it is).
See the program:

REPEAT:
CREATE <workfile>.
IMPORT STREAM <stream> <workfile>.

IF <workfile>.table_name = "orders" THEN DO:
RUN create_order.
DELETE <workfile>.
NEXT.
END.

IF <workfile>.table_name = "order_lines" THEN DO:
RUN create_line.
DELETE <workfile>.
NEXT.
END.
.
.
.
END.

PROCEDURE create_order:
IF <workfile>.code = "C" THEN DO:
CREATE orders.
ASSIGN orders.d_creinv = INT(SUBSTR(<workfile>.wf-zone_champs,89,8))
orders.numordjou = INT(SUBSTR(<workfile>.wf-zone_champs,97,4)).
END.
ELSE DO: /* code = "M" or "D" */
FIND FIRST orders
WHERE orders.d_creinv = INT(SUBSTR(<workfile>.wf-zone_champs,89,8))
AND ordres.numordjou = INT(SUBSTR(<workfile>.wf-zone_champs,97,4))
NO-ERROR.
END.
IF <workfile>.code = "D" THEN DO:
DELETE orders.
END.
ELSE DO: /* code = "C" or "M" */
IF <workfile>.code = "C" OR
AVAILABLE orders THEN DO:
ASSIGN
orders.idcompte = INT(SUBSTR(<workfile>.wf-zone_champs,1,3))
orders.idclient = INT(SUBSTR(<workfile>.wf-zone_champs,4,6))
.
.
.
orders.idlieu = INT(SUBSTR(<workfile>.wf-zone_champs,125,3)).
END.
END.
RELEASE orders.
END PROCEDURE.

As far as I know each transaction ends at the bottom of the repeat loop. Should the locks then not be released?

Thanks in advance,
Peter

 

[TomBascom]

What is the actual error message?

Compile your code with XREF. This will show you the true transaction scope.

Also, whatever it is that you think that RELEASE is doing it probably isn't doing.

 

[RealHeavyDude]

Every statement that modifies the database or data retrieval with EXCLUSIVE-LOCK causes a transaction to occur. The transaction is then scoped to the next outer block with transaction capabilities - this might be the procedure itself ...

• Did you check the compiler listing (LISTING option with the COMPILE statement) to see the transaction and buffer scopes?
• Are there any statements that modify the database or a FIND ... EXCLUSIVE-LOCK in the procedure before or after the REPEAT block?

Regards, RealHeavyDude.

 

[bohrmann]

Yes, there is a FIND with EXCLUSIVE-LOCK, just it handles another table. Probably this is what confused me. So does that mean, the transaction begins with that FIND in the main procedure, and we use a REPEAT to no avail? (No effect of the end of the loop, though it's a REPEAT loop?)

 

[RealHeavyDude]

Welcome to the world of transaction scope.

Every FIND ... EXCLUSIVE-LOCK can only be done within a transaction regardless for which tables. Transactions are not scoped to database objects, manipulation of database objects are contained in a transaction - otherwise each statement that modifies the database would be isolated in it's own transaction.

If the REPEAT is in the same block (the procedure?) in which the FIND ... EXCLUSIVE-LOCK is then the transaction is caused by the FIND. The scope of the transaction is then raised up to the next outer block - which in your case could be the procedure itself. That means that all iterations within the REPEAT are in the transaction cause by the FIND. REPEAT blocks do not bind a transaction to them by default - sometimes they just happen to be the next outer block with transaction properties.

Unless your code reveals other "accidentally" unwanted behaviour a solution would be to wrap the code belonging to the FIND in it's own DO TRANSACTION block.

Working with the TRANSACTION statement on purpose does make sense.

I can not stress this enough - not understanding record locking, buffer and transaction scope is by far the most common issue for unwanted behaviour like blowing the lock table, getting things rolled back which should have been committed, leaving SHARE-LOCKs and, you name it ...

It is just that the compiler often makes "right" descisions , but, that doesn't lift the responsability from the developer's shoulders - IMHO.

Regards, RealHeavyDude.

 

[bohrmann]

Thanks for the answers. I'll keep in mind.