Sybase 12.4.2 Versioning prevents inconsistencies, How locking works, Locks for DML operations

CHAPTER 8 Transactions and Versioning

Versioning prevents inconsistencies

Without versioning, concurrent read and write operations could cause inconsistencies in the database. The table-level versioning provided by Adaptive Server IQ prevents inconsistencies both by serializing transactions, and by making the table the version level.

Adaptive Server IQ allows multiple writers to modify a table serially—that is, one after the other, never more than one at a time—while multiple readers continue to work on an original copy of the table. With this method, IQ takes on full responsibility for preventing inconsistencies.

While any transaction processing system is designed to ensure that the database remains consistent, the Adaptive Server IQ approach means that users don't need to worry about placing their queries and updates in appropriate transactions. IQ begins and ends transactions automatically, and ensures that read and write operations do not interfere with each other.

How locking works

All Adaptive Server IQ locks occur automatically, based on the type of operation a user requests. You do not need to request a lock explicitly. The transaction that has access to the table is said to hold the lock.

When a table is locked in Adaptive Server IQ, no other transaction can have write access to it, but any transaction can have read access to it. Data definition operations form an exception to this universal read access; see the discussion below for details. Any other write transaction that attempts to access a table with a write lock on it receives an error.

The locks maintain the reliability of information in the database by preventing concurrent access by other transactions. The database server retains all the locks acquired by a transaction until the transaction completes, due to either a commit or a rollback.

Locks for DML operations

Data Manipulation Language (DML) operations include insertions, deletions, and queries. For all such operations, Adaptive Server IQ permits one writer and multiple readers on any given table. This rule has the following implications:

299