Sybase 12.4.2 How Adaptive Server IQ keeps track of versions, Versioning of temporary tables, 298

COMMIT PRESERVE ROWS

Overview of transactions and versioning

How Adaptive Server IQ keeps track of versions

Adaptive Server IQ assigns a version identifier to each database object that exists in the metadata, and that has a life span beyond a single command. IQ uses these version identifiers to ensure that writes to any database object are always based on the latest version of the object. It keeps each active version of a database object on disk.

When an older version is no longer needed by active transactions, Adaptive Server IQ drops it from the database. A version is needed until the transactions using it do one of the following:

•Commit

•Roll back

•Issue a RELEASE SAVEPOINT command releasing that version

For information on defining, releasing, and rolling back to savepoints, see “Savepoints within transactions”.

Versioning of temporary tables

A temporary table that is created in the database is called a global temporary table. A global temporary table is accessible to all users with the appropriate permissions. Each user has his or her own instance of the table, however; only one user ever sees a given set of rows. By default, a global temporary table is deleted at the next COMMIT. You can override this default, by specifying ON

when you create the temporary table.

A local temporary table is declared rather than created in the database. Only one user sees any of the rows in a local temporary table. The table is dropped when that user disconnects. When you declare a local temporary table, Adaptive Server IQ issues a savepoint instead of committing the transaction automatically, as it would for a data definition operation on any other type of table.

For purposes of versioning, Adaptive Server IQ makes no distinction between base tables (main database tables) and global temporary tables. Because the data in any temporary table is accessible to only one user, there will never be more than one write transaction open for a temporary table.

298