Sybase 12.4.2 manual Savepoints within transactions, Releasing savepoints, 305

CHAPTER 8 Transactions and Versioning

•At the start and end of the backup process.

•When the database server is shut down.

The CHECKPOINT_TIME is the maximum time that can pass between checkpoints. It is set by default at 60 minutes. You can adjust the checkpoint interval with the SET OPTION command; see the Adaptive Server IQ Reference Manual for details. You probably do not need to adjust the checkpoint time or issue explicit checkpoints, however. Controlling checkpoints is less important in Adaptive Server IQ than in OLTP database products, because IQ writes the actual data pages after each transaction commits.

For more information on checkpoints in recovery, see “How transaction information aids recovery”.

Savepoints within transactions

Adaptive Server IQ supports savepoints within a transaction.

A SAVEPOINT statement defines an intermediate point during a transaction. Because a single IQ transaction may write millions of rows of data, you may want to limit the amount of data that is committed—and thus written to disk— to less than a full transaction's worth. Setting savepoints allows you to subdivide transactions.

You can undo all changes after a savepoint using a ROLLBACK TO SAVEPOINT statement. For more information on savepoints and rollback, see “Naming and nesting savepoints”.

Releasing savepoints

Once a RELEASE SAVEPOINT statement has been executed or the transaction has ended, you can no longer use the savepoint. Releasing a savepoint frees up the version pages that have been used, up to that savepoint. Remember that data is versioned at the page level internally. Adaptive Server IQ maintains a separate copy of just the updated pages; the remaining pages are shared with the previous version. By releasing savepoints, you free up the pages associated with them, and thus make better use of your disk space.

Releasing savepoint n both releases all resources after that savepoint, and gives up your ability to roll back to any intermediate savepoints.

No locks are released by the RELEASE SAVEPOINT command.

305