Commitment control
The Db2 for i commitment control support provides a means for processing a group of database changes, such as update, insert, or delete operations or data definition language (DDL) 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 run with an isolation level other than COMMIT(*NONE) or a RELEASE statement is run, then Db2 for i sets up the commitment control environment by running the internal equivalent of the Start Commitment Control (STRCMTCTL) command. Db2 for i specifies the NFYOBJ(*NONE) and CMTSCOPE(*ACTGRP) parameters, along with the LCKLVL parameter. The LCKLVL parameter specified is the lock level on the COMMIT parameter of the Create SQL (CRTSQLxxx), Start SQL Interactive Session (STRSQL), or Run SQL Statements (RUNSQLSTM) command. In REXX, the LCKLVL parameter specified is the lock level on the SET OPTION statement. You can use the STRCMTCTL command to specify a different CMTSCOPE, NFYOBJ, or LCKLVL parameter. If you specify CMTSCOPE(*JOB) to start the job-level commitment definition, Db2 for i uses the job-level commitment definition for programs in that activation group.
- When commitment control is used, the tables that are referred to in the application program by data manipulation language (DML) statements must be journaled.
- The LCKLVL parameter 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 of the CRTSQLxxx, STRSQL, or RUNSQLSTM command will override the default lock level. Also, the LCKLVL parameter only applies to commitment control operations that are requested through the IBM® i traditional system interface (non-SQL). The lock level specified on the LCKLVL parameter is not affected by any subsequent changes to the SQL isolation level that are made by using, for example, the SET TRANSACTION statement.
For cursors that use aggregate 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 for i locks all referenced tables in shared mode (*SHRNUP). The lock prevents concurrent application processes from processing 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 might also be sent.
For cursors where 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 for i locks all referenced tables in shared mode (*SHRNUP). This prevents concurrent processes from processing anything but read-only operations on the named table or tables. A message (either SQL7902 or CPI430A) is sent that says COMMIT(*ALL) is requested but not allowed. Message SQL0595 might 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.
In a highly contentious environment using COMMIT(*RR), an application might need to retry an operation after getting an SQL0913 to allow the database unlocking mechanism time to work.
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 for i uses locks on rows to keep other jobs from accessing changed data before a unit of work is completed. 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 is completed. This does 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.
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 an IBM 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 an IBM i platform. Such a cursor is closed on a rollback.
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)
MERGE, INSERT sub-statement |
*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 UPDATE3 |
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 |
MERGE, UPDATE
sub-statement |
*NONE
*CHG *CS *ALL |
From read until MERGE statement completion
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 |
MERGE, DELETE
sub-statement |
*NONE
*CHG *CS *ALL |
From read until MERGE statement completion
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:
|