ALLBASE/SQL Reference Manual (36216-90216)

188 Chapter5

Concurrency Control through Locks and Isolation Levels

Scope and Duration of Locks

In general, the length of a transaction affects concurrency. Long transactions hold locks

longer, which increases the chances that another transaction is waiting for a lock. Short

transactions are "in and out" quickly, which means they are less likely to interfere with

other transactions.

The isolation level determines what kinds of locks are obtained in particular

circumstances, and also how long these locks are held. Great differences can be found

between isolation levels in the duration of locks. For example, a sequential scan that

obtains share locks at the RR level holds them while the entire table is read, making

updates impossible by others during that time. At the RU level, other users can update the

table throughout an entire scan by another reader. Figure 5-6. shows the relative scope

and duration of share locks obtained for a sequential scan by the RR, CS, and RC isolation

levels on PUBLIC and PUBLICROW tables. RU is not shown, because it does not obtain any

share locks on user data.

Figure 5-6. Scope and Duration of Share Locks for Different Isolation Levels