Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 309 / 536

CHAPTER 8 Transactions and Versioning
289
• 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.