Consistency of data means that if multiple users repeatedly execute a series of transactions, the results are correct for each transaction, each time. Simultaneous retrievals and modifications of data do not interfere with each other: the results of queries are consistent.
For example, in Table 10-1, transactions T1 and T2 are attempting to access data at approximately the same time. T1 is updating values in a column, while T2 needs to report the sum of the values.
T1 |
Event Sequence |
T2 |
|---|---|---|
begin transaction update account set balance = balance - 100 where acct_number = 25 update account set balance = balance + 100 where acct_number = 45 commit transaction |
T1 and T2 start. T1 updates balance for one account by subtracting $100. T2 queries the sum balance, which is off by $100 at this point in time—should it return results now, or wait until T1 ends? T1 updates balance of the other account by adding the $100. T1 ends. |
begin transaction select sum(balance) from account where acct_number < 50 commit transaction |
If transaction T2 runs before T1 starts or after T1 completes, either execution of T2 returns the correct value. But if T2 runs in the middle of transaction T1 (after the first update), the result for transaction T2 will be different by $100. While such behavior may be acceptable in certain limited situations, most database transactions need to return correct consistent results.
By default, Adaptive Server locks the data used in T1 until the transaction is finished. Only then does it allow T2 to complete its query. T2 “sleeps,” or pauses in execution, until the lock it needs it is released when T1 is completed.
The alternative, returning data from uncommitted transactions, is known as a dirty read. If the results of T2 do not need to be exact, it can read the uncommitted changes from T1, and return results immediately, without waiting for the lock to be released.
Locking is handled automatically by Adaptive Server, with options that can be set at the session and query level by the user. You must know how and when to use transactions to preserve the consistency of your data, while maintaining high performance and throughput.
