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 more information, see Intelligent Transactions.
Create order transaction creates a "create order" transaction in a simple order entry application. The following example illustrates how to implement a solidDB Intelligent Transaction in a simple order entry application.
-- 1. Make changes to local database CALL INSERT_ORDER(...) ; CALL UPDATE_CUSTOMER_CREDIT(...) ; -- 2. Save the statements for later propagation to master SAVE CALL INSERT_ORDER(...) ; SAVE CALL UPDATE_CUSTOMER_CREDIT(...) ; -- 3. Make the local changes as well as the saved transaction persistent COMMIT WORK;
The first part of the transaction (1) is a straightforward execution of the standard SQL clauses in the replica database. In the solidDB Advanced Replication architecture, local changes remain local unless the statements and parameters of a transaction are explicitly saved for later propagation.
The second part of the transaction (2) saves the statements so that the local updates can be propagated to the master database at a later time, see SAVE.
The final part of the transaction (3) commits the transaction which updates the local database and makes the saved transaction persistent.
Note that it is possible to merely save the statements for later propagation and not update the local database at all. In this case, the replica will get the updated information after it propagates the information to the master and then refreshes the updated data from the master.
Important The saved statement is executed "as is" on the master; the statement does not carry with it any memory of which records on the replica were affected when it was executed on the replica. For example, suppose that you execute a series of statements like:
UPDATE employee_table SET salary = salary * 1.10 WHERE state = 'California'; SAVE UPDATE employee_table SET salary = salary * 1.10 WHERE state = 'California';
Suppose also that the master database contains 200 employees who work in California, while the replica contains only the 100 employees who worked at the local branch office in San Francisco, California. In that case, the UPDATE statement executed on the replica would apply only to the 100 employees contained in the replica database, but the identical saved statement would apply to all 200 California employees listed in the master database. When you propagate a statement to the master, be careful to use a WHERE clause that ensures that the statement applies only to the appropriate records.