Transaction Integrity with Connected Systems

Broken pencilWith .NET Interoperability around, it’s very likely you’ll be synchronously calling external web services from C/AL, to exchange data. I won’t go into discussing whether or not this kind of architecture is good (my own position is that it isn’t), you may end up having situations where your C/AL code simply makes a synchronous call to external systems, such as web services.

Any external call is an expected point of failure. An important question you must always have in mind when calling external functions is transaction integrity. When writing code that targets only NAV, the structure of code is largely irrelevant, as long as you are not using COMMITs (which is another thing you should avoid at all costs). However, as soon as you introduce external calls, the structure becomes critically relevant. Critically relevant.

I’ve talked about this during my 2012 NAV TechDays session, and I promised I’d blog about it – so, here it goes.

Let me start with the rule first: whenever making external calls from C/AL, make sure they are the last thing that’s called in the transaction.

Remember this whenever calling external systems synchronously from C/AL.

Imagine you have another system, let’s call it Bongo, that keeps track of customers. When a customer is created in NAV, it must be created in Bongo. When it’s deleted in NAV, it must be deleted in Bongo.

Now you have this code:

OnCreate()

IF "No." = ” THEN BEGIN
  SalesSetup.GET;
  SalesSetup.TESTFIELD("Customer Nos.");
  NoSeriesMgt.InitSeries(SalesSetup."Customer Nos.",xRec."No. Series",0D,"No.","No. Series");
END;
IF "Invoice Disc. Code" = ” THEN
  "Invoice Disc. Code" := "No.";

IF NOT InsertFromContact THEN
  UpdateContFromCust.OnInsert(Rec);

DimMgt.UpdateDefaultDim(
  DATABASE::Customer,"No.",
  "Global Dimension 1 Code","Global Dimension 2 Code");

 

All red marked lines above are possible failure points – at any of those, a runtime error can stop the execution, and the customer would not get inserted in NAV. The structure really doesn’t matter from the transactional integrity point of view, as all of this is a single transaction, and if any line fails, no data is written to the database.

Imagine that you add this Bongo synchronization now. You can do it like this:

OnCreate()

IF "No." = ” THEN BEGIN
  SalesSetup.GET;
  SalesSetup.TESTFIELD("Customer Nos.");
  NoSeriesMgt.InitSeries(SalesSetup."Customer Nos.",xRec."No. Series",0D,"No.","No. Series");
END;

// Bongo >
CreateInBongo(Rec);
// Bongo <

IF "Invoice Disc. Code" = ” THEN
  "Invoice Disc. Code" := "No.";

IF NOT InsertFromContact THEN
  UpdateContFromCust.OnInsert(Rec);

DimMgt.UpdateDefaultDim(
  DATABASE::Customer,"No.",
  "Global Dimension 1 Code","Global Dimension 2 Code");

 

This new line of code is a new failure point, however, this one is a separate transaction, which has no integration with the transaction in NAV. If this line fails, no problem, as it is just another failure point for NAV. However, if this line succeeds, and it fails at the violet colored line above then you have a problem: the customer, that does not exist in NAV, has just been created in Bongo.

The code should look like this:

OnCreate()

IF "No." = ” THEN BEGIN
  SalesSetup.GET;
  SalesSetup.TESTFIELD("Customer Nos.");
  NoSeriesMgt.InitSeries(SalesSetup."Customer Nos.",xRec."No. Series",0D,"No.","No. Series");
END;

IF "Invoice Disc. Code" = ” THEN
  "Invoice Disc. Code" := "No.";

IF NOT InsertFromContact THEN
  UpdateContFromCust.OnInsert(Rec);

DimMgt.UpdateDefaultDim(
  DATABASE::Customer,"No.",
  "Global Dimension 1 Code","Global Dimension 2 Code");

// Bongo >
CreateInBongo(Rec);
// Bongo <

 

The principle is very simple – you first execute everything in the local transaction. If something fails, Bongo is not even called. If all works locally, the transaction is still waiting for Bongo to complete. If Bongo fails, then the local transaction fails because an error occurred. If Bongo succeeds, the local transaction commits.

Always put the code that targets external systems at the end, never at the beginning or in the middle.

And yes – of course – there is no way to absolutely guarantee the transaction integrity between NAV and Bongo, or whatever it is on the other end. Technically, a web service call can fail on the transport layer, after the transaction has been committed to Bongo, and then your C/AL code structure will not help. Without a transaction coordinator layer somewhere between the systems, you are toast. That’s why I said that synchronous calls to external web services (at least for data replication or exchange goes) are not architecturally good.

However, from time to time, you’ll have to do this for whatever reasons. When you do it, remember these five words: external calls at the end.

3 thoughts on “Transaction Integrity with Connected Systems”

  1. Trying to do “real time” insert into a third party app. on the Insert() trigger will indeed be a killer 😉

    I would personnaly just do a simple insert a record into a “Bongo Message Entries” without further processing, and have a NAS process that scan & push uncomitted records from “Bongo Message Entries” into Bongo App., to not wait endlessly for an ACKnowledge message from Bongo …

    +1 For exernal calls at the end otherwise you might get a lot of out of synch. records between NAV and Zi-Bongo System! 😉

Leave a Reply