Versioning Overview

Sybase IQ uses snapshot versioning to allow transactions to operate concurrently.

You can think of snapshot versioning as you would a snapshot you take with a camera. When you photograph a snapshot of an object or scene, you get an image of it as it appears at a given moment in time. Likewise, when IQ takes a snapshot of an object in your database, it retains an image of that object at a given instant in time.

Unlike a camera, though, IQ does not need to make a copy of the entire object each time the image changes. Instead, it copies only the parts of the image—the database pages—that have changed. Database pages that have not changed are shared among all active versions in the database.

IQ takes its snapshot when the first command is executed following a connect, commit, or rollback. The user can force the snapshot to be taken earlier by executing an explicit BEGIN TRANSACTION command. Throughout the transaction, a user who reads from the object sees the unchanged image, or snapshot version.

• Table-level Versioning
  With table-level versioning, Sybase IQ can control access to the data at the level where write operations occur, and where query results are focused.
• One Writer and Multiple Readers at the Table Level
  On a given table, IQ permits only one user to have write access for doing insertions and deletions, and multiple readers to issue queries concurrently.
• Multiple Writers and Readers in a Database
  Within an IQ database, multiple read-only and read-write users can operate concurrently, as long as the writers are inserting data into (or deleting it from) different tables.
• Transactions Use Committed Data
  Every transaction uses the latest committed version of the database as of the time the transaction begins.
• Timing of Commit Operations on Read Transactions Affects Versions
  While a read transaction cannot affect what an existing write transaction sees, committing a read transaction does have implications for other transactions.
• Hold Cursors Span Transactions
  The only exception to the rule that transactions always use the latest committed version is in transactions that use hold cursors.
• How Sybase IQ Tracks Versions
  Sybase IQ assigns a version identifier to each database object that exists in the metadata, and that has a life span beyond a single command.
• Versioning of Temporary Tables
  A temporary table that is created in the database is called a global temporary table.