Intelligent Transaction refers to propagating changes from a replica database to the master database. The data of all the replica databases is unofficial. Any modifications to the data on a replica are tentative. The modifications become official only when they are successfully validated and committed in the master database.
The “create now in replica, commit later in master” requirement extends the lifecycle of a transaction from a fraction of a second to an undefined duration. In a multi-database system, transactions are propagated from replicas to the master database over a time period that can vary from seconds to even weeks. The challenge of such a transaction is to ensure that whenever it is validated and committed to the master database, it changes the master database from one consistent state to another consistent state.
Ensuring database consistency
A database is consistent if the transactions that modify the contents of the database meet the following criteria at the commit moment of the transaction:
▪DBMS specific rules, such as referential integrity rules, are not violated.
▪Business rules that apply to the business transactions and their respective database transactions are not violated.
When the propagated replica transaction is eventually committed in the master database, it is possible that the state of the master database is different than the state of the replica database (where the transaction was originally created). The state of the master database might have been changed because of propagated transactions from other replicas or updates done directly to the master database after the replica’s latest refresh. For this reason, the replica transaction cannot be used in the master database with its original content.
To address the consistency requirement in the two-tier replication model, each transaction that can become invalid during its lifecycle must contain built-in business logic for ensuring that the master database remains consistent when the transaction is committed on the master. If the database becomes inconsistent with the original behavior of the transaction, the transaction must detect this and change the behavior so that the consistency of the database is maintained.
The Intelligent Transaction model provides a framework for implementing transactions with long life spans. Transaction propagation in advanced replication architecture is based on solidDB® Intelligent Transaction technology. See Propagate and Refresh for a simple illustration of propagating and refreshing data when some records are accepted by the master and some are not.
Intelligent Transaction scenario
To illustrate Intelligent Transaction implementation, assume an order entry application has a business rule that customers must not exceed their credit limit. If the limit has been exceeded, new orders are prohibited.
In a multi-database system it is possible that the customer credit limit in a replica database is OK, whereas the same data in the master database indicates a limit overrun. In this situation, a customer can still enter an order to the replica database, because the information about the limit overrun has not reached that database yet. However, when the “add a new order” transaction is propagated from the replica to the master database, it must not be committed in its original form, because that would mean a violation of the “credit limit” business rule. Instead the transaction needs to change its behavior to be valid. For instance, the “status” column of the order must be given the value “invalid” in the master database to keep the order separate from the valid orders. The invalid order can be refreshed back to the replica to notify the replica users that the transaction has failed.
Multi-database systems versus centralized systems
In a traditional client/server system that uses a central database, the validation logic of each transaction is typically in the client application or in the application server’s services. For instance, in an Order Entry application, the application logic must check prior to committing the transaction that the credit limit of a customer is not exceeded by the new order.
When propagating a transaction to the master database, similar validation is needed to ensure the database integrity. The only difference is that the transaction validation logic of the application is not available to the synchronization mechanism. Therefore the logic must be bundled into the transaction itself. The following kind of validation logic is required in each transaction:
▪update conflict detection
▪validation using business rules
▪DBMS error handling
The transactions of a centralized system are very different from the transactions of a multi-database system. In a centralized system, the lifetime of a transaction is typically a fraction of a second and with the DBMS locking mechanism, update conflicts are not possible.
Transaction in a central database
The figure above illustrates a typical transaction. Within the transaction, some queries are made prior to write operations to validate the contents of the transaction. For example, an order entry system can check that a customer credit limit is OK prior to creating a new order to the customer. During the transaction, the concurrency control mechanism of the server takes care of the update conflicts and other issues caused by concurrent usage of the data.
In a multi-database system, a transaction is initially created and saved in the replica database but finally committed in the master database later when the transaction is propagated there as part of the database synchronization process. The tentatively committed transaction can exist in the system for an unlimited period of time. In other words, the lifecycle of the transaction is entirely different.
Transaction in a synchronized database
Intelligent Transaction in the multi-database system
In a multi-database system, a transaction has “two lives”. The transaction is created as tentative in the replica database where it is validated and committed by the business application. The transaction is saved (put to the transaction queue) in the replica database for later propagation to the master database. The transaction has its “second life” when it is propagated to the master database. There it must perform the same validation routines, that is, the queries that were performed in the replica database. For instance, if a customer credit limit was checked in the replica database to ensure the transaction validity, the same operation must usually be done also in the master database prior to committing the transaction. Otherwise the validity of the transaction cannot be guaranteed in the master database.
To support the extended lifecycle of the two-tier data redundancy model, solidDB® Intelligent Transaction allows a transaction to validate itself in the master database and adjust its behavior to ensure the validity of the transaction.
The structure of Intelligent Transaction
How Intelligent Transaction functionality works
With Intelligent Transaction functionality, a transaction not only has the capability of validating itself in its current database, but is also capable of changing its behavior (that is, the database operations) if the original behavior was invalid.
Statements of a transaction can be any SQL statements, but most often are calls to stored procedures. Statements should contain logic that is required to ensure the validity of the statement in different environments and situations.
When executing the transaction in the master database, the statements of the transaction can communicate with each other by putting transaction parameters on the Parameter Bulletin Board for the following statements of the same transaction to read. This communication ability of the statements makes it possible to create transactions that can validate themselves and adjust their behavior according to the current environment.
For example, if a transaction which is propagated to the master database attempts to add a new order to a customer whose credit limit has been exceeded, the following takes place:
The transaction has the following operations:
▪Insert a row to the CUST_ORDER table.
▪Update the CREDIT column of the CUSTOMER table.
The processing of the statements goes as follows:
1 Prior to inserting a new row to the CUST_ORDER table, the INSERT_ORDER procedure checks if the customer has credit In this example, it is assumed the credit level has been exceeded.
2 Because the customer does not have sufficient credit, the intelligent transaction inserts the new row to the CUST_ORDER table with a different STATUS value (for example, STATUS = 'Not approved').
3 Because the order is not a valid one, the update operation of the CREDIT column must not be done. Therefore the INSERT_ORDER procedure puts a parameter with name “ORDER_FAILED” and value “YES” to the bulletin board.
4 The UPDATE_CUST_CREDIT procedure checks the bulletin board and detects that it contains information that applies to this procedure.
5 The UPDATE_CUST_CREDIT procedure then knows that it must not update the credit amount.
6 Later, when the replica refreshes to get up-to-date data, the replica’s own information about the customer order is updated, and the data on the replica will correctly indicate that the order was not processed. As a result, both the master and replica have correct data, even when the replica made a request/update that could not be fulfilled.
