Locking and data consistency

Data consistency 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 1-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.

Table 1-1: Consistency levels in transactions

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 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 is different by $100. While such behavior may be acceptable in some situations, most database transactions must 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 results do not need to be exact, T2 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 should know how and when to use transactions to preserve data consistency while maintaining high performance and throughput.