How can I wrap multiple client function calls in a single transaction?
Basically I want the client to call a series of discrete functions on the server but, if any of them fail, I need to roll back the lot.
if I correctly understand you, you can create StartTr / CommitTr / RollBackTr methods on server-side so client can call them as
lserver.StartTr;
try
// do some things
lserver.CommitTr;
except
lserver.RollBackTr;
end;
Ah ok I’ll give that a try thanks. I’ve used transactions within the server logic for a single client function call but never tried encasing multiple client calls within a single transaction before.
1 Like
Having some basic problems with this.
I’ve just tried wrapping multiple updates in a transaction on the server itself and it doesn’t appear to work.
What I did was create some test code which appends a new record using TDAMemDataTable components. I insert two records but, critically, call ApplyUpdates after each one to process them separately. I’ve engineered it so the first insert will succeed but the second will fail.
If I execute this then, as expected, the second insert throws an error but the first record is inserted successfully. I then tried wrapping both inside a transaction with Connection.BeginTransaction and Connection.RollbackTransaction. I would expect the error to still be thrown but the first record not to be inserted as the whole thing would be rolled back but the first record is still there. What am I doing wrong?
Basically I have some complex server-side processing which requires multiple ApplyUpdates calls and I need to ensure all of it is wrapped in a transaction so that, if any of these updates fails, all of them are rolled back to ensure integrity.
have you implement custom transaction logic on server side?
You need to implement OnUpdateDataBeginTransaction / OnUpdateDataCommitTransaction / OnUpdateDataRollBackTransaction and return false for aUseDefaultTransactionLogic when there events are fired.
your handlers for these events should be set in StartTr and unset in CommitTr/RollBackTr.
see also this thread - Transaction control over Multple Server Side TDAMemDataTables , it has similar problem.
So, if I follow you correctly, you mean to disable the built-in transaction logic by setting aUseDefaultTransactionLogic to false in each of the three handlers, then manually start/commit/rollback the transaction myself using the Connection object? I’ll give that a try.
yes, you are right.
also this should revert default behavior when custom logic isn’t needed.
Yes, I’ll only need to do this for certain operations, at other times I want the standard handling to operate as normal.
Can I disable/override these handlers from inside a service function? Presumably the transaction won’t begin until I actually do some data access, so I can modify the behaviour of the handlers before that time?
you can read aUseDefaultTransactionLogic value from session value like:
aUseDefaultTransactionLogic := Session['MyValue'];
so you can set it in any place of your code
Ok thanks let me have a play with it and see how I get on
No it’s still not working.
I need to do this on a per-function basis within the service. Most functions are fine with the default processing but some need to encapsulate many operations within a transaction.
I thus tried adding a boolean variable to the service class which, if set to true, causes these three handlers to set aUseDefaultTransactionLogic to false. My test function then sets this variable to true as the first thing it does, then calls Connection.BeginTransaction, performs the data access, then calls Connection.RollbackTransaction.
The rollback here appears to be having no effect. The inserts remain in the database. Not sure where I’m going wrong.
try to store variable in Session instead of local variable because server’s instance is created for each request from scratch if default class factory is used.
I can’t do that though because it’s not session-specific. If a single client happened to call two service functions simultaneously from different threads, where one function used the default logic and the other didn’t, it wouldn’t work.
I am using the default class factory so a fresh instance of the service class is created for each client call. Surely using the system I described should work as each instance of the class has its own variable so I could have multiple instances running simultaneously with some using default transaction logic and others not.
I’ve just stepped through it in the debugger whilst keeping an eye on Connection.InTransaction and this appears correct. When I call Connection.BeginTransaction this changes to True, then remains True through the ApplyUpdates calls, suggesting the default logic is indeed disabled, then changes to False after I call Connection.RollbackTransaction. I don’t understand why the transaction isn’t actually being rolled back in the database and the inserted record remains.
Or is the issue that, when I call the ApplyUpdates on the data table, it’s actually creating another instance of the service class to do this processing, not using the one from which I’m making the call. If that’s the case the obviously this second instance would use the default transaction logic.
it may depend on driver and database you are using.
e.g. for MSSQL, you can use SQL Server Profiler and see what actually is happened.
Well I’ve just tried using a session variable as you suggested and the same result - the record remains in the database.
I’m using MSSQL with FireDAC. I guess I could try seeing what’s going on but none of it makes any sense at the moment.
Something else I don’t understand. Assuming what I said above is correct and additional instances of my service class are being created for the data inserts, then even if I could make this work somehow and get these additional instances to also disable the default transaction logic, surely there’s no guarantee that they’d use the same connection?
I’m using connection pooling so, if I’m manually controlling the transaction from my main code then this is against the connection object that the main service class instance has acquired. If additional service class instances are created by the insert processing then isn’t there a chance they could obtain a different connection object and thus wouldn’t be within the transaction I’m controlling?
I’m getting very confused by all of this. All I want to do is encapsulate more than one call to ApplyUpdates in a single transaction, so if any of them fail, all changes are rolled back. Surely it can’t be this difficult but I can’t see any way of making this work.
you can store connection and reassign it again for specific instance. of course, it won’t work if server was accessed from two backgrounds threads of one client
Now I’m completely lost. Can you provide a working example of how to do this as nothing I try is working.
I’ve also read the other topic you linked and it appears he was having exactly the same issues as me - just not working
You mentioned in that thread about ensuring any other service instances are also disabling the default transaction logic but, having done that by setting a session variable, I still can’t make it work.
Ah I think I may have cracked it. My update logic is encapsulated within data modules which I instantiate as required, such as within a service function. When created, these data modules are creating a fresh service instance and potentially using a different connection.
I’ve come up with a system whereby I can instantiate these data modules but pass them the existing service instance to use, along with its connection, which should alleviate the issue. I’ll have a play with it and see if I can make it work.