Some applications
have a requirement to treat groups of updates to a particular data
source as a single unit. That is, either the entire group of updates
is applied to the data or none of them is applied. Applications can
test for errors and execute specific commit and rollback operations
to provide that functionality.
FedSQL supports both
COMMIT and ROLLBACK statements, which provide data protection by ensuring
that updates are either fully applied or rolled back to the pre-transaction
state when an operation is interrupted.
A
transaction is
an atomic unit of work. That is, a transaction either completely succeeds
or has no effect. After a logical, consistent set of changes has occurred,
a transaction is ended either by committing the changes, which makes
them permanent, or by canceling the changes, which returns the values
that are changed by a transaction to their original state.
When a connection to
a database is established, autocommit functionality is the default.
That is, each individual FedSQL statement is treated as a transaction.
As soon as the statement is executed, if no return code is detected,
the transaction is automatically committed. If update problems are
detected, FedSQL initiates a rollback of the transaction.
To allow a transaction
to be made up of multiple FedSQL statements, the application must
turn off autocommit functionality. When autocommit functionality
is on, COMMIT and ROLLBACK statements have no effect.
Not all data sources
provide transaction management. For example, transaction management
is not available for SAS data sets or SPD Engine data sets. However,
transaction support is available for data sources such as Oracle and
Teradata. See the server administration documentation for information
about how to turn off autocommit functionality. For example, see
SAS
Federation Server: Administrator’s Guide or the
FEDSQL procedure documentation for information about connection options
that turn autocommit functionality off.