19.3.1.4 Transaction handling

A transaction in SQL is defined as starting from the connect, or from a commit, rollback or data-dictionary update and lasting until a commit, rollback, data-dictionary update or a disconnect command.

The macro with-transaction executes a body of code and then does a commit, unless the body failed in which case it does a rollback. Using this macro allows your code to cope with the fact that transactions may be handled differently in the different vendor implementations. Any differences are transparent if the update is done within a with-transaction form.

Note: Common SQL opens an ODBC database in manual commit mode, so that with-transaction and rollback take effect.

Applications should perform all database update operations in a with-transaction form (or follow them with commit or rollback) in order to safely commit or discard their changes. This applies to operations that modify either the data or the schema.

The following example shows a series of updates to an employee table within a transaction. This example would commit the changes to the database on exit from with-transaction. This example inserts a new record into the emp table, then changes those employees whose department number is 40 to 50 and finally removes those employees whose salary is more than 300,000.

(with-transaction

 (insert-records :into [emp]

                 :attributes '(empno ename job deptno)

                 :values '(7100 "ANDERSON" "SALESMAN" 30))

 (update-records [emp]

                :attributes [deptno]

                :values 50

                :where [= [deptno] 40])

 (delete-records :from [emp]

                 :where [> [sal] 300000]))

To commit or roll back all changes made since the last commit, use the functions commit or rollback.