Problems with Transactions

[Crittar]

I am having problems with one of my programs:

When I try to run the program the whole of the code seems to be regarded as a single transaction.

I have checked this using the transaction function (which I placed right at the top of the program, as soon as I try to run the program I am told a transaction is active).

The code is too large for me to reproduce here but I have found that when I comment out my main "FOR EACH" loop the transaction is no longer regarded as active, as soon as I remove the commenting the whole program once again becomes a transaction.

The "FOR EACH" is:

FOR EACH sinvo
WHERE nl-loc = "2"
AND sinvo.invoice-d ge vardate1
AND sinvo.invoice-d le vardate2 NO-LOCK:

much code (including database updates to tables other than sinvo)

END.

If anyone out there has any ideas as to why I get this large transaction I would be most grateful.

 

[Don Alexander]

Try enclosing any database updates within the for each with
a block that will allow the scope of the transaction(s) to be
smaller.

for each xyx:
program code
find nofile exclusive-lock.
update nofile.
program code
end.
this will scope the transaction to the for each block.

However

for each xyx:
program code
small-block: do on error undo, retry:
find nofile exclusive-lock.
update nofile.
end.
program code.
end.

This will scope the transaction to the small-block.

I hope this helps.

 

[Unregistered]

Transaction

Are you sure your FOR EACH has a NO-LOCK on it?

 

[Crittar]

Don,

Many thanks for your suggestion, I have tried this and it has proved successful.

 

[ravi_gupta2u]

If there is exclusive-lock instead of no-lock, then what would b u suggestions?
Please let me know.

 

[StuartT]

If there is exclusive-lock instead of no-lock, then what would b u suggestions?
Please let me know.

define a buffer for the table.
change it from exclusive-lock to no-lock.
then within the loop just before the update, find the buffer with exclusive-lock (using a recid find) and change the update statement to update the buffer, then release the buffer record.

 

[ravi_gupta2u]

You mean code would be similar to -

define buffer bcust for customer.

for each bcust no-lock:
program code
small-block: do on error undo, retry:
find bust exclusive-lock.
update nofile.
end.
program code.
end.

OR

 

[StuartT]

More along the lines of:
for each bcust no-lock:
program code
find bcust where
recid(bcust) = recid(cust) exclusive-lock.
update bcust.<whatever field to update>.
release bcust.
program code.
end.

 

[rstanciu]

Try to COMPILE with the option LISTING, like
COMPILE xxx.p LISTING xxx.lst.
and read the xxx.lst you can see better when a transaction begin/end.

note: if there is a RUN something.p in the FOR EACH and if in the something.p
there is a UPDATE/DELETE/ASSIGN the scope of transaction change !

 

[Cringer]

find bust exclusive-lock.

I've been trying to execute this code on a regular basis for about 15 years and had no success so far. No matter what I do, someone downgrades my exclusive lock to a share lock.

 

[StuartT]

I've been trying to execute this code on a regular basis for about 15 years and had no success so far. No matter what I do, someone downgrades my exclusive lock to a share lock.

Or more commonly "This record is in use and locked by ????"

 

[Cringer]

Indeed