Database lock considerations
These tables show the types of locks that some of the database functions place on database files, the valid lock combinations, and the types of locks for constraints.
Table 1 summarizes some of the most commonly used database functions and the types of locks they place on database files. The types of locks are explained on the next page.
| Function | Command | File lock | Member/Data lock | Access path lock |
|---|---|---|---|---|
| Add Member | ADDPFM, ADDLFM | *EXCLRD | *EXCLRD | |
| Add Physical File Trigger | ADDPFTRG | *EXCL5 | ||
| Change File Attributes | CHGPF, CHGLF | *EXCL | *EXCLRD | *EXCLRD |
| Change Member Attributes | CHGPFM, CHGLFM | *SHRRD | *EXCLRD | |
| Change Object Owner | CHGOBJOWN | *EXCL | ||
| Change Physical File Trigger | CHGPFTRG | *EXCL5 | ||
| Check Object | CHKOBJ | *SHRNUPD | ||
| Clear Physical File Member | CLRPFM | *SHRRD | *EXCLRD3 | |
| Create Duplicate Object | CRTDUPOBJ | *EXCL (new object) *SHRNUPD (object) | ||
| Create File | CRTPF, CRTLF, CRTSRCPF | *EXCL | ||
| Delete File | DLTF | *EXCL | *EXCLRD | |
| Grant/Revoke Authority | GRTOBJAUT, RVKOBJAUT | *EXCL5 | *EXCL6 | |
| Initialize Physical File Member | INZPFM | *SHRRD | *EXCLRD | |
| Move Object | MOVOBJ | *EXCL | ||
| Open File | OPNDBF, OPNQRYF | *SHRRD | *SHRRD | *EXCLRD |
| Rebuild Access Path | EDTRBDAP, OPNDBF | *SHRRD | *SHRRD | *EXCLRD |
| Remove Member | RMVM | *EXCLRD | *EXCL | *EXCLRD |
| Remove Physical File Trigger | RMVPFTRG | *EXCL5 | ||
| Rename File | RNMOBJ | *EXCL | *EXCL | *EXCL |
| Rename Member | RNMM | *EXCLRD | *EXCL | *EXCL |
| Reorganize Physical File Member | RGZPFM | *SHRRD | *EXCL4 | |
| Restore File | RSTLIB, RSTOBJ | *EXCL | ||
| Save File | SAVLIB, SAVOBJ, SAVCHGOBJ | *SHRNUPD1 | *SHRNUPD2 |
1 For save-while-active, the file lock is *SHRUPD initially, and then the lock is reduced to *SHRRD. See Save-while-active function for a description of save-while-active locks for the save commands.
2 For save-while-active, the member/data lock is *SHRRD.
3 The clear operation does not happen if the member is open in this process or in any other process.
4 If ALWCANCEL(*YES) is specified, the LOCK keyword can specify a *SHRUPD or *EXCLRD lock instead.
5 If the QAQQINI option ALLOW_DDL_CHANGES_WHILE_OPEN *YES is specified, an *EXCLRD lock is acquired instead.
6 If the QAQQINI option ALLOW_DDL_CHANGES_WHILE_OPEN *YES is specified, a *SHRUPD lock is acquired instead. For a grant operation that is not running under commit, a *SHRUPD lock is acquired instead.
The following table shows the valid lock combinations:
| Lock | *EXCL | *EXCLRD | *SHRUPD | *SHRNUPD | *SHRRD |
|---|---|---|---|---|---|
| *EXCL1 | |||||
| *EXCLRD2 | X | ||||
| *SHRUPD3 | X | X | |||
| *SHRNUPD4 | X | X | |||
| *SHRRD5 | X | X | X | X |
1 Exclusive lock (*EXCL). The object is allocated for the exclusive use of the requesting job; no other job can use the object.
2 Exclusive lock, allow read (*EXCLRD). The object is allocated to the job that requested it, but other jobs can read the object.
3 Shared lock, allow read and update (*SHRUPD). The object can be shared either for read or change with other jobs.
4 Shared lock, read only (*SHRNUPD). The object can be shared for read with other jobs.
5 Shared lock (*SHRRD). The object can be shared with another job if the job does not request exclusive use of the object.
Table 2 shows database locking for constraints of a database file, depending on whether the constraint is associated with the parent file (PAR) or the dependent file (DEP).
| Type of function | File type | File5 | Member5 | Other file | Other member |
|---|---|---|---|---|---|
| ADDPFM1 | DEP | *EXCL | *EXCL | *EXCL | *EXCL |
| ADDPFM1 | PAR | *EXCL | *EXCL | *EXCL | *EXCL |
| ADDPFCST7 | *REFCST | *EXCL | *EXCL | *EXCL8 | *EXCL8 |
| ADDPFCST6 | *UNQCST *PRIKEY | *EXCL | *EXCL | *EXCL | *EXCL |
| ADDPFCST | *UNIQUE *PRIKEY | *EXCL | *EXCL | ||
| RMVM2 | DEP | *EXCL | *EXCL | *EXCL | *EXCL |
| RMVM2 | PAR | *EXCL | *EXCL | *EXCL | *EXCL |
| DLTF3 | DEP | *EXCL | *EXCL | *EXCL8 | *EXCL8 |
| DLTF3 | PAR | *EXCL | *EXCL | *EXCL | *EXCL |
| RMVPFCST7 | *REFCST | *EXCL | *EXCL | *EXCL4, 8 | *EXCL8 |
| RMVPFCST6 | *UNQCST *PRIKEY | *EXCL | *EXCL | *EXCL | *EXCL |
| RMVPFCST | *UNIQUE *PRIKEY | *EXCL | *EXCL | ||
| CHGPFCST | *EXCL | *EXCL | *SHRRD | *EXCL |
1 If adding a physical file member causes a referential constraint to be established.
2 If removing a physical file member causes an established referential constraint to become defined.
3 When deleting a dependent or parent file that has constraints established or defined for the file.
4 When the Remove Physical File Constraint (RMVPFCST) command is invoked for the parent file which has constraints established or defined, the parent and any logical files over the parent file are all locked *EXCL.
5 For referential constraints, the column refers to the dependent file or the dependent member.
6 Unique constraint or primary key constraint is a parent key in a referential constraint where the other file is a dependent file.
7 The other file is a parent file.
8 If the dependent file is empty, only *EXCLRD is required on the parent.