This 5 minute presentation shows the basics of the package

How to protect sets of operations by transactions <- For Henry Cesbron Lavau (TeamB-Fr)
->


Introduction
1. Transactions with InterBaseExpress (IBX)
Transactions tiered
2. Transactions with dbExpress
3. Transactions with ADO (dbGo)


Introduction


You may have heard of transactions: there are real estate, securities and .. . others. Most DBMS (database management systems) worthy of the name implement transactions. General sense, we note that there is an exchange between (at least) two actors who concluded with an agreement. The exchange aims to establish the agreement. If the agreement is concluded, the purpose of the exchange is enabled (the database is updated), otherwise each returns to initial conditions (the data are restored to their initial values).

The transaction mechanism is first used by the database itself, internally, to maintain consistency: for example, when you asked for an index be maintained on a column of a table, each update, a properly constructed DBMS:

  1. Updates the data
  2. Updates the index.
If a problem arises during the update of the index, the update of the data is canceled: either all operations are carried out, or none.

This mechanism is interesting course for procedures performed at the application level. For example, in a deliberately simplified diagram, add a line to the invoice if the item is removed from the stock. Suppose Article lack of stock, the line must be removed.

Let's see this in detail:

  1. Reservation stock
  2. Sign the invoice line
  3. Acceptance by the client of the line given the quantity and price
  4. Effective Output of stock
In single-user, no particular problems. However, if working in a network, another operator may be trying to sell the same item. Which can give the following sequence

< td class="column"> Acceptance by the client of the line given the quantity and price
1 customer Step 2 ° client
Reservation stock for 1 customer 1
2 Reservation stock for the customer 2
Registration the invoice line 3 Registration of invoice line
Acceptance by the client of the line given the quantity and price 4
Effective Output of the stock: the stock is decremented 5
6 effective output Attempted stock but the stock is insufficient
The stock was permanently assigned to the 1st customer in step 5 and, in step 6, the 2 ° client is more what was yet available in step 2.

Of course you could have made the allocation from step 1. But if the 1 client, given the price, refuses the order, the sale is lost for the client 2 since the stock affected as soon step 1 is not available to him Step 2.

To avoid this, operating sequences are grouped as follows:

1 customer Step 2 ° client
Transaction Start 1 Transaction Start
Reservation stock for 1 customer 2
Registration of invoice line 3
Acceptance by customer the line given the quantity and price 4
Effective Output of the stock: the stock is decremented 5
Transaction End < strong> 6 Transaction End
Transaction Start 7 Transaction Start
8 Reservation stock for the customer 2
9 Registration of invoice line
10 Acceptance by the client of the line given the quantity and price
11 Effective Output of stock : the stock is decremented
Transaction End 12 Transaction End
Each group will be isolated in what is called a transaction .
The transaction is visible to all the clients is indicated by the mark start and end of the transaction in each column.

We note that this is the level of the application must be delimited transaction: it is the developer who defines the operations that constitute a transaction.

In practice, you should avoid putting in the transaction of shares pending the decision of customers.
In addition, the DBMS use methods more sophisticated than transaction serialization as induced the table: wait to have finished the 1 ° client to process the 2 ° client introduces very penalizing bottlenecks. But for logical understanding of what happens, the presentation above is sufficiently simple and complete.

In the SQL language, one has to start an order and two orders to complete a transaction.
For the beginning of transaction is used SET TRANSACTION , while for the end of the transaction, we use COMMIT (or ROLLBACK ) depending on whether the valid (or invalid) the changes the base made during the transaction.
These orders will be implemented by the programmer or through components that will make it for him.

We will see the application in 3 cases


1. Transactions with InterBaseExpress (IBX)


Before you start talking about our transactions, we must emphasize that all operations carried out with IBX are already enrolled in a basic transaction. This basic transaction has the effect show the database to each as if it were the only user.
In these conditions, it is unclear how we could implement fine transactions, since all what does a user read and write is isolated in basic transaction.
Well this is possible if we properly setting the fundamental transaction by giving the Params of < strong> IBTransaction1
values ??

IBTransaction1
Params ...
READ_COMMITTED rec_version nowait

READ_COMMITTED allows the transaction to see the values ??validated by other transactions (including other users) and even update those values his turn.

TIBTransaction provides methods that execute SQL commands seen above. These are

 
StartTransaction Commit Rollback
However the The disadvantage of Commit and Rollback is that they require to re-activate the transaction. This is expensive for a simple upgrade.
Fortunately, we have available a variation of these commands that keeps the transaction open. These are


CommitRetaining RollbackRetaining
Let's see how our transaction program

Let a Data Module component and a TIBDatabase TIBTransaction

We link the two components indicating the properties

=IBDatabase1.DefaultTransaction IBTransaction1 IBTransaction1 .DefaultDatabase=IBDatabase1
and put IBTransaction1.Params as described above in READ_COMMITTED , rec_version and nowait

