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.
(connect "personnel")
(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
.