Implement Transactions (For pre Dynamics CRM Online Update 1 (7.1.x)

[dazinator]

Dynamics CRM Online Update 1 (7.1.x) now has support for transactions using the ExecuteTransactionRequest

However for older editions of Dynamics, there is no such request. This issue is for a way of achieving transactions for those systems.

Idea is:

1. Beginning an Ado.Net Transaction
   1. Starts an Async Workflow running in CRM (aka TransactionWorkflow) - https://msdn.microsoft.com/en-gb/library/gg309600(v=crm.5).aspx
   2. Sets up a listener for the Azure Service bus (ASB)
2. The now running TransactionWorkflow enters a type of "REPL" loop, posting / polling to the ASB to retrieve it's next command which is either:
   1. A command it needs to execute within the currently running transaction against CRM (which it receives in the form of serialised organisation service requests)
   2. A Rollback request, to rollback the current transaction
   3. A Commit request, to commit the current transaction

As the TransactionWorkflow executes commands within the database transaction it posts the results to the ASB in the form of the OrganisationServiceResponses - or in the form of an exception if there was a problem processing the command and the transaction had to be aborted.

When the TransactionWorkflow recieves a Rollback request, it will throw an exception in order to trigger CRM to rollback the current transaction. It will also attempt to post a confirmation that the rollback was performed to the ASB

When the TransactionWorkflow recieves a Commit request, it will exit out of the REPL loop and return such that the transaction is committed, It will also attempt to post a confirmation that the commit was performed to the ASB.

1. When an ADO.NET command is executed, if there is an active Ado.Net Transaction, rather than translating the SQL and executing it against the org service directly on the client, instead it waits for the TransactionWorkflow to post a request for the command to be executed via listening to the ASB for that request, and responding with the current command text (or org request)

It then listens for a message from the ASB containing the results (posted from the TransactionWorkflow) which it then deals with in the normal way as if the results had been returned via normal execution on the client.

It can also recieve a result from the TransactionWorkflow (via the ASB) to say that an error occurred processing the command on the server, in which case it throws this exception on the client.

1. Lastly the ADO.NET transaction is either then Committed, or Rolled back. Both these events are sent as commands to TransactionWorkflow via the ASB and the TransactionWorkflow does the appropriate thing. If the connection is closed or disposed before either the Commit or Rollback methods have been executed, the transaction is rolled back.

[dazinator]

Psuedo code for the TransactionWorkflow

start transaction -- >  plugin instance run
                        poll for operation loop
                RecieveOperation
                    ExecuteOperation
                    PostResultsToASBListener
                    Continue
                                CommitTransaction
                    Return
                RollbackTransaction
                    Throw TransactionRollbackException("transaction rollback")

            CATCH TransactionRollbackException
                try
                    Throw new InvalidPluginExecutionException("transaction rolled back")                        
                finally
                    try
                        PostRollbackSuccessfulMessageToASBListener "transaction successfully rolled back"
                    catch ex --> swallow

            CATCH Exception     
                try
                        PostServerExceptionToASBListener "Exception Occurred On Server - transaction will be rolled back."
                    catch ex --> swallow

                Throw new InvalidPluginExecutionException(Ex)

[dazinator]

Useful information about setting up ASB for Dynamics CRM: https://msdn.microsoft.com/en-us/library/gg309340.aspx

[dazinator]

Useful info: https://msdn.microsoft.com/en-us/library/gg327941.aspx

also

Regardless of whether a plug-in executes synchronously or asynchronously, there is a 2 minute time limit imposed on the execution of a (message) request. If the execution of your plug-in logic exceeds > the time limit, a System.TimeoutException is thrown. If a plug-in needs more processing time than > the 2 minute time limit, consider using a workflow or other background process to accomplish the intended task

For this reason, a Workflow has been chosen in which to execute the transaction, rather than a plugin.

[dazinator]

Also regarding the ASB, Only a two-way or REST listener will return a string back to the caller.

https://msdn.microsoft.com/en-us/library/microsoft.xrm.sdk.iserviceendpointnotificationservice.execute.aspx

I will need to return results back to the caller so I should implement a two way or Rest listener.

[dazinator]

It looks like I can execute the workflow using this: https://learn.microsoft.com/en-us/dotnet/api/microsoft.crm.sdk.messages.executeworkflowrequest?view=dataverse-sdk-latest

Maybe in addition to process above:

1. CrmAdo can insert a custom entity at the start (and outside the transaction) to represent the transaction request - it would get the unique Id.
2. The custom async workflow could be started to process the transaction based on this entity Id.
3. The async workflow proceeds with the repl described above. Upon commit it also updates the custom entity representing the transaction to "suceeded".

Because crmado is sending the transaction id (from inserting custom entity initially), if there is a glitch and CrmAdo needs to retry the transaction, the workflow can check state of the custom entity for the transaction id and if it has already marked it as succeeded it knows this is a duplicate execution. For idempotent behiour it would be nice if it could mimic previous results for eventual consistency however this is where service bus comes in - the previous results should be on a message queue for the client to process in this case. The workflow could therefore signal the trains as a duplicate back to the caller and the caller can check for and process outstanding messages on the queue.