Asta Multi-Threaded DB Server

In order to handle a large amount of concurrent users, AstaServers can multi-thread their database sessions. The AstaServerSocket is non-blocking and event driven so that client requests can always be handled. Performance issues are normally related to bandwidth considerations and a properly designed client application is critical if you want your AstaServer to respond promptly to requests.

In order to multi-thread any database application, each user must have their own connection or Session to the database and their own copy of the component that is to be used in conjunction with this session, usually a Query or Stored Procedure component. Using the BDE, each client needs a TSession and their own TQueries. Using ODBC, each client would need their own Handle to a Database (THdbc) and their own Query component (TOEDataSet using ODBCExpress).

AstaServers, in order to allow for greater scalability, create and maintain a pool of Sessions that are available on an as-needed basis for connected clients. A TAstaSessionList is created and acts as a Resource Dispenser that supplies client sessions upon request. Instead of creating a session for each connected client, AstaServers pool the sessions and dispense them when they are needed. In order to achieve maximum scalability, resources should be acquired as late as possible and released as soon as possible. State should only be maintained when only absolutely necessary. When clients request a Session from a the AstaSessionLIst , and there are no sessions available, another session is dynamically created and added to the pool.

AstaServers can also maintain client Database State while the client is connected to the server. This model will not scale as well, but there may be certain occasions where you would want a client to keep a persistent database connection. For example, you might want a client to use a real database name and password to keep their database security level applicable to them or when you want to open a server side cursor and fetch partial rows from the server.

Property ThreadingModel:TAstaThreadModel

tmSingleSession

This is the default model and should be the fastest for small numbers of users or larger numbers of users that don't have excessive concurrent activity.

tmPooledSessions

This is a resource conscious scaling model. Use the DataBaseSessions:Integer property to set the number of sessions that are created at server startup. All connections are created at server startup, and client requests are paired with sessions from this pool on an as-needed basis. If you are trying to support a large number of users, this is the model to use. The pooled number increases dynamically if there is a demand.

tmPersistentSessions

This allows each client to connect to a Session when the client connects to the server. The sessions are persistent. Used in conjunction with the TAstaClientSocket Login property, you can use this threaded model to allow users to login with their actual database login, and even allow each client to access a separate Alias. When the client connects, the socket triggers an event (DBLoginEvent) to create a Database session. Any subsequent client database access will pair the incoming socket with the session chosen at startup, within a spawned thread. When the client disconnects, the session is disposed of.

When an AstaServer runs in this mode, AstaClients can fire a server query and keep a cursor open on the server. Instead of fetching all the records in the query the client can request X number of rows via the PacketSize:Integer property. See the Packet discussion in the TAstaClientDataSet.Doc.

There are three elements of a Multi-Threaded Database AstaSever.

• Set the ThreadingModel Property. If the tmPooledSessions is chosen, then you must set the DataBaseSessions property as to the number of sessions to be created at server startup.
• Code the ThreadedDBSupplySession Event.
• Code the ThreadedDBSupplyQuery Event

When an AstaServer starts up, the method CreateSessionsForDbThreads is called. If the DataBaseSessions property is greater than 0, and the ThreadedDBSupplySession Event and the ThreadedDBSupplyQuery event is coded, than an AstaSessionList can be created that will act as a pool of connections to the database. If you choose to thread the server, these two events must be coded! Use the Function UseThreads: TAstaThreadModel to verify at runtime which threading model is in effect.

DataBase Sessions

The DatabaseSessions property of the TAstaServerSocket determines the number of sessions or connections that will be pooled by the AstaServer and will be created at startup. By creating the sessions before the server starts to run, they will be available when needed.

function ThreadedDBSupplySession(Sender: TObject): TComponent;

Under the BDE, this method is coded to supply a TDatabase component along with a TSession to ensure that any Query or TStoredProc used under a multi-threaded BDE AstaServer will have it's own connection. Using ODBCExpress, a THDBC component is created. Both need to have the information available so that they may connect to the DataBase when the application starts up so as not to slow down performance when they are actually used. See the example Servers supplied with the AstaComponents for code implementations using the BDE and ODBCExpresss.

function ThreadedDBSupplyQuery(Sender: TObject; DBAction: TThreadDbAction; CreateNew: Boolean): TComponent;

When an AstaClient is connected to an AstaServer and it needs to do a select or an exec or to execute a stored procedure, this routine will be called to create a new component to be used in the client process. Under the BDE, a TQuery is used for selects and execs and a TStoredProc is used for stored procedures. Under the ODBCExpress implementation, a TOEDataSet is created for both. The DBAction indicates the type of process that the Component will be used for.

