INF: Locking Behavior of Cursors on SQL Server Version 6.0

• Microsoft SQL Server, version 6.0

SUMMARY

Microsoft SQL Server version 6.0 has implemented server cursors and a user controllable setting for TRANSACTION ISOLATION LEVEL. When used together, it can be useful to know the expected locking behavior.

MORE INFORMATION

This document attempts to clarify the effect of optimizer hints and isolation levels on the locking considerations of cursors. The nonlocking parts of optimizer hints affect the cursor behavior in the following ways:

• INDEX = index_name: If the given index is unique, then this index will be used as the primary keyset for the given table. If the index is not unique, then this optimizer hint will be ignored.

  NOTE: Use of this option may cause dynamic cursors to convert to keyset based cursors if the index chosen does not match the 'ORDER BY' columns in the cursor statement exactly.

• FASTFIRSTROW: Dynamic cursors will always use this option whenever the primary keyset values chosen is from a nonclustered index. For keyset based cursors this option will be honored if used in the cursor statement, not if not given. The locking related parts of optimizer hints and isolation levels will affect cursor locking behavior as follows:
• All exclusive locks and table locks (TABLOCK and TABLOCKX) are taken and maintained inside a user transaction. These are held as long as the transaction is open (even if the cursor closes) and released when the transaction ends. If there are no transactions outstanding the TABLOCKX and TABLOCK options will only have read duration, i.e. the table lock will be taken while the rows are being read and released immediately as soon as the read is complete.
• The isolation level at the time of cursor declare will be in effect throughout the cursor. Changes made to the isolation level after the cursor is declared will not affect the locking behavior.
• The PAGELOCK keyword is ignored in cursor declare statements.
• If a certain optimizer locking hint is used for a given table in a cursor statement, it will override isolation level and cursor concurrency option for that table. For example you can open a cursor with the LOCKCC option and specify NOLOCK option on one of the tables participating in the cursor select statement. This will cause that table to be read uncommitted (with no locks) while other tables are protected by update locks.
• No data modifications (update, delete, insert) can be made to tables that have the NOLOCK optimizer hint.
• If no optimizer locking hint is used, the isolation level locking will take effect on the cursor if the cursor is opened with OPTCC, OPTCCVAL, or READONLY concurrency options. If the cursor is opened with the LOCKCC option, this option will override isolation level locking.
• OPTCC, OPTVAL, and LOCKCC concurrency options are disallowed in cursors when isolation level is 0. Also UPDLOCK and TABLOCKX keywords are disallowed as optimizer hints when at this isolation level.
• If isolation level is 0 during cursor declaration, ANSI cursors will be opened READ ONLY. Extended procedure cursors will fail to open unless READ ONLY option is specified.

RDL - Read locks: These are shared page locks that are taken while the pages are being read and released immediately after the page read is complete.

SCR -Scroll locks: These locks are taken by the cursor fetch command on pages that the rows are returned from. These locks will remain in place as long as the last fetch command has rows fetched from these pages; and released either when a succeeding fetch scrolls off these pages or the cursor is closed. These locks come in two varieties: shared SCR(sh), and update SCR(up) page locks. Only the locks on the data pages will be kept, no locks on the index pages will be taken. Furthermore, if SCROLL locking is in effect on the cursor (shared or update) and there is an outstanding transaction in effect during opening of a keyset based cursor, shared locks will be accumulated on pages generating the keyset and these locks will be kept until the transaction ends.

XT/C Locks: These page locks are taken by the cursor fetch command on pages that the rows are returned from. If a transaction is outstanding, these locks will be kept within the duration of the transaction and released when the transaction commits or rolls back. If there are no transactions, these locks will be kept as long as the cursor is open, or a new transaction is started and then ended. These locks also have shared and update varieties. XT/C(sh) and XT/C(up) for page locks

XT(tb) and XT(tbx): Shared and exclusive table locks. These locks can only be taken inside a transaction and can only live within the life of the transaction, not the cursor.

The following table attempts to clarify in detail what kind of locks will be taken with cursors under different locking hints and optimizer levels:

TABLE OPTIMIZER HINT

Cursor       Isolation
Concurr.      Level   No hints    NOLOCK    HOLDLOCK   UPDLOCK

OPTCC       Uncomm.
OPTVAL        (0)       Disallowed Disallowed Disallowed Disallowed
   -----------------------------------------------------------

              (1)       RDL         NONE      SCR(sh)    SCR(up)
   -----------------------------------------------------------
            Serial/rpt
              (2)       SCR(sh)     NONE      SCR(sh)    SCR(up)
                        XT/C(sh)              XT/C(sh)   XT/C(up)

LOCKCC      Uncomm.
              (0)       Disallowed Disallowed Disallowed Disallowed

            Comtd.
              (1)       SCR(up)    NONE       SCR(up)    SCR(up)

            Serial/rpt
              (2)       SCR(up)    NONE       SCR(up)    SCR(up)
                        XT/C(up)              XT/C(up)   XT/C(up)

READ        Uncomm.
ONLY          (0)       NONE       NONE       SCR(sh)    Disallowed

            Comtd.
              (1)       RDL        NONE       SCR(sh)    Disallowed

            Serial/rpt
              (2)       SCR(sh)    NONE       SCR(sh)    Disallowed
                        XT/C(sh)              XT/C(sh)

Part 2 TABLE OPTIMIZER HINT

Cursor       Isolation
Concurr.     Level        TABLOCK          TABLOCKX

OPTCC        Uncomm.
OPTVAL        (0)         Disallowed      Disallowed

             Comtd.     (inside xact only) (inside xact only)
              (1)         XT(tb)            XT(tbx)

             Serial/rpt
              (2)       (inside xact only) (inside xact only)
                          XT(tb)            XT(tbx)

LOCKCC       Uncomm.
              (0)         Disallowed        Disallowed

             Comtd.     (inside xact only) (inside xact only)
              (1)         XT(tb)            XT(tbx)

             Serial/rpt
              (2)       (inside xact only) (inside xact only)
                          XT(tb)            XT(tbx)

READ         Uncomm.
ONLY          (0)        NONE               Disallowed

             Comtd.
              (1)        NONE               Disallowed

             Serial/rpt
              (2)       (inside xact only)  Disallowed
                          XT(tb)

NOTE: The above two tables should also fit side by side into one table.