What is COMMIT for?

Andwian

I have read about COMMIT in the NAV online help, but I still do not understand.

Would someone explain it please?

Thank you.

COMMIT (Database)

Use this function to end the current write transaction.

COMMIT
Comments
When the system enters a C/AL codeunit, it automatically enables write transactions to be performed. When the system exits a C/AL code module, it automatically ends the write transaction by committing the updates made by the C/AL code.
This means that if you want the C/AL codeunit to perform a single write transaction, the system automatically handles it for you. However, if you want the C/AL codeunit to perform multiple write transactions, you must use the COMMIT function to end one write transaction before you can start the next. The COMMIT function separates write transactions in a C/AL code module.

Example
The metasyntax below contains two write transactions. As execution begins, a write transaction is automatically started. Using the COMMIT function, you tell the system that the first write transaction has ended and prepare the system for the second. Once execution has been completed, the system automatically ends the second write transaction.

BeginWriteTransactions
(C/AL Statements) // Transaction 1
COMMIT(...)
(C/AL Statements) // Transaction 2
EndWriteTransactions

bestianhow

COMMIT need to use carefully.
Example:
Let said you have a function called CreateSalesOrder.

//INSERT Sales Header
SH.INIT;
SH."No." := 'XXXXX';
.....
.....
.....
SH.INSERT;

COMMIT;

FOR i := 1 to NoOfLine DO BEGIN
//INSERT Sales Line 
END;

Refer to the Coding above, if the Sales Line inserting got error, the Sales Header still inserted in database, because the COMMIT command executed before Sales Line that coding.
When you use COMMIT command, standard NAV's rollback feature will broken.

Andwian

bestianhow wrote:

COMMIT need to use carefully.

Is it because how we want the system work, rollback at any point or from the beginning?

bestianhow wrote:

When you use COMMIT command, standard NAV's rollback feature will broken.

Oh, I have been faced with some scenario where:
Once the program error, it will be rollbacked to the initial condition, or some point.

I understand now that these behaviour is just because of this COMMIT placing.

Thank you bestianhow!

einsTeIn.NET

COMMIT is to write your changes straight into the database. If an error occurs then this will be the last point where your system is able to do a rollback. All other changes that were made before COMMIT will remain in the database even if there's an error.

kine

The main thing you need to learn is word TRANSACTION. Learn what does it mean. Than you will learn new two words - Rollback and Commit. Than the Commit command will be clear for you and ERROR command will be same as Rollback. ;-)

It is just about terms from database systems... :whistle:

lvanvugt

If NAV is in a process of changing data in the database this is done in a temporary (database) version. Once the whole process is complete it needs to be finalized, i.e. the temporary version should become final or ... comitted.
In general as a programmer you need not do anything for this as the system will automatically perform a commit as soon as the process is ended. This is called an implicit commit.
From a user point of view a process (write transaction) ends as soon as the focus of the system is back to the user, i.e. the user can perform his next action. For example a posting routines has ended, new value have been entered on a record and the focus is changed to another record.
Sometimes however a programmer explicitly wants to commit the changes his code made to the database long before the whole process will end. In that case you want to program a commit using the COMMIT function. This is called an explicit commit.

Andwian

Thank all of you for the response.

lvanvugt wrote:

If NAV is in a process of changing data in the database this is done in a temporary (database) version.

Thank you for the useful link

lvanvugt wrote:

Sometimes however a programmer explicitly wants to commit the changes his code made to the database long before the whole process will end. In that case you want to program a commit using the COMMIT function. This is called an explicit commit.

When will we need the explicit COMMIT? Would you give me a scenario?

kine wrote:

The main thing you need to learn is word TRANSACTION.

Do you mean: Browse, Snapshot, UpdateNoLock, and Update?

kine

No, transaction as itself. What is transaction, when it starts, what does it mean... e.g. http://en.wikipedia.org/wiki/Database_transaction

lvanvugt

Andwian wrote:

When will we need the explicit COMMIT? Would you give me a scenario?

Basically this is what this part of the Help topic on COMMIT is saying:

... if you want the C/AL codeunit to perform multiple write transactions, you must use the COMMIT function to end one write transaction before you can start the next. The COMMIT function separates write transactions in a C/AL code module.

A famous example is to be found codeunit 80 (Sales-Post) and 90 (Purch.-Post).
The scenario here is that a sales/purchase document needs to be posted and various new documents can be created likewise the Posted Sales Invoice. For these new docs a new number is to be taken from the associated number series. Taking such a number means updating the number series table(s). When updating this table the system locks this table so the posting process is the only 'user' allowed to modify this table and thereby locking out all other users/processes. They can read but not write (update). This would mean everybody else in the same company (i.e. database) can not do anything related to getting numbers from number series as long as the posting of the sales/purchase document has not ended and an implicit commit is executed. Therefor an explicit commit has beebn programmed in codeunit 80 and 90.
Just search for COMMIT in these codeunits.

