Transaction recovery through commitment control
Commitment control is an extension of the IBM® i 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 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-IBM 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.
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 UPDATE3 |
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 UPDATE4 |
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:
|