Designing and implementing Intelligent Transactions
Traditionally, a transaction is an atomic set of database operations that changes a database from one valid state to another valid state. A “valid state of a database” is a state in which no integrity rules or consistency rules are violated in the database. These rules can be both database specific (referential integrity) and application specific.
solidDB®Intelligent Transaction is an extension to the traditional transaction model. It allows you to implement transactions that are capable of validating themselves in the current database and adapting their contents (if required) according to the rules of the transaction.
For example, in an order processing system, an application rule might permit the creation of an order only if the customer’s credit limit is not exceeded. A “create order” transaction may consist of inserting a row into the CUST_ORDER table andinserting another row into the INVOICE table. If inserting an order fails because a customer credit limit is exceeded, then inserting an invoice about the order should also fail. The INSERT_ORDER procedure should inform the INSERT_INVOICE procedure about the validation error. This allows the INSERT_INVOICE procedure to change its behavior and thus keep the transaction valid.
The existence of application specific consistency rules lead to the following transaction design principles:
A solidDB®Intelligent Transaction is a collection of SQL statements that may contain business logic that is typically implemented as a solidDB® stored procedure. Transactions that are intelligent have the following behavior and characteristics:
▪They contain more than one operation, that is, calls to more than one stored procedure.
▪They are long-lived because they are created, tentatively committed, and saved in the replica database, but finally committed in the master database. Thus, all validity checking of each transaction in the master database must be done by the transaction itself.
▪They are responsible for the consistency of the master database.
“Create order transaction” creates a “create order” transaction in a simple order entry application. The following sections use this example to illustrate how to implement a solidDB®Intelligent Transaction.
Create order transaction
-- Make changes to local database CALL INSERT_ORDER(...) ; CALL UPDATE_CUSTOMER_CREDIT(...) ; -- Save a property to the transaction SAVE PROPERTY priority VALUE '1'; -- Save the statements for later propagation to master SAVE CALL INSERT_ORDER(...) ; SAVE CALL UPDATE_CUSTOMER_CREDIT(...) ; -- make the local changes as well as the saved transaction -- persistent COMMIT WORK;