Locks acquired for SQL statements

When SQL statements access or modify data, locks must be acquired to prevent other applications from accessing data that has been changed but not committed. How and when the locks are acquired for a particular SQL statement depend on the type of processing, the access method, and attributes of the application and target tables.

Begin program-specific programming interface information.
The following tables show the locks that certain SQL processes acquire and the modes of those locks. Whether locks are acquired at all and the mode of those locks depend on the following factors:

  • The type of processing being performed
  • The value of LOCKSIZE for the target table
  • The isolation level of the plan, package, or statement
  • The method of access to data
  • Whether the application uses the SKIP LOCKED DATA option

Example SQL statement

The following SQL statement and sample steps provide a way to understand the following tables.

EXEC SQL DELETE FROM DSN8A10.EMP WHERE CURRENT OF C1;

Use the following sample steps to understand the table:

  1. Find the portion of the table that describes DELETE operations using a cursor.
  2. Find the row for the appropriate values of LOCKSIZE and ISOLATION. Table space DSN8A10 is defined with LOCKSIZE ANY. The default value of ISOLATION is CS with CURRENTDATA (NO) by default.
  3. Find the sub-row for the expected access method. The operation probably uses the index on employee number. Because the operation deletes a row, it must update the index. Hence, you can read the locks acquired in the sub-row for "Index, updated":
    • An IX lock on the table space
    • An IX lock on the table (but see the step that follows)
    • An X lock on the page containing the row that is deleted
  4. Check the notes to the entries you use, at the end of the table. For this sample operation, see:
    • Note 2, on the column heading for "Table". If the table is not segmented, or if the table is segmented and partitioned, no separate lock is taken on the table.
    • Note 3, on the column heading for "Data Page or Row". Because LOCKSIZE for the table space is ANY, DB2® can choose whether to use page locks, table locks, or table space locks. Typically it chooses page locks.

SELECT with read-only cursor, ambiguous cursor, or no cursor

The following table shows locks that are acquired during the processing of SELECT with read-only or ambiguous cursor, or with no cursor SQL statements. UR isolation is allowed and requires none of these locks.

Table 1. Locks acquired for SQL statements SELECT with read-only or ambiguous cursor
LOCKSIZE ISOLATION Access method1 Lock Mode Table space Lock Mode Table Lock Mode Data Page or Row
TABLESPACE CS RS RR Any S n/a n/a
TABLE2 CS RS RR Any IS S n/a
PAGE, ROW, or ANY CS Index, any use IS4, 10 IS4 S5
Table space scan IS4, 10 IS4 S5
PAGE, ROW, or ANY RS Index, any use IS4, 10 IS4, 11 or IX S5, U11, or X11
Table space scan IS4, 10, 11 or IX IS4, 11 S5, U11, or X11
PAGE, ROW, or ANY RR Index/data probe IS4, 11 IS4, 11 S5, U11, or X11
Index scan6 IS4, 11, IX, or S S, IS4, 11, IX, or n/a S5, U11, X11, or n/a
Table space scan6 IS2or S S or n/a n/a

INSERT, VALUES(...), or INSERT fullselect7

The following table shows locks that are acquired during the processing of INSERT, VALUES(...), or INSERT fullselect SQL statements.

Table 2. Locks acquired for SQL statements INSERT ... VALUES(...) or INSERT ... fullselect
LOCKSIZE ISOLATION Access method1 Lock Mode Table space9 Lock Mode Table2 Lock Mode Data Page or Row3
TABLESPACE CS RS RR Any X n/a n/a
TABLE2 CS RS RR Any IX X n/a
PAGE, ROW, or ANY CS RS RR Any IX IX X

UPDATE or DELETE without cursor

The following table shows locks that are acquired during the processing of UPDATE or DELETE without curso SQL statements. Data page and row locks apply only to selected data.

Table 3. Locks acquired for SQL statements UPDATE, or DELETE without cursor
LOCKSIZE ISOLATION Access method1 Lock Mode Table space9 Lock Mode Table2 Lock Mode Data Page or Row3
TABLESPACE CS RS RR Any X n/a n/a
TABLE2 CS RS RR Any IX X n/a
PAGE, ROW, or ANY CS Index selection IX IX
  • For delete: X
  • For update: U 8→X
Index/data selection IX IX U8→X
Table space scan IX IX U8→X
PAGE, ROW, or ANY RS Index selection IX IX
  • For update: S or U8, 11, 12 →X
  • For delete: [S→X]8 or X
Index/data selection IX IX S or U8, 11, 12 →X
Table space scan IX IX S or U8, 11, 12 →X
PAGE, ROW, or ANY RR Index selection IX IX
  • For update: [S or U→X]8, 11, 12 or X
  • For delete: [S→X] or X
Index/data selection IX IX S or U8, 11, 12 →X
Table space scan IX2 or X X or n/a n/a

SELECT with FOR UPDATE OF

