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.

Table 1. Database functions and locks
Function Command File lock Member/Data lock Access path lock
Add Member ADDPFM, ADDLFM *EXCLRD   *EXCLRD
Change File Attributes CHGPF, CHGLF *EXCL *EXCLRD *EXCLRD
Change Member Attributes CHGPFM, CHGLFM *SHRRD *EXCLRD  
Change Object Owner CHGOBJOWN *EXCL    
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 *EXCL    
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
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 Backup and Recovery

Link to PDF 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.

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).

Table 2. Database constraint locks
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 *EXCL *EXCL
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 *EXCL *EXCL
DLTF3 PAR *EXCL *EXCL *EXCL *EXCL
RMVPFCST7 *REFCST *EXCL *EXCL *EXCL4 *EXCL
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.

 

Parent topic:

Locking shared data