Note We detail here what is related transactions .
For those who want an introduction to the use of IBX components, you should tutorial .

Let validation sequence of the command line ( can be in the event handler of a button on the Form ).

procedure ReservationDeLigne;
begin
try

IBTransaction1.CommitRetaining; //Beginning of transaction
if StockSuffisant Then
InsererLaLigne
else
Abort;
< span class="delphi_keyword"> if AcceptationDeLaLigne Then
SortieEffectiveDuStock < br/> else
Abort;
IBTransaction1.CommitRetaining; //Normal completion of the transaction
except
IBTransaction1.RollbackRetaining; //abnormal end of the transaction
end
end;
This method is actually a series of validations of the fundamental transaction. The first CommitRetaining is designed to mark the starting point which we would if RollbackRetaining .


Transactions tiered


When writing of transactions, these can later be included in a global transaction.
For example, we have a transaction for a line of invoice.
But it is quite possible to also have a global transaction in the invoice in which the transactions occur at each line.
We'll set up a method taking this into account in a very interesting way.
Writing differently above procedure, we do not even will need to change the day she is caught in a global transaction ( and this regardless of the number of global transaction that we would have to follow suit later ).

First we add the NiveauDeTransaction read-only in the DataModule:

private
FNiveauDeTransaction: Integer;
public
property NiveauDeTransaction: Integer read FNiveauDeTransaction;
This will allow us to manage the internal DataModule in transaction level and know outside DataModule whether or not we are in a transaction.

Now, to handle our transactions, at any point in the application, we will create three public procedures in the DataModule.

public procedure TransactionStart; procedure TransactionCommit; procedure TransactionRollback; implementation procedure TransactionStart; begin if =0 FNiveauDeTransaction Then IBTransaction1.CommitRetaining; Inc. (FNiveauDeTransaction); end procedure TransactionCommit; begin if FNiveauDeTransaction> 0 Then Dec (FNiveauDeTransaction); if =0 FNiveauDeTransaction Then IBTransaction1.CommitRetaining; end procedure TransactionRollback; begin if FNiveauDeTransaction> 0 Then IBTransaction1.RollbackRetaining; FNiveauDeTransaction:=0; end
Of course, we now forbid to use IBTransaction1 . To manage our transactions, we now will appeal exclusively to the 3 procedures above.

Note As you can see, the procedure TransactionStart does not implement IBTransaction1.StartTransaction as one might expect. Indeed, we do not create new transactions. But we rely on the basic transaction (see above). Each Commit acts as a point of validation of this transaction. That is why it is found in TransactionStart procedure; and TransactionCommit procedure;

For example, booking the invoice line now reads:
function ReservationDeLigne: boolean; begin Result:=True; try DataModule1.TransactionStart; //Beginning of transaction if StockSuffisant Then InsererLaLigne else Abort; if AcceptationDeLaLigne Then SortieEffectiveDuStock else Abort; DataModule1.TransactionCommit; //Normal completion of the transaction except DataModule1.TransactionRollback; //abnormal end of the transaction Result:=False; end end;
Now assume that the invoice line will be definitively confirmed when the invoice is validated as a whole.
For example, if we buy a prosecutor to ask with glue, or we take all or nothing.
The client may also decide to cancel, given the price.

So our basic transactions are now included in a global transaction level billing. ( We said at the beginning that we simplify by having a single operation for the order and the invoice ).

function Billing: boolean; begin Result:=True; try DataModule1.TransactionStart; //Beginning of transaction repeat if not ReservationDeLigne Then Abort; up to DerniereLigne; DataModule1.TransactionCommit; //Normal completion of the transaction except DataModule1.TransactionRollback; //abnormal end of the transaction Result:=False; end end;
We note that our transactions at the lines have not been changed when we embraced them in the transaction at the bill.
Similarly, if we must embrace one day the transaction level of the bill in an overall transaction, our procedures will not be changing.

One last word about the impact of controls Retaining used repeatedly: they do not leave the InterBase database in a state optimum. Therefore, it is desirable to run the utilities InterBase regularly this tutorial on using gfix and gbak . ->


2. Transactions with dbExpress


(in preparation)


3. Transactions with ADO (dbGo)


With ADO, we have the explicit management of transactions in TADOConnection as we see in the example below.

procedure ReservationDeLigne; begin try ADOConnection1.BeginTrans; //Beginning of transaction if StockSuffisant Then InsererLaLigne else Abort; if AcceptationDeLaLigne Then SortieEffectiveDuStock else Abort; ADOConnection1.CommitTrans; //Normal completion of the transaction except ADOConnection1.RollbackTrans; //abnormal end of the transaction end end;
In this tutorial, we saw how to protect sets of operations per transaction.




Using concerted office 1 day.
In this one-day course, this is to enable users to be efficient with their word processing and spreadsheets, in harmony with Descartes software:

 o Mail Merge in Word from the Federal Data
 o Reporting Presentation in Excel from the accounting results
 o Sending emails from Fed data