T4.4: Implements Concurrency Controls

InterSystems IRIS provides sophisticated locking mechanisms through the LOCK command to control concurrent access to data resources. The fundamental concept is that a lock is a named resource (typically matching a global variable name) that processes must acquire before modifying the corresponding data. Locks follow the same naming conventions as variables and can be local (process-specific) or global (cross-process). When you lock a specific node like ^student(1,2), you have an explicit lock only on that node, but other processes are implicitly barred from locking parent nodes (^student(1)) or child nodes (^student(1,2,3)). This hierarchical lock behavior prevents conflicts across the subscript tree.

The system supports three primary lock types: exclusive (default), shared (#"S"), and escalating (#"E"). Exclusive locks limit a resource to one process, while shared locks allow multiple processes to hold non-conflicting locks simultaneously. An existing shared lock prevents other processes from applying an exclusive lock, and vice versa. However, a process can upgrade its own shared lock to exclusive. Escalating locks provide automatic lock escalation when the number of locks at a subscript level reaches the threshold (default 1000), automatically locking the parent node to prevent lock table overflow.

Lock operations use three indicators: no character (unlock all prior locks then lock), + (incremental lock without unlocking), and - (unlock/decrement). The timeout parameter (specified as :seconds) is critical for preventing process hangs; without a timeout, lock requests wait indefinitely. When successful, $TEST is set to 1; on timeout, $TEST becomes 0. The lock table maintains system-wide information about all locks including exclusive/shared counts, escalating status, delock state, and waiting processes. Locks are automatically released when a process terminates, and within transactions, unlocks may be deferred until commit/rollback.

Best practices for lock usage center on preventing deadlocks, minimizing contention, and maintaining system responsiveness. The most critical practice is always specifying timeout values when acquiring locks. Without timeouts, a LOCK command waits indefinitely if another process holds a conflicting lock, potentially hanging the process. A timeout of 0 makes one attempt and immediately returns, while positive values wait the specified number of seconds. After issuing a lock with timeout, always check $TEST to verify success before proceeding with critical operations.

Deadlock prevention requires understanding the dangerous pattern: requesting an exclusive lock when holding a shared lock. If Process A holds a shared lock on ^data(1) and Process B also holds a shared lock on ^data(1), and both then request exclusive locks with LOCK +^data(1), neither can proceed - each waits for the other to release its shared lock. The solution is to use LOCK ^data(1) (without the plus sign) which first releases the shared lock before requesting the exclusive lock. Consistent lock ordering across all processes also prevents deadlocks: if all processes lock resources in the same sequence (e.g., always lock ^indexA before ^indexB), circular wait conditions cannot occur.

Performance best practices include using subscripted lock names whenever possible (^student(1) rather than ^student) and minimizing lock duration by acquiring locks as late as possible and releasing them as soon as safe. Lock granularity matters: locking at the appropriate level balances concurrency (fine-grained locks allow more parallel operations) against overhead (too many locks consume lock table space). Use escalating locks (#"E") when applying many locks at the same subscript level to prevent lock table overflow. Within transactions, understand that unlocks of non-incremented locks are deferred until transaction commit unless you specify immediate unlock (#"I"). Regular monitoring of the lock table through Management Portal (System Operation > Locks > View Locks) or the ^LOCKTAB utility helps identify contention bottlenecks and deadlock situations before they impact production systems.

InterSystems IRIS automatically manages the choice between row-level and table-level locks during SQL operations, with the ability to configure the escalation threshold based on application requirements. Row-level locking is the default for SQL DML operations (INSERT, UPDATE, DELETE) within transactions. Each row modification acquires a lock on the unique data value, allowing high concurrency as different processes can simultaneously modify different rows in the same table. These locks are held for the duration of the transaction to prevent conflicts and ensure data integrity. For example, if a process deletes a unique data value, this value remains locked until the transaction completes, preventing another process from inserting a record with the same value (which could cause a duplicate after rollback).

Lock escalation occurs automatically when the number of row-level locks for a single table exceeds the lock threshold. The default threshold is 1000 locks per table. When the 1001st lock is requested, the system escalates to a table-level lock, releasing all individual row locks and acquiring a single lock on the parent node. This table lock persists for the duration of the transaction, and subsequent row operations increment the table lock count rather than creating new row locks. Lock escalation prevents lock table overflow but reduces concurrency since the entire table is locked rather than individual rows. The lock threshold is configurable using $SYSTEM.SQL.Util.SetOption("LockThreshold",value) which changes both the current system-wide value and the configuration file setting. You can query the current threshold with $SYSTEM.SQL.Util.GetOption("LockThreshold").

Choosing the appropriate lock granularity depends on transaction patterns. Applications with short transactions modifying few rows benefit from row-level locks maximizing concurrency. Batch operations modifying large numbers of rows might benefit from a lower lock threshold or even explicit table-level locks to reduce lock management overhead. However, lowering the threshold too much increases the chance of lock escalation, which blocks other processes from accessing any rows in the table. In ObjectScript, you can use escalating locks explicitly with the #"E" lock type: LOCK +^data(1,1)#"E",+^data(1,2)#"E",... When the threshold is reached, these automatically escalate to ^data(1). Understanding the escalation mechanism allows developers to design applications that balance concurrency needs against lock table size constraints. The %NOLOCK restriction argument in SQL statements bypasses automatic locking when you have external lock management or read-only operations.