Sybase 12.4.2 Table-level versioning, One writer and multiple readers at the table level, 292

Overview of transactions and versioning

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 a transaction begins. Throughout the transaction, a user who reads from the object sees the unchanged image, or snapshot version.

Table-level versioning

In Adaptive Server IQ, at the user-visible level, the unit of versioning is the table. Table-level versioning makes sense for Adaptive Server IQ for these reasons:

•IQ data structures aggregate data for columns at the table level.

•Most IQ insertions and deletions write data table-wide.

With table-level versioning, Adaptive Server IQ can control access to the data at the level where write operations occur, and where query results are focused.

Internally, however, data is versioned at the page level. This approach helps conserve system resources.

A given IQ table may consist of millions of pages of data. When you update that table, you may be writing to only a small percentage of those pages. It would require a vast amount of disk space to maintain a complete copy of each version of an entire table. Adaptive Server IQ saves on disk space by allowing table versions to share pages that are not being updated.

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.

Imagine a situation such as the one shown in Figure 8-1. First, User 1 begins a transaction and starts to insert data into the customer table. As long as User 1's transaction remains open, no other user can write to the customer table. Any transaction that attempts to write to the customer table receives an error until User 1's transaction commits.

In Figure 8-1, User 2 gets an error for attempting to write before User 1's transaction commits. User 2's application determines whether to roll back the transaction, or to try writing to a different table. However, User 2 cannot write to the customer table again in the same transaction.

292