Checkpoints, savepoints, and transaction rollback

Rolling back to a savepoint

 

You can undo all changes after a savepoint by issuing a ROLLBACK TO

 

SAVEPOINT. This command rolls back to the savepoint you specify, or to the

 

most recent SAVEPOINT if you do not specify a named savepoint. Rolling back

 

to savepoint n undoes all actions for all savepoints greater than or equal to n.

 

Normally, locks are released only at the end of a transaction. However,

 

ROLLBACK TO SAVEPOINT does release locks under certain conditions, as in

 

the following scenario.

Example

Assume that you have a series of savepoints in a transaction, and then perform

 

a write operation. You then roll back the transaction to an earlier savepoint. The

 

rollback undoes all actions after that savepoint, including the write operation

 

and any locks it acquires after the savepoint you are rolling back to.

Automatic and user-defined savepoints

IQ sets an implicit savepoint before and after every DML command. The data page versions associated with these savepoints are released when the command completes. If you want to retain data page versions beyond the end of a single DML command, you need to set your own, named savepoints.

Naming and nesting savepoints

Savepoints can be named and they can be nested. By using named, nested savepoints, you can have many active savepoints within a transaction. Changes between a SAVEPOINT and a RELEASE SAVEPOINT can still be canceled by rolling back to a previous savepoint or rolling back the transaction itself. Changes within a transaction are not a permanent part of the database until the transaction is committed. All savepoints are released when a transaction ends.

Savepoints cause Adaptive Server IQ to update information it maintains about the location of available disk space. This information is used during transaction rollback.

There is no additional overhead in using savepoints, although unreleased savepoints may consume extra disk space by keeping older intermediate versions active.

306

Page 326
Image 326
Sybase 12.4.2 manual Rolling back to a savepoint, Automatic and user-defined savepoints, Naming and nesting savepoints, 306