T2.1: Ensures Data Integrity

Overview

Transactions in ObjectScript guarantee atomicity: either all changes within the transaction are applied, or none are. The TSTART command marks the beginning of a transaction and increments the $TLEVEL special variable. TCOMMIT marks the end of a successful transaction and decrements $TLEVEL. The actual commit to the database only happens when $TLEVEL reaches 0, meaning the outermost transaction has completed.

Basic Transaction Pattern

 TSTART
 TRY {
     SET ^Data("account", 1001, "balance") = ^Data("account", 1001, "balance") - 500
     SET ^Data("account", 1002, "balance") = ^Data("account", 1002, "balance") + 500
     TCOMMIT
 }
 CATCH ex {
     TROLLBACK
     WRITE "Error: ", ex.DisplayString(), !
 }

Nested Transactions

InterSystems IRIS supports nested transactions up to 255 levels. Each TSTART increments $TLEVEL, and each TCOMMIT decrements it. The key concept is that inner TCOMMIT calls do not actually commit data -- commitment is deferred until $TLEVEL reaches 0.

 WRITE "$TLEVEL = ", $TLEVEL, !   // 0
 TSTART
 WRITE "$TLEVEL = ", $TLEVEL, !   // 1
 SET ^Data("outer") = "value1"
 TSTART
 WRITE "$TLEVEL = ", $TLEVEL, !   // 2
 SET ^Data("inner") = "value2"
 TCOMMIT                           // $TLEVEL back to 1 (not yet committed)
 TCOMMIT                           // $TLEVEL back to 0 (all changes committed)

This design allows modular code where called methods can issue their own TSTART/TCOMMIT without knowing whether the caller already started a transaction.

Full Rollback vs Partial Rollback

TROLLBACK without an argument performs a complete rollback of all transaction levels, resetting $TLEVEL to 0 and undoing all global changes made since the first TSTART. TROLLBACK 1 performs a partial rollback, undoing only the changes made at the current $TLEVEL and decrementing $TLEVEL by 1.

 TSTART
 SET ^Data("A") = 100
 TSTART
 SET ^Data("B") = 200
 TROLLBACK 1                  // Only ^Data("B") = 200 is rolled back; $TLEVEL decremented to 1
 WRITE "$TLEVEL = ", $TLEVEL, !  // 1
 TCOMMIT                      // ^Data("A") = 100 is committed

Complete Rollback Pattern

 TSTART
 SET ^Data("A") = 100
 TSTART
 SET ^Data("B") = 200
 TROLLBACK                    // ALL changes rolled back; $TLEVEL reset to 0
 WRITE "$TLEVEL = ", $TLEVEL, !  // 0

Critical: What TROLLBACK Does NOT Undo

Several operations are not affected by TROLLBACK:

• Local variables: Changes to local variables persist after rollback
• $INCREMENT / $SEQUENCE: These atomic operations are not transactional
• LOCK operations: Locks acquired within a transaction are released at the end of the transaction, but TROLLBACK does not "undo" lock operations
• Process-private globals (^||PPG): Not journaled, therefore not rolled back

 SET localVar = "before"
 TSTART
 SET localVar = "during"
 SET ^Data("test") = "transactional"
 TROLLBACK
 WRITE localVar, !          // "during" -- local variable NOT rolled back
 WRITE $GET(^Data("test"))  // "" -- global change WAS rolled back

Incremental Lock Pattern

The standard pattern in modern ObjectScript uses incremental locks (+/-) to manage concurrency without accidentally releasing other locks held by the process.

 // Acquire lock with 5-second timeout
 LOCK +^Data("account", acctId):5
 IF '$TEST {
     WRITE "Could not acquire lock", !
     QUIT
 }
 TRY {
     // Critical section: read-modify-write
     SET balance = ^Data("account", acctId, "balance")
     SET ^Data("account", acctId, "balance") = balance + amount
     TCOMMIT
 }
 CATCH ex {
     TROLLBACK
 }
 LOCK -^Data("account", acctId)     // Always release the lock

