The `dbms_lock` package provides functions to assist in resource locking and timing. From release 27.0 onward the package carries named intra-session locks for use with the parallel PSQL constructs of [[Parallel Statement (pSqlParallelStatement)|Parallel Statement]]: a lock coordinates branches and loop iterations of one session and never spans sessions, processes or machines. Lock names are compared without regard to case. A lock is anchored to the worker frame which acquired it: a parallel branch, a parallel loop iteration, or the top-level PSQL block for statements outside a parallel construct. Within one frame a lock may cross block boundaries freely, so a nested block may acquire and the enclosing block may release, or the other way around. A lock can never cross the join of a parallel construct or the end of the top-level block: a frame completing normally while still holding a lock raises `itgenpsp013` and the lock is released, a frame completing on an exception has its locks force-released with an annotation on the propagating error, and at the end of the session every named lock ceases to exist. Locks are re-entrant. A frame already holding a lock may request it again; the hold count increments and only the release which brings the count back to zero lets the next waiter in. An unbalanced release - never acquired, already fully released, or held by another frame - raises `itgenpsp012` at the faulting call. A cycle among the lock frames of the session raises `deadlock_detected` in one of the participants instead of hanging, and the wait-for graph includes the implicit join edges of suspended parallel constructs. ## `held_locks`: The locks held by the caller. Enumerate the locks the executing PSQL worker frame currently holds, as a table function used in a select through table(...). Each row holds the columns NAME, HOLD_COUNT and ACQUIRED_UTC. The SystemPSqlLocks data dictionary view adds session-wide lifetime statistics per lock: FIRST_ACQUIRED_UTC and LAST_ACQUIRED_UTC (a release does not clear them) and FIRST_WAITER_UTC and LAST_WAITER_UTC. The view keeps at most the 10,000 most recently released locks, so a session generating many unique lock names stays fast; locks currently held or waited on are always shown. The function answers only for the calling frame; session-wide lock state is diagnostic by nature and acting on another worker's state read there is a race by definition. ## `is_acquired`: Whether the caller holds a named lock. Return true when the executing PSQL worker frame currently holds the named lock. The function deliberately answers only for the caller, for the same reason `held_locks` does. Parameters: - `name` (string, required): Name of the lock. ## `release`: Release a named lock. Release one hold on the named lock. Releasing a lock the frame does not hold raises `itgenpsp012`: an unbalanced release is a structural bug and is reported at the faulting call rather than at some later hang. Parameters: - `name` (string, required): Name of the lock. ## `request`: Request a named lock. Request the named intra-session lock for the executing PSQL worker frame, blocking until acquired. Without a timeout the wait is bounded only by the statement timeout; with a timeout the catchable exception `lock_timeout` (`itgenpsp010`) is raised on expiry. A deadlock among the session's frames raises the catchable exception `deadlock_detected` (`itgenpsp011`) instead of hanging. Parameters: - `name` (string, required): Name of the lock. - `timeout_sec` (decimal, optional): Timeout in seconds with sub-second resolution via fractions, for instance `0.25` for 250 milliseconds. Defaults to waiting indefinitely within the statement timeout. ## `sleep`: Sleep some time Postpone execution for the number of seconds indicated. The method accepts a single parameter indicating the number of seconds as a decimal with sub-second resolution. Parameters: - `seconds` (decimal, required): Number of seconds to sleep.