18th Chapter-delphi Client Server application Development (III.) (4)

18.3.2.2 using implicit control

By default, Delphi provides an implicit transaction control for the application through BDE. When an application is in implicit transaction control, Delphi provides implicit transaction control for each record written in the dataset. It submits each individual write operation, such as Post and append record.

It is easy to use implicit transaction control, which guarantees minimal record update conflicts and a consistent view of the database. On the other hand, because transaction control is needed for every row of data written to the database, implicit transaction control causes the network to be too busy and application performance to degrade.

With explicit transaction control, you can choose the most effective time to start, commit, and terminate transactions, especially if the client application that is developing a multiuser environment is running access to a remote SQL Server, you should use explicit control.

18.3.2.3 using explicit transaction control

There are two kinds of collaborative and independent ways to apply the Delphi database application transaction control:

Methods and properties for using the tdatabase part

Use transitive SQL in the Tquery part. This is only valid in the Delphi Client/server Suite Edition, and SQL links passes SQL expressions directly to the process SQL or ODBC server

The benefit of using the methods and properties of the tdatabase part is to provide a clear, lightweight application capability that is not relevant to a particular database or server.

The primary benefit of using transitive SQL is the ability to take advantage of the advanced transaction management capabilities of a particular server.

1. Methods and properties for using Tdatabase

The following table lists the methods and properties used for transaction management in the Tdatabase part and how they are used:

Table 18.11 Tdatabase Method table for transaction explicit control

━━━━━━━━━━━━━━━━━━━━━━━━

Method or Property action

────────────────────────

Commit to commit data modification and terminate transaction

Rollback cancels the data modification and terminates the transaction

StartTransaction Start a transaction

Transisolation expresses the independence level of a transaction

━━━━━━━━━━━━━━━━━━━━━━━━

Starttransaction,commit and rollback are methods for the application to invoke the start transaction at run time, control the transaction, and save or discard the modified data.

Transisolation are properties that are used by tdatabase parts to control how different transactions interact with the same table.

⑴ Start a transaction

When you start a transaction, all subsequent expressions of the read-write database occur in the context of that transaction. Each expression is part of it. Any changes made to the expression are either successfully submitted to the database or each modification is canceled. Consider a bank transfer problem on ATM. When a customer decides to transfer money from a deposit account to a payment account, two changes must occur on the Bank database record:

Deposit account must be credited to debit

Payment account must be credited to credit

If for some reason one of these operations cannot be completed, then none of the actions should occur. Because these operations are related, they should occur in the same transaction.

To begin a transaction in the Delphi application, you need to invoke the StartTransaction method in the Tdatabase part:

Databaseinterbase.starttransaction;

All subsequent data operations occur in the context of the most recent transaction until the transaction is explicitly terminated by invoking a commit or rollback.

So how long should the business be maintained? Ideally, as long as you need, you can. The longer the active state of a transaction, and the more users accessing the database, the more concurrent and simultaneous transactions begin and end in the life of your transaction, and the more likely to conflict with other transactions when attempting to commit a change.

⑵ Commit a transaction

For permanent modifications, the transaction must be committed using the commit method of the tdatabase part. Executing a COMMIT expression saves the changes to the database and terminates the transaction. For example, the following expression terminates the transaction that started in the previous example:

Databaseinterbase.commit

A commit call should be placed in an try...except expression. If a transaction cannot be committed successfully, you can handle the error and retry the operation.

⑶ return to a transaction

In order to cancel a database modification, a transaction must be returned with the rollback method. Rollback the modification of a transaction and terminates the transaction, for example: The following expression will revert to a transaction:

Databaseinterbase.rollback;

Rollback usually occurs in:

Exception handling code

button or menu event code, such as a user clicking the Cancel button

⑷ using the Transisolation property

The Transisolation property describes the independent level of the Tdatabase part transaction, and the independent level of the transaction determines how transactions interact with other transactions that act on the same table. You should be fairly familiar with the transaction and transaction management in Delphi before changing or setting transisolation values.

The default value for Transisolation is tireadcommitted. The following table summarizes the possible values of transisolation and describes their implications:

Table 18.12 The meaning of Transisolation property values

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Independence level meaning

──────────────────────────────────────

Tidirtyread allows read uncommitted modifications that are written to the database by other simultaneous transactions. Uncommitted

Modifications are not permanent and may be recovered at any time. At this level you

Transactions with other transactions have a minimum degree of independence.

Tireadcommitted only allow reading of database modifications submitted by other concurrent transactions. This is the default alone

State level.

Tirepeatableread allows a single database read transaction to not see other simultaneous transactions doing the same data

of changes. This level of independence ensures that your transactions are read one record at a time, recording

The view will not change, at this level your transaction and other transactions are done to complete the modification

All Independent.

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Various database servers support these levels of independence to varying degrees, some of which are not supported at all. If the requested isolation level is not supported by the server, Delphi will take a higher level of independence. Separate levels supported by various servers are shown in table 18.10.

If an application uses ODBC to interact with the server, the ODBC driver must support a stand-alone level.

2. Using transitive SQL

In order to be able to use transitive SQL to control things, you must:

Using the Delphi client/server Suite

Install the correct SQL links driver

Configure network protocols correctly

Ability to access databases on remote servers

Set Sqlpassthrough mode to not SHARED with the BDE Configuration tool

With transitive SQL, you can send a SQL transaction control expression directly to the remote database server using the Tquery, Tstoredproc, or tupdatesql parts, and BDE itself does not handle SQL expressions. The adoption of transitive SQL enables users to gain direct access to the SQL Server, providing transactional control advantages, especially when those controls are non-standard.

SQL Pass Throughmode describes whether BDE and transitive SQL share the same database join. In most cases, Sqlpassthroughmode is set to shared autocommit. However, if you want to pass SQL transaction control to the server, you must use the BDE Configuration tool to set the BDE Sqlpassthroughmode to not SHARED. In addition, you must establish a separate tdatabase part for the tquery part that passes the SQL transaction control expression.

3. Using Local transactions

BDE also supports local transactions on Paradox and dBASE. From a code perspective, there is no difference between transactions between local and remote database servers.

When a transaction acting on a local database table begins, the update operation is recorded in the log, and each log record contains the old record buffer. When a transaction is active and the updated record is locked until the transaction is committed or returned, the old record is applied to restore the updated record to its original state during the return process.