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 “Scope and Duration of Share Locks for Different Isolation Levels” 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