Andwian

kine wrote:

No, transaction as itself. What is transaction, when it starts, what does it mean... e.g. http://en.wikipedia.org/wiki/Database_transaction

I will read it. Thank you for sharing.

lvanvugt wrote:

This would mean everybody else in the same company (i.e. database) can not do anything related to getting numbers from number series as long as the posting of the sales/purchase document has not ended and an implicit commit is executed. Therefor an explicit commit has beebn programmed in codeunit 80 and 90.

lvanvugt wrote:

Quote:
... if you want the C/AL codeunit to perform multiple write transactions, you must use the COMMIT function to end one write transaction before you can start the next. The COMMIT function separates write transactions in a C/AL code module.

In my understanding: please CMIIW

So it is about "taking the new No. Series" and then "let the other user take a newer No. Series" - so the other users will not be locked to wait until the first user process finished, right?

Rather than "do the take No. Series" and then "do the Sales Post".

I am still confused with the phrase:

if you want the C/AL codeunit to perform multiple write transactions, you must use the COMMIT function to end one write transaction before you can start the next.

I confuse with the terms "First" transaction and the "Next" transaction.

kine

First transaction is the implicitly started transaction
Next transaction is when you want to do some next step which should be separate transaction and you need to end the previous transaction.

Transaction 1 begin
some reads and writes which are atomic
Transaction 1 end
Transaction 2 begin
some reads and writes which are atomic
Transaction 2 end
....

lvanvugt

Andwian wrote:

In my understanding: please CMIIW

CMIIW? My knowledge is failing. What does that mean?

Andwian wrote:

So it is about "taking the new No. Series" and then "let the other user take a newer No. Series" - so the other users will not be locked to wait until the first user process finished, right?

Yes

For the rest: Kamil has perfectly answered your question.

Andwian

I have a scenario here where:
1. Register the Whse. Pick
2. Post the Whse. Shipment

are the 2 process that are separated.

Suppose that I want to combine each other, i.e. when I register the Pick, it will automatically post the Whse. Shipment.
If I just "combine" these two transaction, then if there is an error when post the Whse. Shipment (e.g. The Shipping No. Series is missing in Sales Header), then the system will rollback, just to the condition where AFTER the pick has been registered, but BEFORE the Whse. Shipment posted, i.e. I will get the Pick registered, but the Shipment not posted already.

Hence, if I want these transactions becoming one whole transaction, I should search for the COMMIT in the first (register pick) transaction, and then remark the COMMIT. So, that it will cancel to register the pick although the register is successful, when the Shipment posting is fail.

Am I understand enough the explanations?

lvanvugt wrote:

CMIIW? My knowledge is failing. What does that mean?

That is the internet slang, instead of the highly-technology term in NAV
Correct Me If I am Wrong http://www.internetslang.com/CMIIW.asp

Andwian

One more shot

A famous example is to be found codeunit 80 (Sales-Post) and 90 (Purch.-Post).
The scenario here is that a sales/purchase document needs to be posted and various new documents can be created likewise the Posted Sales Invoice. For these new docs a new number is to be taken from the associated number series. Taking such a number means updating the number series table(s). When updating this table the system locks this table so the posting process is the only 'user' allowed to modify this table and thereby locking out all other users/processes. They can read but not write (update). This would mean everybody else in the same company (i.e. database) can not do anything related to getting numbers from number series as long as the posting of the sales/purchase document has not ended and an implicit commit is executed. Therefor an explicit commit has beebn programmed in codeunit 80 and 90.
Just search for COMMIT in these codeunits.

Is it okay to remove the COMMIT after the Sales Header get the No. Series?
Can we just view it as the one whole transaction, instead of 2 atomics transaction? Because I read from the reference, there is a phrase: "all-or-nothing" proposition.
I guess we can, but as long as we do not COMMIT, the No. Series table will be locked for the other user, until the first user in question finishing his task. Hence, it will lead to any table locking error for the other users.

Do I get this well? \:D/

lvanvugt

Andwian wrote:

One more shot

lvanvugt wrote:

A famous example is to be found codeunit 80 (Sales-Post) and 90 (Purch.-Post).
The scenario here is ...

Is it okay to remove the COMMIT after the Sales Header get the No. Series?
Can we just view it as the one whole transaction, instead of 2 atomics transaction? Because I read from the reference, there is a phrase: "all-or-nothing" proposition.
I guess we can, but as long as we do not COMMIT, the No. Series table will be locked for the other user, until the first user in question finishing his task. Hence, it will lead to any table locking error for the other users.

