Commitment control

Commitment control

The DB2^® UDB for iSeries™ commitment control support provides a means for processing a group of database changes, such as update, insert, data definition language (DDL), or delete operations, as a single unit of work (also referred to as a transaction).
A commit operation guarantees that the group of operations is completed. A rollback operation guarantees that the group of operations is backed out. A savepoint can be used to break a transaction into smaller units that can be rolled back. A commit operation can be issued through several different interfaces. For example,

An SQL COMMIT statement

A CL COMMIT command

A language commit statement (such as an RPG COMMIT statement)

A rollback operation can be issued through several different interfaces. For example,

An SQL ROLLBACK statement

A CL ROLLBACK command

A language rollback statement (such as an RPG ROLBK statement)

The only SQL statements that cannot be committed or rolled back are:

DROP SCHEMA

GRANT or REVOKE if an authority holder exists for the specified object

If commitment control was not already started when either an SQL statement is executed with an isolation level other than COMMIT(*NONE) or a RELEASE statement is executed, then DB2 UDB for iSeries sets up the commitment control environment by implicitly calling the CL command Start Commitment Control (STRCMTCTL). DB2 UDB for iSeries specifies NFYOBJ(*NONE) and CMTSCOPE(*ACTGRP) parameters along with LCKLVL on the STRCMTCTL command. The LCKLVL specified is the lock level on the COMMIT parameter on the CRTSQLxxx, STRSQL, or RUNSQLSTM commands. In REXX, the LCKLVL specified is the lock level on the SET OPTION statement. You may use the STRCMTCTL command to specify a different CMTSCOPE, NFYOBJ, or LCKLVL. If you specify CMTSCOPE(*JOB) to start the job level commitment definition, DB2 UDB for iSeries uses the job level commitment definition for programs in that activation group. Notes:

When using commitment control, the tables referred to in the application program by Data Manipulation Language statements must be journaled.

Note that the LCKLVL specified is only the default lock level. After commitment control is started, the SET TRANSACTION SQL statement and the lock level specified on the COMMIT parameter on the CRTSQLxxx, STRSQL, or RUNSQLSTM commands will override the default lock level.
For cursors that use column functions, GROUP BY, or HAVING, and are running under commitment control, a ROLLBACK HOLD has no effect on the cursor's position. In addition, the following occurs under commitment control:

