Long running client controlled (stateful) transactions and the query service.

How to use stateful transactions (long running transactions)?

Firstly... stateful transactions are considered a 'no no' in large n-tier setups. The reason is that stateful transactions hold up ressources both on the db server and on application servers, which is bad for scalability and ressource reuse.

However there are small setups where stateful transactions (or other type stateful operations) are required.

This can be done using a stateful service.

One way is to create a query service via the service wizard, put a checkmark in the Stateful, ConstructService, DestroyService, InitializeState and FinalizeState checkboxes (on 2 pages in the wizard).

The checkmark in the Stateful checkbox makes the service a stateful service. I.e. a client can hold one or more instances of these for as long as the client decides.

When the service has been generated, there will be a couple of empty methods in the generated code, namely the InitializeState etc. methods.

In the public section of the service class declare a connection variable:

  MyConnection:TkbmMWCustomConnection;
  MyTransaction:TkbmMWTransaction;

In the ConstructService method write the following:

  MyTransaction:=TkbmMWTransaction.Create;
  MyTransaction.StartTransaction;

In the DestroyService method write the following:

  MyTransaction.Free;
  MyTransaction:=nil;

In the InitializeState method write the following:

 // Wait max 10 secs for a connection from the conneciton pool.
  MyConnection:=centralmodule.theconnectionpool.GetBestConnection(true,-1,10000);
 if MyConnection=nil then raise Exception.Create('Cant initialize state for stateful query service.');
 MyTransaction.AddConnection(MyConnection);

This should obtain a kbmMW database connection which you can use later on. The connection is already locked for your use  when you get it so no other thread or service will be able to use it until you unlock it.
The connection is added to the kbmMW transaction and as we specified the transaction to already being started, kbmMW will automatically ensure that a true database transaction is started on the database connection.

centralmodule is the datamodule/form containing your database connection pool. theconnectionpool is the database connection pool itself.

In the FinalizeState method write the following.

if MyConnection<>nil then
begin
  MyTransaction.DeleteConnection(MyConnection);
  MyConnection.UnlockConnection;
end;
MyConnection:=nil;

This ensures that the connection is taken out of our transaction when the client releases the state of the service, and the connection is then released back to the connection pool for reuse later on.

Next you need to write some code for the event handler OnAuthenticateQuery:

procedure Tyourservice.kbmMWQueryServiceAuthenticateQuery(Sender: TObject;
  Query: TkbmMWCustomPooledCursor; ClientIdent: TkbmMWClientIdentity;
  var Permissions: TkbmMWAccessPermissions);
begin
  Query.Connection:=MyConnection;
  Permissions:=[mwapExecute];
end;

This ensures that the query component or stored procedure component that the client is about to use, will use a specific connection, namely the one that participates in the transaction we have defined.

At any time you can choose to either MyTransaction.RollbackTransaction or MyTransaction.CommitTransaction. Remember though that you need to start the transaction again later on before you do any new updates/queries if they should be performed in your specific transaction.

On the client side, you will need to get the value of ActiveClient.StateID after the _first_ call to the statefull query service, and store that value in some variable in the client.
Set all client query components ActiveClient.StateID to the value you have stored when you want to access that specific statefull query service.
You can read up on the statemanagement in the 'Custom services' whitepaper for more information about it.

Before the client exists, make sure to release all held statefull services. This can be done by doing:

yourclientquery.Client.ReleaseStateID(thestateidyoustored);

You could use any other TkbmMWSimpleClient/TkbmMWPooledSimpleClient etc. instance to do that. However one will automatically be instantiated for you for each client query component and thus you can reuse that without problems.

--

best regards
Kim Madsen
kbm@components4developers.com
www.components4developers.com

The best components for the best developers
kbmMW - kbmMemTable - kbmWABD - kbmX10