The ThreadedDBSupplyQueryEvent returns a TComponent. This allows you to return a single component like a TQuery for a specific action, or to use a component that was created in a DataModule. In the AstaBDEServer, TQueries or TStoredProcs are created as needed. Since individual components are created on the fly, they must be disposed of by the ASTA server when they are done their tasks. In this case, make sure the DisposeofQueriesForThreads:Boolean property is set to True. This will tell the ASTA server to dispose of these components. In the AstaODBCExpress Server, a complete DataModule is created for each thread. In this case, the DispsoeOfQueriesForThreads would be set to false, as any components that are part of a DataModule will be disposed of when the DataModule is destroyed.

NOTE: When ASTAServers utilize the tmPersistentSession Model, and packet fetch requests are received from AstaClientDataSets, components must be created on the fly and not used from datamodules. In this case, the incoming TThreadDBAction will be set to ttPacketSelect. See the AstaODBCExpress server for an example of this. Since the Component must be kept open to be able to return further packet requests, it must be created on the fly.

A TThreadDBAction is an enumerated type found in the AstaDBTools unit consisting of the following types

TThreadDbAction = (ttSelect, ttExec, ttStoredProc, ttMetaData, ttBlobFetch,ttTransaction,ttTransactionStart,ttCustom,ttPacketSelect);

There may be occasions where you need to accomplish a database task within a thread and stream back some data, other than from a select statement. To provide for this, AstaServerSockets have an event called ThreadedDBSupplyCustomQueryEvent.

Function ThreadedDBSupplyCustomQueryEvent(Sender: TObject; DBAction: TThreadDbAction; CreateNew: Boolean; DataBaseStr, SQLString: String): TComponent;

Coding this event, and returning a TComponent (eg TQuery or TStoredProc) will allow you to thread custom database events. The following is taken from the AstaBDEServer and shows how to code a "GetTables" call that will return BDE table information when any TAstaClientDataSet sends the SQL string of "GetTables".

function TForm1.AstaServerSocket1ThreadedDBSupplyCustomQueryEvent(
     Sender: TObject; DBAction: TThreadDbAction; CreateNew: Boolean;
     DataBaseStr, SQLString: String): TComponent;
begin
   result := nil;
   if not createnew then exit;
   if instring('GetTables',SQLString) then
   begin
     result := TAstaBDEInfoTable.Create(nil);
     with result as TAstaBDEInfoTable do
     begin
       SessionName := TDataBase(TAstaThread(Sender).Session).Session.SessionName;
       DataBaseName := TDataBase(TAstaThread(Sender).Session).DataBaseName;
       BDEInfo := BDEOpenTables;
     end;
   end;
end;

This creates a custom component, in this case a TAstaBDEInfoTable. The Sender:TObject is a TAstaThread which has a session property that can be linked to the newly created table. Notice the incoming SQLString that you can key off of to decide if this is the query you want to intercept within the thread. See the source for the AstaBDEServer for the full implementation of the 'GetTables' custom event.

Custom Database Logins

There may be times when you want to use the tmPersistentSession threading model to allow for connected clients to actually login to a specific database. Used in conjunction with the TastaclientSocket. AutoLoginDlg property, a user can be validated using the actual database login. The following code is an example from the AstaBDEServer that is distrubuted with ASTA components and shows the UserName, Password and AppName that comes in from a TastaClientSocket login. The AppName could be used to trigger a different alias to be logged into on the server. See the Login tutorial for an example of login in remote users. It is your responsibility to set the Var Verified variable to true for the login to be successfully relayed to the client application.

function TForm1.AstaServerSocket1ClientDBLogin(Sender: TObject; UserName,
   Password, AppName: String; var Verified: Boolean): TComponent;
var
   sess: TComponent;

     function StripSpaces:String;
     begin
       result:=UserName;
       while pos(#32,result)>0 do
         delete(result,pos(#32,result),1);
     end;

begin
   result:=nil;
   sess := TDataBase.Create(TSession.Create(self));
   with sess as TDataBAse do
   begin
     Session.SessionName := 'Session' + UserName;
     DataBaseName:= 'db ' + UserName;
     Name:= 'DataBase_' + StripSpaces;
     // need a unique name??
     AliasName := MainDataBase.AliasName;
     loginPrompt := False;
     params.assign(maindatabase.params);
     TransIsolation := MainDataBase.TransIsolation;
     Connected := True;
     result := Sess;
     Verified := True;
   end;
end;

Threading Command line Switches

To allow you to start AstaServers up in different threading models, without recompiling them there are some command line switches available.

• Sessions=XX where XX is the number of Database Sessions to create at server startup
• PersistentSessions which forces the ASTA server to provide persistent database sessions for each connected client. Note: if both command line switches are used, PersistentSessions will override the Sessions switch.
• SingleSession trumps all of the other switches and forces the ASTA server to run without threads (sets databasesessions to 0 and the threading model to tmSingleSession)

Examples:

AstaBDEServer.exe Sessions=12
AstaBDEServer.exe PersistentSessions
AstaOdBCServer SingleSession