Shared vs Exclusive Locks

Any lock without the #"S" flag is an exclusive lock (also called a write lock). There is no explicit "exclusive" flag — the absence of #"S" makes a lock exclusive by default. Shared locks allow multiple processes to read concurrently, while exclusive locks provide sole access:

• An existing shared lock prevents other processes from acquiring an exclusive lock on that resource
• An existing exclusive lock prevents other processes from acquiring any lock (shared or exclusive) on that resource
• Multiple processes can hold shared locks on the same resource simultaneously

 // Shared lock for reading (multiple readers allowed)
 LOCK +^Data("report")#"S":5
 IF $TEST {
     // Read operations
     LOCK -^Data("report")#"S"
 }

 // Exclusive lock for writing (blocks all other locks)
 LOCK +^Data("report"):5
 IF $TEST {
     // Write operations
     LOCK -^Data("report")
 }

Lock Type Flags

The LOCK command supports type flags specified after # in quotes. Available flags:

Flag	Meaning	Lock	Unlock	Description
"S"	Shared	Yes	Yes	Multiple readers allowed
"E"	Escalating	Yes	Yes	Participates in lock escalation
"I"	Immediate	No	Yes	Releases lock immediately, even within a transaction
"D"	Deferred	No	Yes	Defers unlock until transaction ends (default behavior within transactions)

Flags can be combined: #"SE" (shared escalating), #"SI" (shared immediate unlock), #"EI" (escalating immediate unlock).

Immediate vs Deferred Unlock Within Transactions

