SAND CDBMS SQL Reference Guide


A transaction is a group of SQL statements that concludes with either a COMMIT [WORK] or a ROLLBACK [WORK] statement. A transaction is treated as a logical unit of work from the standpoint of database integrity. That is, all SQL statements in a transaction succeed or fail.

Consider a simplified accounting application example. The payment of an electricity bill consists of recording the negative of the amount in the cash column of the Asset table, and recording the amount in the electricity column of the Expense table. One cannot be allowed to happen without the other. Therefore the transaction would look like this:

INSERT INTO asset (tr,cash) VALUES (139001,-123.67);
INSERT INTO expense (tr,electricity) VALUES (139001,123.67);

The transaction number is recorded in the tr column of both tables, and offsetting amounts are recorded in the cash and electricity columns of the respective tables. Both statements are committed together. In this way, one statement is never permitted to be recorded without the other. This is true whether there is a concurrency conflict, or even a database server crash.