When the WITH LOCKS clause is used in an RR or CS transaction, existing
locks (and appropriate intention locks) that ensure that the row
that is currently pointed to by the cursor cannot be modified by another
transaction are retained when the COMMIT WORK statement is issued. All other
locks (including X locks) are released. For example, on a PUBLICROW or PUBLIC
table, if the user had fetched the second row of an active set when a COMMIT
WORK is issued, the S or SIX lock that was obtained when the FETCH was
issued would continue to exist in the new transaction that is automatically
created.
If the isolation level is RR and a serial scan is used, the S or SIX lock
that is retained is at the table level.
If the isolation level is RR and another scan is used, or if the isolation
level is CS, the S or SIX lock that is retained is at the row (or page)
level.
If the user had updated the second row after it was fetched, the
following occurs:
If the isolation level is RR and a serial scan is used, an SIX lock exists
at the table level, and an X lock exists at the row (or page) level. When
the COMMIT WORK is issued, the X lock is released because the SIX lock at the
table level already guarantees that no other transaction can modify any data
in the table.
If the isolation level is RR and another scan is used, or if the isolation
level is CS, an IX lock exists at the table level, and an X lock exists
at the row (or page) level. When the COMMIT WORK is issued, the X lock is
retained because it is needed to protect the row (or page) from changes
by another transaction.
When the WITH LOCKS clause is used in an RC or RU transaction, all locks
are released when you issue a COMMIT WORK statement. In these isolation
levels, the row (or page) that is currently pointed to by the cursor is
frequently not protected from changes by other transactions
(S locks are released immediately in RC and are never obtained in RU when
fetches are made). Only the REFETCH, UPDATE WHERE CURRENT, and DELETE WHERE
CURRENT cursor statements obtain and retain locks. Because the spirit of RC
and RU is to improve concurrency by letting other transactions access data as
soon as possible, SIX and X locks obtained in an RC or RU transaction are
released when you issue a COMMIT WORK statement when the WITH LOCKS clause is
used.
Printable version
