In the previous issues, we covered the basic points of creating an application using the framework. This issue will be the first of a series about handling transactions.
Remember that you can download the latest version of the TierAdapter and of the complete Demo application, including source code, from our framework's main page at SourceForge.
Introduction to transactions
It is now time to start seeing how the framework allows us to handle transactions. For this purpose, I will describe certain situations we might encounter when we need to open a transaction.
Any database engine, in order to be worthy of receiving this designation, should offer the possibility of handling transactions. The appropriate handling of transactions (among other things) allows us to guarantee the integrity of the stored data. For those with less experience in this subject, I shall mention a typical example used in this case, brief and simple.
Let's assume we have to carry out a money transfer between two bank accounts. The user of the system must specify the date, the amount to be transferred, the bank account where the amount is debited, and finally the bank account where it is credited. Upon confirming the operation, two changes must be stored, the debit and the credit. Now, what would happen if just after storing the debit, and before storing the credit, the operation is interrupted for whatever reason? In case you don't have the possibility of opening a transaction that spans both changes, we might end up having no money in either of the bank accounts, an unpleasant situation if ever there was one. How does a transaction help? The transaction allows us to ask if everything finished OK, and confirm the changes; otherwise, revert them, and leave everything as it was before.
Let's see the representation in pseudo-code:
InitiateTransaction() TRY ProcessDebit() ProcessCredit() ConfirmTransaction() CATCH RevertTransaction() ENDTRY
Usually, database engines automatically place the INSERT, UPDATE and DELETE operations within what is usually called an implicit atomic transaction. If we need to include more than one of these operations in the same transaction, we can manually open another (explicit) transaction that spans them. In this case, we are nesting transactions.
Transactions within a tiered model
In an application designed with a tiered model, as in TierAdapter, the tier that should know how to implement a transaction model is the one that is closest to the database engine.
In our case this is the DataTierAdapter. If we see the code, we will find some methods designed to open transactions, (TransactionBegin), to confirm them (TransactionEnd) and to revert them (TransactionRollback). All this is managed at the level of an entity in the Put method. Now, if it is necessary to span more than one entity within the same transaction, things start to get complicated. In this case, the tier that should know about what entities are spanned by the transaction should be the business tier (BusinessTierAdapter), therefore, it must talk with the data tier in order to ask it to start and close this type of events.
With the tier model, a large variety of possibilities is opened, which we can consider. Depending on the way how the components of the different tiers are distributed, we should implement the transactional model in different ways.
One of the possibilities is to include the different tiers in a single executable (monolithic EXE). Thus, we can interact, without restrictions, between the different tiers.
Something similar happens if we include the data and business tiers of all entities within a single DLL, leaving the user tier and the forms within the executable.
However, it is possible that the data tier and the business tier are in different DLLs, or even, that each entity has its own DLL for its data tier, and another DLL for its business tier. TierAdapter allows us to adopt any of these alternatives. We will see how to do this in the following articles.
Transactions within a disconnected model
Since TierAdapter implements a disconnected model, which doesn't maintain its state, let's see some of the things that must be considered here. Simplifying the concept (quite a lot), we can say that in a disconnected model, for any operation to be done against the database, a conection to the database is opened, the operation is carried out, and the connection is closed again, as quickly as possible. The result of this technique is that we obtain a highly scalable model, since the number of users which have open connections at any one time will be considerably less than with a permanently connected model.
Among other things, transactions have to live within a single connection. Therefore, when we need a single global transaction to span more than one entity, we have to keep the connection open during the operation. We mentioned before that this type of operation, which involves more than one entity, is managed at the business tier, therefore, we should provide it with the required skill to deal with this kind of situation. This applies if the components are in the same EXE or DLL. Otherwise, the only way we can implement the model of distributed transactions is the one offered by COM+, through the Distributed Transaction Coordinator (DTC).
Working with data in different database engines
In TierAdapter, we can handle the possibility of storing data in different databases and even in different database engines. As an example, in the Demo we can see that the entity "User" persists data in the "taSystem" database, which is a native Visual FoxPro database (DBC), wherease the entity "Products" persists in the "NorthWind" database, either in its DBC or SQL-Server versions. It can happen that we need to span a single transaction over two or more entities in different databases; this is another possible scenario which we may have to consider. Here, again, we must use the services of the Distributed Transaction Coordinator (DTC) of COM+.
Different ways of connecting to the database from VFP
If, to the already considerable (for my taste) range of possibilities we add yet other alternatives, perhaps you will feel (as I do) that this is getting too complicated, but unfortunately, any of the possibilities of connecting to different database engines, be it the OLE-DB provider for VFP, the OLE-DB provider for SQL Server, etc., add new variables to the equation, since the possibilities offered by every one of the different providers for different database engines are quite dissimilar.
More information...
You can read more about these subjects in any of these links, from recognized experts:
Conclusion
In this article I tried to enumerate and briefly explain some of the various alternatives which we must consider when we manage transactions in an application. In the next article, we will start seeing every one of the different cases, and how to handle them with the framework.
