Sybase 12.4.2 manual Options in Dbisql, Committing a transaction writes data to disk, 289

CHAPTER 8 Transactions and Versioning

•A ROLLBACK statement undoes all the changes made by the transaction.

•A disconnection from a database causes an implicit rollback (the default) or commit, depending on whether the DBISQL option COMMIT_ON_EXIT is set.

•A statement with a side effect of an automatic commit is executed.

Database definition commands, such as ALTER, CREATE, and DROP all have the side effect of an automatic commit. You can also use two DBISQL options to cause a commit to occur automatically.

Options in DBISQL

DBISQL provides two options that let you control when and how transactions end:

•If you set the option AUTO_COMMIT to ON, DBISQL automatically commits your results following every successful statement, and automatically performs a ROLLBACK after each failed statement.

•The setting of the option COMMIT_ON_EXIT controls what happens to uncommitted changes when you exit DBISQL. If this option is set to ON (the default), DBISQL does a COMMIT; otherwise it undoes your uncommitted changes with a ROLLBACK statement.

Adaptive Server IQ also supports Transact-SQL commands, such as begin transaction, for compatibility with Adaptive Server Enterprise. For further information, see “Transact-SQL Compatibility” in the Adaptive Server Anywhere User's Guide.

Committing a transaction writes data to disk

When you execute a write operation, Adaptive Server IQ does not immediately write the data to disk. Instead, it writes it into a data cache, an area in memory where it stores pages from the database while they are in use. Reading from and writing to the cache reduces the number of number of times Adaptive Server IQ must access the disk. It is an essential part of IQ’s high performance.

Eventually, IQ must write dirty pages—that is, pages that have been updated— to the disk. Adaptive Server IQ writes dirty pages to disk each time a transaction commits. This approach is a major benefit to IQ users, because it means that IQ does not need to log data insertions in the transaction log. By not logging the very large insertions that are typical with IQ, users gain tremendous savings in disk and performance cost.

289