Do I get this well? \:D/

Yes =D>

kine

Of course, do not just remove the command, rather create condition, which will skip the command only for your specific case and rest will work as before!!! Adding or removing commit could totally break the system! :whistle:

DenSter

Locking the No Series for other users is not always a bad thing though. You can't take that and start inserting COMMIT statements all over the place because you want to prevent locks. Locks are most often necessary to keep database integrity intact.

You need to learn about transactions, and rolling back transactions.

Say a transaction contains 3 steps: step 1, step 2 and step 3. If there are no errors, and all three steps are completed successfully, the transaction is complete and NAV issues a COMMIT behind the scenes. No need to explicitly issue the command. Now if there is a COMMIT between steps 1 and 2, and step 3 causes an error, the system won't be able to completely rollback to before step 1, because of the commit. If you use COMMIT in a half-completed transaction, you are going to have MAJOR problems on your hand.

Just use this rule of thumb: until you fully understand the term "transactional integrity" and what that means for the particular job that you are working on, you are not allowed to use COMMIT. In most cases, like 99% of the time, you do NOT need to issue a COMMIT statement.

This type of thing, as well as every other question you have submitted over the past few days, are questions that your senior should address with you. You need proper guidance, and proper training. If mibuso is your only source of information, you are not getting enough help from your employer. You need to make sure that you have a senior whom you can ask these questions.

Denis_Vitse

Another important thing to notice as well, is that you have this code, for instance:

SalesHeader.GET(HeaderCode);
SalesLine.SETRANGE("Document No.", HeaderCode);
SalesLine.SETRANGE("sthg", "sthg");
SalesLine.FINDFIRST;

... Process what you need to ...

ReleaseSalesDocument.PerformManualReopen(SalesHeader);

SalesLine.VALIDATE(UnitPrice, MyValue);
SalesLine.MODIFY(TRUE);

You will probably get an error message on the MODIFY saying that another user has changed your record since the time you requested it (understand: in your SalesLine variable).
Try to understand why it would send a runtime error.

DenSter wrote:

If mibuso is your only source of information, you are not getting enough help from your employer. You need to make sure that you have a senior whom you can ask these questions.

Tell me about it!
DenSter is absolutely right: if you are in such a case that your company doesn't provide you with learning ways, consider that you'll have to do it by yourself and it's a long and painful path you should try to avoid!

This topic of implicit/explicit db transaction is one particularly hard to apprehend in NAV, if you don't have senior support. Even the NAV courses are not making things clearer, until you finally understand what's going on or somebody teaches you.

Andwian

Thank you all gurus for all of your valuable comments and suggestions.

Denis Vitse wrote:

Another important thing to notice as well, is that you have this code, for instance:

SalesHeader.GET(HeaderCode);
SalesLine.SETRANGE("Document No.", HeaderCode);
SalesLine.SETRANGE("sthg", "sthg");
SalesLine.FINDFIRST;

... Process what you need to ...

ReleaseSalesDocument.PerformManualReopen(SalesHeader);

SalesLine.VALIDATE(UnitPrice, MyValue);
SalesLine.MODIFY(TRUE);

You will probably get an error message on the MODIFY saying that another user has changed your record since the time you requested it (understand: in your SalesLine variable).
Try to understand why it would send a runtime error.

Why it would send a runtime error? Is it because the SalesHeader is still locked, when it run the ReleaseSalesDocument.PerformManualReopen(SalesHeader);?

kine

No, it is not because locks. It is because timestamps. Try to think about this:

  MyRecVar1.GET(somerecord);  //Timestamp=A
  MyRecVar2.GET(somerecord);  //Timestamp=A
  MyRecVar2.MODIFY; //Timestamp change from A to B
  MyRecVar1.MODIFY; //Expecting timestamp=A but it is B => someone changed the record => Error (doesn't matter that same process changed the record)

Andwian

Thanks Kamil for the explanation.

I guess I understand about the timestamp.

Denis Vitse wrote:

You will probably get an error message on the MODIFY saying that another user has changed your record since the time you requested it (understand: in your SalesLine variable).
Try to understand why it would send a runtime error.

As I understand, this will raise error message since there is a MODIFY in the

ReleaseSalesDocument.PerformManualReopen(SalesHeader);

so that that the Timestamp changed, while on the

Andwian wrote:

SalesLine.MODIFY(TRUE);

the system expect the timestamp to be the previous one.

Anyway, if I want to learn about timestamp more, where can I found it? Is it about the DBMS?

kine

Yes, it is about the DBMS.

Andwian

Okay, thank you Kamil, and the other gurus