Transactions

Each transaction is a sequence of logically related commands that accomplish one task and transform the database from one consistent state into another.

Transactions are atomic; SAP Sybase IQ executes all the statements within a transaction as a unit. At the end of each transaction, changes are committed to make them permanent. If for any reason any of the commands in the transaction do not process properly, some or all of the intermediate changes can be undone, or rolled back. The user application controls the conditions under which changes are committed or rolled back.

Transactions are made up of small blocks. The completion of each block marks a point at which the information is self-consistent. Transaction processing is fundamental to ensuring that a database contains correct information.

In most cases, SAP Sybase IQ transactions begin and end automatically, based on the commands being issued, and the options set. You can also issue explicit commands to begin or end a transaction.

Transactions start automatically with the first statement following either:

• A connection to a database
• The end of a previous transaction

SAP Sybase IQ also supports SAP Sybase IQ Transact-SQL commands, such as BEGIN TRANSACTION, for compatibility with Adaptive Server. You can explicitly start a transaction using the BEGIN TRANSACTION command.

Transactions complete with one of these events:

• A COMMIT statement makes the changes to the database permanent.
• A ROLLBACK statement undoes all the changes made by the transaction.
• A disconnection from a database causes an implicit rollback (the default) or commit.
• The execution of a statement with a side effect of an automatic commit makes changes to the database.

Database definition commands, such as ALTER, CREATE, and DROP all have the side effect of an automatic commit.

You can use savepoints to identify important states within a transaction and return to them selectively or cause other actions to occur.

• Viewing Transaction Activity
  Use the sp_iqtransaction stored procedure to display a snapshot of transaction activity, such as main and temporary space created and in use, open cursors, and savepoints.
• Isolation Levels
  An important aspect of transaction processing is the database server's ability to isolate an operation. ANSI standards define four levels of isolation. Each higher level provides transactions a greater degree of isolation from other transactions, and thus a greater assurance that the database remains internally consistent.