DB2 10.5 for Linux, UNIX, and Windows

Table locking during load operations

In most cases, the load utility uses table level locking to restrict access to tables. The level of locking depends on the stage of the load operation and whether it was specified to allow read access.

A load operation in ALLOW NO ACCESS mode uses a super exclusive lock (Z-lock) on the table for the duration of the load.

Before a load operation in ALLOW READ ACCESS mode begins, the load utility waits for all applications that began before the load operation to release their locks on the target table. At the beginning of the load operation, the load utility acquires an update lock (U-lock) on the table. It holds this lock until the data is being committed. When the load utility acquires the U-lock on the table, it waits for all applications that hold locks on the table before the start of the load operation to release them, even if they have compatible locks. This is achieved by temporarily upgrading the U-lock to a Z-lock which does not conflict with new table lock requests on the target table as long as the requested locks are compatible with the load operation's U-lock. When data is being committed, the load utility upgrades the lock to a Z-lock, so there can be some delay in commit time while the load utility waits for applications with conflicting locks to finish.

Note: The load operation can time out while it waits for the applications to release their locks on the table before loading. However, the load operation does not time out while waiting for the Z-lock needed to commit the data.

Applications with conflicting locks
Use the LOCK WITH FORCE option of the LOAD command to force off applications holding conflicting locks on a target table so that the load operation can proceed. Before a load operation in ALLOW READ ACCESS mode can proceed, applications holding the following locks are forced off:

Table locks that conflict with a table update lock (for example, import or insert).
All table locks that exist at the commit phase of the load operation.

Applications holding conflicting locks on the system catalog tables are not forced off by the load utility. If an application is forced off the system by the load utility, the application loses its database connection, and an error is returned (SQL1224N).

When you specify the COPY NO option for a load operation on a recoverable database, all objects in the target table space are locked in share mode before the table space is placed in the Backup Pending state. This occurs regardless of the access mode. If you specify the LOCK WITH FORCE option, all applications holding locks on objects in the table space that conflict with a share lock are forced off.