Transaction Usage

The begin transaction and commit transaction commands tell SAP ASE to process any number of individual commands as a single unit. rollback transaction undoes the transaction, either back to its beginning, or back to a savepoint. Use save transaction to define a savepoint inside a transaction.

In addition to grouping SQL statements to behave as a single unit, transactions improve performance, since system overhead is incurred once per transaction, rather than once for each individual command.

Any user can define a transaction. No permission is required for any of the transaction commands.

• Allow Data Definition Commands in Transactions
  You can use certain data definition language commands, such as create table, grant, and alter table, in transactions by setting the ddl in tran database option to true.
• System Procedures That Are Not Allowed in Transactions
  You cannot use certain system procedures within transactions.
• Begin and Commit Transactions
  The begin transaction and commit transaction commands can enclose any number of SQL statements and stored procedures.
• Roll Back and Save Transactions
  If you must cancel a transaction before it commits—either because of some failure or because of a change by the user—you must undo all of its completed statements or procedures.
• Transaction States
  The global variable @@transtate keeps track of the current state of a transaction. SAP ASE determines what state to return by keeping track of any transaction changes after a statement executes.
• Nested Transactions
  You can nest transactions within other transactions. When you nest begin transaction and commit transaction statements, the outermost pair actually begin and commit the transaction. The inner pairs only track the nesting level.
• Example of a Transaction
  An example showing how you might specify a transaction.