Commit lock level
The value you specify for the LCKLVL parameter on the Start Commitment Control (STRCMTCTL) command becomes the default level of record locking for database files that are opened and placed under commitment control for the commitment definition.
The default level of record locking cannot be overridden when opening local database files. However, database files accessed by SQL use the current SQL isolation level in effect at the time of the first SQL statement issued against it.
The lock level must be specified with respect to your needs, the wait periods allowed, and the release procedures used most often.
The following descriptions apply only to files that are opened under commitment control:
- *CHG Lock Level
- Use this value if you want to protect changed records from changes by other jobs running at the same time. For files that are opened under commitment control, the lock is held for the duration of the transaction. For files not opened under commitment control, the lock on the record is held only from the time the record is read until the update operation is complete.
- *CS Lock Level
- Use this value to protect both changed and retrieved records from changes
by other jobs running at the same time. Retrieved records that are not changed
are protected only until they are released, or a different record is retrieved.
The *CS lock level ensures that other jobs are not able to read a record for update that this job has read. In addition, the program cannot read records for update that have been locked with a record lock type of *UPDATE in another job until that job accesses a different record.
- *ALL Lock Level
- Use this value to protect changed records and retrieved records that are
under commitment control from changes by other jobs running under commitment
control at the same time. Records that are retrieved or changed are protected
until the next commit or rollback operation.
The *ALL lock level ensures that other jobs are not able to access a record for update that this job has read. This is different from normal locking protocol. When the lock level is specified as *ALL, even a record that is not read for update cannot be accessed if it is locked with a record lock type of *UPDATE in another job.
The following table shows the duration of record locks for files under and not under commitment control.
| Request | LCKLVL parameter | Duration of lock | Lock type |
|---|---|---|---|
| Read-only | No commitment control | No lock | None |
| *CHG | No lock | None | |
| *CS | From read to next read, commit, or rollback | *READ | |
| *ALL | From read to commit or rollback | *READ | |
| Read for update then update or delete1 | No commitment control | From read to update or delete | *UPDATE |
| *CHG | From read to update or delete | *UPDATE | |
| Then from update or delete to next commit or rollback2 | *UPDATE | ||
| *CS | From read to update or delete | *UPDATE | |
| Then from update or delete to next commit or rollback2 | *UPDATE | ||
| *ALL | From read to update or delete | *UPDATE | |
| Then from update or delete to next commit or rollback2 | |||
| Read for update then release1 | No commitment control | From read to release | *UPDATE |
| *CHG | From read to release | *UPDATE | |
| *CS | From read to release, commit, or rollback | *UPDATE | |
| Then from release to next read, commit, or rollback | *UPDATE | ||
| *ALL | From read to release, commit, or rollback | *UPDATE | |
| Then from release to next commit or rollback | |||
| Add | No commitment control | No lock | None |
| *CHG | From add to commit or rollback | *UPDATE | |
| *CS | From add to commit or rollback | *UPDATE | |
| *ALL | From add to commit or rollback | *UPDATE | |
| Write direct | No commitment control | For duration of write direct | *UPDATE |
| *CHG | From write direct to commit or rollback | *UPDATE | |
| *CS | From write direct to commit or rollback | *UPDATE | |
| *ALL | From write direct to commit or rollback | *UPDATE | |
| Notes:
1If a commit or rollback operation is performed after a read-for-update operation but before the record is updated, deleted, or released, the record is unlocked during the commit or rollback operation. The protection on the record is lost as soon as the commit or rollback completes. 2If a record is deleted but the commit or rollback has not yet been issued for the transaction, the deleted record does not remain locked. If the same or a different job attempts to read the deleted record by key, the job receives a record not found indication. However, if a unique keyed access path exists over the file, another job is prevented from inserting or updating a record with the same unique key value as that of the deleted record until the transaction is committed. |
|||
A record lock type of *READ is obtained on records that are not read for update when the lock level is *CS or *ALL. This type of lock prevents other jobs from reading the records for update but does not prevent the records from being accessed from a read-only operation.
A record lock type of *UPDATE is obtained on records that are updated, deleted, added, or read for update. This type of lock prevents other jobs from reading the records for update, and prevents jobs running under commitment control with a record lock level of *CS or *ALL from accessing the records for even a read-only operation.
Programs that are not using commitment control can read records locked by another job, but cannot read records for update, regardless of the value specified for the LCKLVL parameter.
The lock level, specified for a commitment definition when commitment control is started for an activation group or for the job, applies only to opens associated with that particular commitment definition.
Within the same job, a program can change a record that has already been changed within the current transaction as long as the record is accessed again using the same commitment definition. When using the job-level commitment definition, the access to the changed record can be made from a program running within any activation group that is using the job-level commitment definition.