I’m having a strange issue with transactions. I’m using Delphi 10.2.3 with FireDAC against an MSSQL database.
I have a delta which I’m applying with an ApplyUpdates call. Obviously all of the delta/change processing on the server takes places within a transaction. This is fine.
However, whilst processing this update, my business processors may need to execute a schema command. This command is executed within the context of the service instance (by assigning the TDALocalCommand’s ServiceInstance property) so that it would operate within the context of the existing service instance and its transaction.
What appears to be happening is that this call is totally screwing up the transaction as it’s wrapped in a begin/commit pair. Tracing it in SQL profiler, it appears this commits the “main” transaction and the remaining changes are then processed outside of the transaction.
Can you confirm what’s going on here? Surely the command should simply execute within the context of the existing transaction, not attempt to start it again and then commit it.