Guidelines for choosing isolation levels

The choice of isolation level depends on the kind of task an application is performing. This section gives some guidelines
for choosing isolation levels.

To choose an appropriate isolation level, you must balance the need for consistency and accuracy with the need for concurrent
transactions to proceed unimpeded. If a transaction involves only one or two specific values in one table, it is unlikely
to interfere as much with other processes compared to one that searches many large tables and therefore may need to lock many
rows or entire tables and may take a very long time to complete.

For example, if your transactions involve transferring money between bank accounts, you likely want to ensure that the information
you return is correct. However, if you just want a rough estimate of the proportion of inactive accounts, then you may not
care whether your transaction waits for others or not, and you may be willing to sacrifice some accuracy to avoid interfering
with other users of the database.

Furthermore, a transfer may affect only the two rows which contain the two account balances, whereas all the accounts must
be read to calculate the estimate. For this reason, the transfer is less likely to delay other transactions.

SQL Anywhere provides four isolation levels: levels 0, 1, 2, and 3. Level 3 provides complete isolation and ensures that transactions
are interleaved in such a manner that the schedule is serializable.

If you have enabled snapshot isolation for a database, then three additional isolation levels are available: snapshot, statement-snapshot,
and readonly-statement-snapshot.

Snapshot isolation offers both concurrency and consistency benefits. Using snapshot isolation incurs a cost penalty since
old versions of rows are saved as long as they may be needed by running transactions. Therefore, long running snapshots can
require storage of many old row versions. Usually, snapshots used for statement-snapshot do not last as long as those for
snapshot. Therefore, statement-snapshot may have some space advantages over snapshot at the cost of less consistency (every
statement within the transaction sees the database at a different point in time).

For most purposes, the snapshot isolation level is recommended because it provides a single view of the database for the entire
transaction.

The statement-snapshot isolation level provides less consistency, but may be useful when long running transactions result
in too much space being used in the temporary file by the version store.

The readonly-statement-snapshot isolation level provides less consistency than statement-snapshot, but avoids the possibility
of update conflicts. Therefore, it is most appropriate for porting applications originally intended to run under different
isolation levels.