User's Guide

Part III. Using SQL Anywhere

Chapter 19. Using Transactions and Locks

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

Typical level 0 transactions

Transactions that involve browsing or performing data entry may last several minutes, and read a large number of rows. If isolation level 2 or 3 is used, concurrency can suffer. An isolation level of 0 or 1 is typically used for this kind of transaction.

For example, a decision support application that reads large amounts of information from the database to produce statistical summaries may not be significantly affected if it reads a few rows that are later modified. If high isolation was required for such an application, it could hold read locks on large amounts of data, not allowing other applications write access to it.

Transactions for which no updates are lost

Some applications require that no updates be lost. The following example typifies the lost update problem.

Example

Consider a sequence of instructions which could occur when two people put money into the same account at about the same time.

The initial account balance is $1000, and two people (Alex and Ben, say) are about to deposit money into it. Alex will deposit $2000, while Ben will deposit $100. We'll call Alex's transaction is transaction A and Ben's transaction is transaction B.

1. A reads the account balance and finds it to be $1000.
2. B reads the account balance and finds it to be $1000.
3. A adds $2000 to the present balance of $1000 to calculate the new account balance. It then updates the account balance to reflect Alex's deposit. It writes a new balance of $3000 into the database.
4. B adds $100 to the present account balance, which it has read as $1000, to calculate the new account balance. It then updates the account balance to reflect Ben's deposit, and writes a balance of $1100 into the database.
5. The final balance recorded after the two deposits is $1100.

While both transactions are perfectly correct in themselves, the interaction between the two creates an invalid result in the database, and Alex's update was lost.

Isolation levels that prevent lost updates

SQL Anywhere's locking mechanism prevents lost updates at isolation level 2 and 3. If your application is using cursors, then cursor stability (achieved at isolation level 1) guarantees no lost updates.

Serializable transactions

Some applications require serializable transactions due to the nature of the application. Isolation level 3 enforces serializability.

When transactions are serializable, they behave as if they were run one after another even if they were actually run concurrently. For example, banking software must prevent two machines from checking a balance and withdrawing the full amount from the account at the same time.

Transactions of this type should read few or no rows and last at most a few seconds, so that concurrency is not likely to be a problem. Applications that involve a high volume of small transactions can use isolation level 3 without sacrificing concurrency.