The following table shows locks that are acquired during the processing of SELECT with FOR UPDATE OF SQL statements. Data page and row locks apply only to selected data.

Table 4. Locks acquired for SQL statements SELECT with FOR UPDATE OF
LOCKSIZE ISOLATION Access method1 Lock Mode Table space9 Lock Mode Table2 Lock Mode Data Page or Row3
TABLESPACE CS RS RR Any S or U12 n/a n/a
TABLE2 CS RS RR Any IS or IX U n/a
PAGE, ROW, or ANY CS Index, any use IX IX U
Table space scan IX IX U
PAGE, ROW, or ANY RS Index, any use IX IX S, U, or X11, 12
Table space scan IX IX S, U, or X11, 12
PAGE, ROW, or ANY RR Index/data probe IX IX S, U, or X11, 12
Index scan6 IX or X X, IX, or n/a S, U, X11, 12, or n/a
Table space scan6 IX2 or X X or n/a S, U, X11, 12, or n/a

UPDATE or DELETE with cursor

The following table shows locks that are acquired during the processing of xxx SQL statements.

Table 5. Locks acquired for SQL statements UPDATE or DELETE with cursor
LOCKSIZE ISOLATION Access method1 Lock Mode Table space9 Lock Mode Table2 Lock Mode Data Page or Row3
TABLESPACE Any Any X n/a n/a
TABLE2 Any Any IX X n/a
PAGE, ROW, or ANY CS, RS, or RR Index, updated IX IX X
Index not updated IX IX X

Mass delete or TRUNCATE

Lock modes for TRUNCATE depend solely on the type of tables space regardless of LOCKSIZE or isolation level:

Simple table space
Locks the table space with an X lock
Segmented table space (not partitioned)
Locks the table with an X lock and lock the table space with an IX lock
Partitioned table space (including segmented)
Locks each partition with an X lock

Notes for this topic

  1. All access methods are either scan-based or probe-based. Scan-based means the index or table space is scanned for successive entries or rows. Probe-based means the index is searched for an entry as opposed to a range of entries, which a scan does. ROWIDs provide data probes to look for a single data row directly. The type of lock used depends on the backup access method. Access methods might be index-only, data-only, or index-to-data.
    Index-only
    The index alone identifies qualifying rows and the return data.
    Data-only:
    The data alone identifies qualifying rows and the return data, such as a table space scan or the use of ROWID for a probe.
    Index-to-data
    The index is used or the index plus data are used to evaluate the predicate:
    Index selection
    The index is used to evaluate predicate and data is used to return values.
    Index/data selection
    The index and data are used to evaluate predicate and data is used to return values.
  2. Used only for segmented table spaces that are not partitioned.
  3. These locks are taken on pages if LOCKSIZE is PAGE or on rows if LOCKSIZE is ROW. When the maximum number of locks per table space (LOCKMAX) is reached, locks escalate to a table lock for tables in a segmented table space without partitions, or to a table space lock for tables in a non-segmented table space. Using LOCKMAX 0 in CREATE or ALTER TABLESPACE disables lock escalation.
  4. If the table or table space is started for read-only access, DB2 attempts to acquire an S lock. If an incompatible lock already exists, DB2 acquires the IS lock.
  5. SELECT statements that do not use a cursor, or that use read-only or ambiguous cursors and are bound with CURRENTDATA(NO), might not require any lock if DB2 can determine that the data to be read is committed. This is known as lock avoidance. If your application can tolerate incomplete or inconsistent results, you can also specify the SKIP LOCKED DATA option in your query to avoid lock wait times.
  6. Even if LOCKMAX is 0, the bind process can promote the lock size to TABLE or TABLESPACE. If that occurs, SQLCODE +806 is issued.
  7. The locks listed are acquired on the object into which the insert is made. A subselect acquires additional locks on the objects it reads, as if for SELECT with read-only cursor or ambiguous cursor, or with no cursor.
  8. Start of changeWhen the value of the XLKUPDLT subsystem parameter is YES, the initial lock is an X-lock.End of change
  9. Includes partition locks, and does not include LOB table space locks.
  10. If the table space is partitioned, locks can be avoided on the partitions.
  11. Start of changeIf you use the WITH clause to specify the isolation as RR or RS, you can use the USE AND KEEP UPDATE LOCKS option to obtain and hold a U-lock instead of an S-lock, or you can use the USE AND KEEP EXCLUSIVE LOCKS option to obtain and hold an X-lock instead of an S-lock. If page or row locks are used, the table space and table locks are IX-locks. If page and row locks are not used, because a gross lock is acquired on the table or table space, the lock is a U-lock for USE AND KEEP UPDATE LOCKS or an X-lock for USE AND KEEP EXCLUSIVE locks.End of change
  12. The RRULOCK subsystem parameter controls the type of lock that is acquired for isolation levels RS and RR. If RRULOCK=YES, a U-lock is acquired. Otherwise, an S-lock is acquired.
    End program-specific programming interface information.