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. Title not available 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
