What R Isolation Levels & How We Havw To Apply Them On Any Application Transaction
Regards.
Vignesh

Isolation Levels affect how long Share locks (from a SELECT) are held:

RR - All accessed rows maintain the Share Lock until a Commit or Rollback happens

RS - All accessed rows maintain the Share Lock until the current SQL statement is completed.

CS - Share Locks are released when the DB2 is finished evaluating the row (when it moves onto the next row to evaluate).

UR - No locks are taken, and locks from other applications are ignored.

For maximum concurrency, especially in a non-data warehouse database, use the default of CS.

Isolation level does not affect how long Exclusive Locks are held that are a result of an INSERT, UPDATE, or DELETE. These locks are released when a commit or rollback occurs.

It's even stronger than that: not only the rows that are accessed (i.e., that satisfy the WHERE condition) are locked, but also any rows that could potentially become "to be accessed" by somone else's update or insert. (These are so-called "phantom reads".) So the whole table needs to be locked to enforce repeatable read (RR).

Just in case you wanted to know the names of these isolation levels, here they are:

RR - Repeatable Read
RS - Read Stability
CS - Cursor Stability
UR - Uncommitted Read

-- Steve