Problem with transactions - controlling commit

abartonicek · 2006-02-16

We are trying to create a procedure that will undo a shipment and automatically post a corrected shipment.
We are using standard Navision functionality by calling procedure that calls the following CU:
CU: Undo Sales Shipment Line &
CU: Sales Post

The problem is when somebody else is using Sales Line table (table locking?) the Sales Post part of routine fails and the error is displayed and rollback is performed to the pre Sales Post state - meaning: our transaction was rolled back to the point after Undo Sales Shipment Line, not to the initial state.

Is there a way to control the transaction from start to end, and if something goes wrong we can return to our initial state (not the state on beginning of some trigger!).

As far as we know, COMMIT is performed on end of every trigger and if our function has more then one trigger then the rollback is performed to the state that was on beginning of current trigger.

Does that mean that our undo functionality (including functionality used from CU: Undo Sales Shipment Line & CU: Sales Post ) has to be in ONLY ONE trigger???

kriki · 2006-02-16

If you call Undo Sales Shipment Line & Sales Post in the same moment (meaning a program that calls both without userinteraction [and it seems you are doing this] ), a transaction continues UNTIL the control is returned to the user OR a COMMIT is executed.
I think that in C80 somewhere a COMMIT is hidden. Check out the codeunit for COMMIT's there are a few in it that can be a real pain-in-the-ass for this kind of things.

abartonicek · 2006-02-16

If you call Undo Sales Shipment Line & Sales Post in the same moment

They are called in the same trigger (with some code in between)!

So we should try to remove those COMMIT's?
What are the possible problems if we do that?
I thought that COMMIT is performed at the end of every trigger :?:

kriki · 2006-02-16

abartonicek wrote:

If you call Undo Sales Shipment Line & Sales Post in the same moment

They are called in the same trigger (with some code in between)!

So this is ok.

abartonicek wrote:

I thought that COMMIT is performed at the end of every trigger :?:

A common misconception, maybe because it is not explained well enough in the manuals/courses.

abartonicek wrote:

So we should try to remove those COMMIT's?
What are the possible problems if we do that?

Possible performance issues because the transaction becomes longer in case of a normal posting.
So better to make a new function in C80 to say that the commits should not be used. And just before calling the codeunit, call that function. I give some idea how to do it.

In codeunit 80:

Function DontUseCommit()
blnDontUseCommit := TRUE; // this is a global variable of C80, not a local for this function

And all COMMIT's must be changed by this code:

IF NOT blnDontUseCommit THEN
  COMMIT;

Code for calling C80:

CLEAR(cduSalesPost);
cduSalesPost.DontUseCommit();
cduSalesPost.RUN(recSalesHeader);

kine · 2006-02-16

The commit is called on beginning of CU80 - when the document is Released and if you need to get new doc. no. for shipment or invoice (if the nos. are not prepared in sales header). If you release the document yourselfs and prepare the nos., the commit will not be called.

abartonicek · 2006-02-20

Thanks for all the help you give me!
We solved it like kriki sad!
Just to mention, there was another COMMIT in Update Analysis View (CU 410) that needs to be taken care of 8)

rcardoso · 2006-06-14

The behavior explained is the same for SQL database or NAV native database?

kine · 2006-06-14

Same for both...

Problem with transactions - controlling commit

Answers

Categories