If COMMIT(*CHG) and (ALWBLK(*NO) or (ALWBLK(*READ)) is specified for one of these cursors, a message (CPI430B) is sent that says COMMIT(*CHG) requested but not allowed.

If COMMIT(*ALL), COMMIT(*RR), or COMMIT(*CS) with the KEEP LOCKS clause is specified for one of the cursors, DB2 UDB for iSeries will lock all referenced tables in shared mode (*SHRNUP). The lock prevents concurrent application processes from executing any but read-only operations on the named table. A message (either SQL7902 or CPI430A) is sent that says COMMIT(*ALL), COMMIT(*RR), or COMMIT(*CS) with the KEEP LOCKS clause is specified for one of the cursors requested but not allowed. Message SQL0595 may also be sent.

For cursors where either COMMIT(*ALL), COMMIT(*RR), or COMMIT(*CS) with the KEEP LOCKS clause is specified and either catalog files are used or a temporary result table is required, DB2 UDB for iSeries will lock all referenced tables in shared mode (*SHRNUP). This will prevent concurrent processes from executing anything but read-only operations on the table(s). A message (either SQL7902 or CPI430A) is sent that says COMMIT(*ALL) is requested but not allowed. Message SQL0595 may also be sent.
If ALWBLK(*ALLREAD) and COMMIT(*CHG) were specified, when the program was precompiled, all read-only cursors will allow blocking of rows and a ROLLBACK HOLD will not roll the cursor position back.
If COMMIT(*RR) is requested, the tables will be locked until the query is closed. If the cursor is read-only, the table will be locked (*SHRNUP). If the cursor is in update mode, the table will be locked (*EXCLRD). Since other users will be locked out of the table, running with repeatable read will prevent concurrent access of the table.
If an isolation level other then COMMIT(*NONE) was specified and the application issues a ROLLBACK or the activation group ends abnormally (and the commitment definition is not *JOB), all updates, inserts, deletes, and DDL operations made within the unit of work are backed out. If the application issues a COMMIT or the activation group ends normally, all updates, inserts, deletes, and DDL operations made within the unit of work are committed.
DB2 UDB for iSeries uses locks on rows to keep other jobs from accessing changed data before a unit of work completes. If COMMIT(*ALL) is specified, read locks on rows fetched are also used to prevent other jobs from changing data that was read before a unit of work completes. This will not prevent other jobs from reading the unchanged rows. This ensures that, if the same unit of work rereads a row, it gets the same result. Read locks do not prevent other jobs from fetching the same rows.
Commitment control handles up to 500 million distinct row changes in a unit of work. If COMMIT(*ALL) or COMMIT(*RR) is specified, all rows read are also included in the limit. (If a row is changed or read more than once in a unit of work, it is only counted once toward the limit.) Holding a large number of locks adversely affects system performance and does not allow concurrent users to access rows locked in the unit of work until the end of the unit of work. It is in your best interest to keep the number of rows processed in a unit of work small.
Commitment control will allow up to 512 files for each journal to be open under commitment control or closed with pending changes in a unit of work.
COMMIT HOLD and ROLLBACK HOLD allow you to keep the cursor open and start another unit of work without issuing an OPEN statement again. The HOLD value is not available when you are connected to a remote database that is not on a System i™ platform. However, the WITH HOLD option on DECLARE CURSOR can be used to keep the cursor open after a commit. This type of cursor is supported when you are connected to a remote database that is not on a System i platform. Such a cursor is closed on a rollback.

Table 1. Row lock duration
SQL statement COMMIT parameter (see note 5) Duration of row locks Lock type

SELECT INTO
SET variable
VALUES INTO

*NONE
*CHG
*CS (See note 6)
*ALL (See note 2 and 7)

No locks
No locks
Row locked when read and released
From read until ROLLBACK or COMMIT

READ
READ
FETCH (read-only cursor)
*NONE
*CHG
*CS (See note 6)
*ALL (See note 2 and 7)

No locks
No locks
From read until the next FETCH
From read until ROLLBACK or COMMIT

READ
READ
FETCH (update or delete capable cursor) (See note 1)
*NONE

*CHG

*CS

*ALL

When row is not updated or deleted
from read until next FETCH
When row is updated
from read until next FETCH
When row is deleted
from read until next DELETE
When row is not updated or deleted
from read until next FETCH
When row is updated or deleted
from read until COMMIT or ROLLBACK
When row is not updated or deleted
from read until next FETCH
When row is updated or deleted
from read until COMMIT or ROLLBACK
From read until ROLLBACK or COMMIT

UPDATE

UPDATE

UPDATE

UPDATE
INSERT (target table)
*NONE
*CHG
*CS
*ALL

No locks
From insert until ROLLBACK or COMMIT
From insert until ROLLBACK or COMMIT
From insert until ROLLBACK or COMMIT

UPDATE
UPDATE
UPDATE³
INSERT (tables in subselect)
*NONE
*CHG
*CS
*ALL

No locks
No locks
Each row locked while being read
From read until ROLLBACK or COMMIT

READ
READ
UPDATE (non-cursor)
*NONE
*CHG
*CS
*ALL

Each row locked while being updated
From read until ROLLBACK or COMMIT
From read until ROLLBACK or COMMIT
From read until ROLLBACK or COMMIT

UPDATE
UPDATE
UPDATE
UPDATE
DELETE (non-cursor)
*NONE
*CHG
*CS
*ALL

Each row locked while being deleted
From read until ROLLBACK or COMMIT
From read until ROLLBACK or COMMIT
From read until ROLLBACK or COMMIT

UPDATE
UPDATE
UPDATE
UPDATE
UPDATE (with cursor)
*NONE
*CHG
*CS
*ALL

From read until next FETCH
From read until ROLLBACK or COMMIT
From read until ROLLBACK or COMMIT
From read until ROLLBACK or COMMIT

UPDATE
UPDATE
UPDATE
UPDATE
DELETE (with cursor)
*NONE
*CHG
*CS
*ALL

Lock released when row deleted
From read until ROLLBACK or COMMIT
From read until ROLLBACK or COMMIT
From read until ROLLBACK or COMMIT

UPDATE
UPDATE
UPDATE
UPDATE
Subqueries (update or delete capable cursor or UPDATE or DELETE non-cursor)
*NONE
*CHG
*CS
*ALL (see note 2)

From read until next FETCH
From read until next FETCH
From read until next FETCH
From read until ROLLBACK or COMMIT

READ
READ
READ
READ
Subqueries (read-only cursor or SELECT INTO)
*NONE
*CHG
*CS
*ALL

No locks
No locks
Each row locked while being read
From read until ROLLBACK or COMMIT

READ
READ
Notes:

A cursor is open with UPDATE or DELETE capabilities if the result table is not read-only and if one of the following is true:

The cursor is defined with a FOR UPDATE clause.

The cursor is defined without a FOR UPDATE, FOR READ ONLY, or ORDER BY clause and the program contains at least one of the following:

Cursor UPDATE referring to the same cursor-name

Cursor DELETE referring to the same cursor-name

An EXECUTE or EXECUTE IMMEDIATE statement and ALWBLK(*READ) or ALWBLK(*NONE) was specified on the CRTSQLxxx command.

A table or view can be locked exclusively in order to satisfy COMMIT(*ALL). If a subselect is processed that includes a UNION, or if the processing of the query requires the use of a temporary result, an exclusive lock is acquired to protect you from seeing uncommitted changes.

An UPDATE lock on rows of the target table and a READ lock on the rows of the subselect table.

A table or view can be locked exclusively in order to satisfy repeatable read. Row locking is still done under repeatable read. The locks acquired and their duration are identical to *ALL.

Repeatable read (*RR) row locks will be the same as the locks indicated for *ALL.

If the KEEP LOCKS clause is specified with *CS, any read locks are held until the cursor is closed or until a COMMIT or ROLLBACK is done. If no cursors are associated with the isolation clause, then locks are held until the completion of the SQL statement.

If the USE AND KEEP EXCLUSIVE LOCKS clause is specified with the *RS or *RR isolation level, an UPDATE lock on the row will be obtained instead of a READ lock.

Parent topic:
Data integrity

Related concepts

Commitment control

Related reference

DECLARE CURSOR
Isolation level

SQL statement	COMMIT parameter (see note 5)	Duration of row locks	Lock type
SELECT INTO SET variable VALUES INTO	NONE CHG CS (See note 6) ALL (See note 2 and 7)	No locks No locks Row locked when read and released From read until ROLLBACK or COMMIT	READ READ
FETCH (read-only cursor)	NONE CHG CS (See note 6) ALL (See note 2 and 7)	No locks No locks From read until the next FETCH From read until ROLLBACK or COMMIT	READ READ
FETCH (update or delete capable cursor) (See note 1)	NONE CHG CS ALL	When row is not updated or deleted from read until next FETCH When row is updated from read until next FETCH When row is deleted from read until next DELETE When row is not updated or deleted from read until next FETCH When row is updated or deleted from read until COMMIT or ROLLBACK When row is not updated or deleted from read until next FETCH When row is updated or deleted from read until COMMIT or ROLLBACK From read until ROLLBACK or COMMIT	UPDATE UPDATE UPDATE UPDATE
INSERT (target table)	NONE CHG CS ALL	No locks From insert until ROLLBACK or COMMIT From insert until ROLLBACK or COMMIT From insert until ROLLBACK or COMMIT	UPDATE UPDATE UPDATE³
INSERT (tables in subselect)	NONE CHG CS ALL	No locks No locks Each row locked while being read From read until ROLLBACK or COMMIT	READ READ
UPDATE (non-cursor)	NONE CHG CS ALL	Each row locked while being updated From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT	UPDATE UPDATE UPDATE UPDATE
DELETE (non-cursor)	NONE CHG CS ALL	Each row locked while being deleted From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT	UPDATE UPDATE UPDATE UPDATE
UPDATE (with cursor)	NONE CHG CS ALL	From read until next FETCH From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT	UPDATE UPDATE UPDATE UPDATE
DELETE (with cursor)	NONE CHG CS ALL	Lock released when row deleted From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT From read until ROLLBACK or COMMIT	UPDATE UPDATE UPDATE UPDATE
Subqueries (update or delete capable cursor or UPDATE or DELETE non-cursor)	NONE CHG CS ALL (see note 2)	From read until next FETCH From read until next FETCH From read until next FETCH From read until ROLLBACK or COMMIT	READ READ READ READ
Subqueries (read-only cursor or SELECT INTO)	NONE CHG CS ALL	No locks No locks Each row locked while being read From read until ROLLBACK or COMMIT	READ READ
Notes: A cursor is open with UPDATE or DELETE capabilities if the result table is not read-only and if one of the following is true: The cursor is defined with a FOR UPDATE clause. The cursor is defined without a FOR UPDATE, FOR READ ONLY, or ORDER BY clause and the program contains at least one of the following: Cursor UPDATE referring to the same cursor-name Cursor DELETE referring to the same cursor-name An EXECUTE or EXECUTE IMMEDIATE statement and ALWBLK(READ) or ALWBLK(NONE) was specified on the CRTSQLxxx command. A table or view can be locked exclusively in order to satisfy COMMIT(ALL). If a subselect is processed that includes a UNION, or if the processing of the query requires the use of a temporary result, an exclusive lock is acquired to protect you from seeing uncommitted changes. An UPDATE lock on rows of the target table and a READ lock on the rows of the subselect table. A table or view can be locked exclusively in order to satisfy repeatable read. Row locking is still done under repeatable read. The locks acquired and their duration are identical to ALL. Repeatable read (RR) row locks will be the same as the locks indicated for ALL. If the KEEP LOCKS clause is specified with CS, any read locks are held until the cursor is closed or until a COMMIT or ROLLBACK is done. If no cursors are associated with the isolation clause, then locks are held until the completion of the SQL statement. If the USE AND KEEP EXCLUSIVE LOCKS clause is specified with the RS or *RR isolation level, an UPDATE lock on the row will be obtained instead of a READ lock.