Transaction recovery through commitment control

Transaction recovery through commitment control

Commitment control is an extension of the i5/OS^® journal management function. The system can identify and process a group of relational database changes as a single unit of work (transaction).
An SQL COMMIT statement guarantees that the group of operations is completed. An SQL ROLLBACK statement guarantees that the group of operations is backed out. The only SQL statements that cannot be committed or rolled back are:

DROP COLLECTION

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

Under commitment control, tables and rows used during a transaction are locked from other jobs. This ensures that other jobs do not use the data until the transaction is complete. At the end of the transaction, the program issues an SQL COMMIT or ROLLBACK statement, freeing the rows. If the system or job ends abnormally before the commit operation is performed, all changes for that job since the last time a commit or rollback operation occurred are rolled back. Any affected rows that are still locked are then unlocked. The lock levels are as follows:

*NONE

Commitment control is not used. Uncommitted changes in other jobs can be seen.

*CHG

Objects referred to in SQL ALTER, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, and REVOKE statements and the rows updated, deleted, and inserted are locked until the unit of work (transaction) is completed. Uncommitted changes in other jobs can be seen.

*CS

Objects referred to in SQL ALTER, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, and REVOKE statements and the rows updated, deleted, and inserted are locked until the unit of work (transaction) is completed. A row that is selected, but not updated, is locked until the next row is selected. Uncommitted changes in other jobs cannot be seen.

*ALL

Objects referred to in SQL ALTER, COMMENT ON, CREATE, DROP, GRANT, LABEL ON, and REVOKE statements and the rows read, updated, deleted, and inserted are locked until the end of the unit of work (transaction). Uncommitted changes in other jobs cannot be seen.

Table 1 shows the record lock duration for each of these lock level values. If you request COMMIT (*CHG), COMMIT (*CS), or COMMIT (*ALL) when the program is precompiled or when interactive SQL is started, then SQL sets up the commitment control environment by implicitly calling the Start Commitment Control (STRCMTCTL) command. The LCKLVL parameter specified when SQL starts commitment control is the lock level specified on the COMMIT parameter on the CRTSQLxxx commands. NFYOBJ(*NONE) is specified when SQL starts commitment control. To specify a different NFYOBJ parameter, issue a (STRCMTCTL) command before starting SQL.
When running with commitment control, the tables referred to in the application program by data manipulation language statements must be journaled. The tables do not have to be journaled at precompile time, but they must be journaled when you run the application.
If a remote relational database is accessing data on the system and requesting commit-level repeatable read (*RR), the tables are locked until the query is closed. If the cursor is read only, the table is locked (*SHRNUP). If the cursor is in update mode, the table is locked (*EXCLRD).
The journal created in the SQL collection is normally the journal used for logging all changes to SQL tables. You can, however, use the system journal functions to journal SQL tables to a different journal.
Commitment control can handle up to 500 000 000 distinct row changes in a unit of work. If COMMIT(*ALL) is specified, all rows read are also included in the 500 000 000 limit. (If a row is changed or read more than once in a unit of work, it is only counted once toward the 500 000 000 limit.) Maintaining 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 unit of work is completed. It is, therefore, more efficient to keep the number of rows that are processed in a unit of work small.
The HOLD value on COMMIT and ROLLBACK statements allows you to keep the cursor open and start another unit of work without issuing an OPEN statement again. If non-System i™ connections are not released for a program and SQL is still in the call stack, the HOLD value is not available. If ALWBLK(*ALLREAD) and either COMMIT(*CHG) or COMMIT(*CS) are specified when the program is precompiled, all read-only cursors allow blocking of rows and a ROLLBACK HOLD statement does not roll the cursor position back.
If there are locked rows (records) pending from running a SQL precompiled program or an interactive SQL session, a COMMIT or ROLLBACK statement can be issued from the server Command Entry display. Otherwise, an implicit ROLLBACK operation occurs when the job is ended.
You can use the Work with Commitment Definitions (WRKCMTDFN) command to monitor the status of commitment definitions and to free locks and held resources that are involved with commitment control activities across systems.

Table 1. Record lock duration
SQL statement COMMIT parameter Duration of record locks Lock type

SELECT INTO
*NONE
*CHG
*CS
*ALL (See note 2)

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
*ALL (See note 2)

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 record not updated or deleted
from read until next FETCH
When record is updated or deleted
from read until UPDATE or DELETE
When record not updated or deleted
from read until next FETCH
When record is updated or deleted
from read until UPDATE or DELETE
When record not updated or deleted
from read until next FETCH
When record is updated or deleted
from read until UPDATE or DELETE
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 record locked while being read
From read until ROLLBACK or COMMIT

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

Each record 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 record 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

Lock released when record updated
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 record 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 record 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 (see description of DECLARE CURSOR) and if one of the following items is true:

The cursor is defined with a FOR UPDATE clause.

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

Cursor UPDATE referring to the same cursor-name

Cursor DELETE referring to the same cursor-name

An EXECUTE or EXECUTE IMMEDIATE statement with ALWBLK(*READ) or ALWBLK(*NONE) 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 group by or 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.

If the row is not updated or deleted, the lock is reduced to *READ.

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.

Parent topic:
Recovery support for a distributed relational database

Related concepts

Troubleshooting transactions and commitment control

Related tasks

Working with commitment definitions in a distributed relational database

Related reference

DECLARE CURSOR
Start Commitment Control (STRCMTCTL) command
Work with Commitment Definitions (WRKCMTDFN) command
Save and restore processing for a distributed relational database
Save Object (SAVOBJ) command
Save Library (SAVLIB) command
Save Changed Object (SAVCHGOBJ) command
Save Save File Data (SAVSAVFDTA) command
Save System (SAVSYS) command
Restore Library (RSTLIB) command
Restore Object (RSTOBJ) command
Restore User Profiles (RSTUSRPRF) command
Restore Authority (RSTAUT) command
Restore Configuration (RSTCFG) command

SQL statement	COMMIT parameter	Duration of record locks	Lock type
SELECT INTO	NONE CHG CS ALL (See note 2)	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 ALL (See note 2)	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 record not updated or deleted from read until next FETCH When record is updated or deleted from read until UPDATE or DELETE When record not updated or deleted from read until next FETCH When record is updated or deleted from read until UPDATE or DELETE When record not updated or deleted from read until next FETCH When record is updated or deleted from read until UPDATE or DELETE 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 record locked while being read From read until ROLLBACK or COMMIT	READ READ
UPDATE (non-cursor)	NONE CHG CS ALL	Each record 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 record 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	Lock released when record updated 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 record 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 record 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 (see description of DECLARE CURSOR) and if one of the following items is true: The cursor is defined with a FOR UPDATE clause. The cursor is defined without a FOR UPDATE, FOR FETCH ONLY, or ORDER BY clause and the program contains at least one of the following items: Cursor UPDATE referring to the same cursor-name Cursor DELETE referring to the same cursor-name An EXECUTE or EXECUTE IMMEDIATE statement with ALWBLK(READ) or ALWBLK(NONE) 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 group by or 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. If the row is not updated or deleted, the lock is reduced to READ. 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.