By default, locks acquired within a transaction are held until TCOMMIT or TROLLBACK, even if explicitly released with LOCK -. This is deferred unlock behavior (#"D", the default within transactions).

Immediate unlock (#"I") overrides this behavior and releases the lock right away, even inside a transaction:

 TSTART
 LOCK +^Data("temp"):5
 // ... work with ^Data("temp") ...
 LOCK -^Data("temp")#"I"          // Released NOW, not at TCOMMIT
 // ... other work continues in the transaction ...
 TCOMMIT

Use immediate unlock when you need to release a lock within a transaction because the protected resource is no longer needed and you want to minimize lock contention. Use the default deferred behavior when the lock must be held for the full duration of the transaction to ensure data integrity.

Lock Table

The lock table is a system-wide in-memory structure that tracks all current locks. It can be viewed through the Management Portal (System Operation > Locks) or programmatically. The lock table has a configurable size (locksiz parameter). When the lock table is full, new lock requests fail with a error.

How Lock Escalation Works

When a process acquires row-level locks through SQL operations (INSERT, UPDATE, DELETE), InterSystems IRIS tracks the number of locks per table. Once the number of row locks for a single table exceeds the lock escalation threshold (default 1000), the system automatically escalates to a table-level lock. This replaces all individual row locks with a single lock on the entire table.

 // Check current lock escalation threshold
 WRITE $SYSTEM.SQL.Util.GetOption("LockThreshold"), !   // Default: 1000

 // Change the threshold
 DO $SYSTEM.SQL.Util.SetOption("LockThreshold", 2000)

Impact on Concurrency

• Before escalation: Other processes can access rows that are not individually locked. Fine-grained concurrency is maintained.
• After escalation: The entire table is locked. Other processes attempting to lock any row in the same table will block (or fail on timeout). This can cause significant concurrency bottlenecks.

When Escalation Occurs

Lock escalation typically happens during:

• Large batch INSERT operations
• Bulk UPDATE affecting many rows
• DELETE operations on large result sets
• Long-running transactions that accumulate row locks

Best Practices

• Keep transactions short to minimize the number of accumulated row locks
• For intentional bulk operations, consider explicitly acquiring a table-level lock first (to avoid gradual escalation) and using %NOLOCK on concurrent read queries, or increasing the threshold temporarily
• Monitor the lock table in Management Portal to detect escalation events
• The threshold is a system-wide setting; changing it affects all processes

Pessimistic Concurrency

In pessimistic concurrency, a process acquires an exclusive lock on a resource before reading or modifying it. This prevents other processes from accessing the resource until the lock is released. It is suitable for high-contention scenarios where conflicts are frequent.

 // Pessimistic approach: lock first, then read and modify
 LOCK +^Data("customer", custId):5
 IF '$TEST {
     WRITE "Resource busy", !
     QUIT
 }
 SET data = ^Data("customer", custId)
 // ... modify data ...
 SET ^Data("customer", custId) = data
 LOCK -^Data("customer", custId)

Optimistic Concurrency

In optimistic concurrency, a process reads data without acquiring locks. Before committing changes, it verifies that no other process has modified the data in the meantime. If a conflict is detected, the operation is retried or reported as a failure. This works well in low-contention environments.

 // Optimistic approach using %VersionId in persistent classes
 SET obj = ##class(MyApp.Customer).%OpenId(custId)
 // ... modify obj properties ...
 SET sc = obj.%Save()
 IF $$$ISERR(sc) {
     // Check if it's a concurrency conflict
     // %VersionId mismatch means another process modified the object
     WRITE "Concurrency conflict - retry needed", !
 }

Object Concurrency Modes (%ConcurrencyMode)

InterSystems IRIS persistent objects support several concurrency modes:

• 0 (None): No locking; fastest but no protection
• 1 (Atomic): Lock during %Save only; protects writes but reads may be stale
• 2 (SharedAtomicRetain): Shared lock on read, exclusive on write; balanced approach
• 3 (SharedRetain): Shared lock on open retained until close
• 4 (PessimisticRetain): Exclusive lock on open retained until close; maximum protection

 // Open with pessimistic locking
 SET obj = ##class(MyApp.Customer).%OpenId(custId, 4, .sc)  // concurrency = 4

SQL Isolation Levels

• READ UNCOMMITTED: No locks; may read uncommitted data (dirty reads)
• READ COMMITTED (default): Reads only committed data; uses brief shared locks
• READ VERIFIED: Re-reads data at end of operation to verify consistency

Basic Embedded SQL Transaction Pattern

 &sql(START TRANSACTION)
 &sql(UPDATE Account SET Balance = Balance - :amount WHERE AccountId = :fromAcct)
 IF SQLCODE < 0 {
     &sql(ROLLBACK)
     WRITE "Error on debit: ", %msg, !
     QUIT
 }
 &sql(UPDATE Account SET Balance = Balance + :amount WHERE AccountId = :toAcct)
 IF SQLCODE < 0 {
     &sql(ROLLBACK)
     WRITE "Error on credit: ", %msg, !
     QUIT
 }
 &sql(COMMIT)

Mixing ObjectScript and SQL Transactions

TSTART and &sql(START TRANSACTION) work with the same underlying transaction mechanism. They share $TLEVEL:

 TSTART
 SET ^AuditLog($INCREMENT(^AuditLog)) = $HOROLOG _ "|Transfer"
 &sql(UPDATE Account SET Balance = Balance - :amount WHERE AccountId = :fromAcct)
 IF SQLCODE < 0 {
     TROLLBACK
     QUIT
 }
 TCOMMIT

Using %NOLOCK for Read-Only Operations

The %NOLOCK hint tells SQL not to acquire shared locks during SELECT operations, improving performance for read-only queries where strict consistency is not required:

 &sql(SELECT Name, Balance INTO :name, :bal
      FROM Account %NOLOCK
      WHERE AccountId = :acctId)

This is particularly useful for reporting queries that should not block concurrent write operations and do not need to participate in transaction isolation.

Checking SQL Permissions Within Transactions

SQL operations within transactions respect the same privilege model. Combine transaction management with error handling for privilege failures:

 &sql(START TRANSACTION)
 &sql(INSERT INTO SensitiveData (PatientId, Diagnosis) VALUES (:pid, :diag))
 IF SQLCODE = -99 {
     &sql(ROLLBACK)
     WRITE "Insufficient SQL privileges", !
     QUIT
 }
 &sql(COMMIT)