Transaction Considerations

By default, INSERT, UPDATE, DELETE, and TRUNCATE TABLE are atomic operations. An INSERT either completes successfully or the whole operation is rolled back. If any of the specified rows cannot be inserted, none of the specified rows are inserted and the database reverts to its state before issuing the INSERT.

You can modify this default for the current process within SQL by invoking SET TRANSACTION %COMMITMODE. You can modify this default for the current process in ObjectScript by invoking the SetOption()Opens in a new tab method, using this syntax:

SET status=$SYSTEM.SQL.Util.SetOption("AutoCommit",intval,.oldval)

The following intval integer options are available:

• 1 or IMPLICIT (autocommit on — default) — Each INSERT constitutes a separate transaction.

• 2 or EXPLICIT (autocommit off) — If no transaction is in progress, INSERT automatically initiates a transaction, but you must explicitly COMMIT or ROLLBACK to end the transaction. In EXPLICIT mode, the number of database operations per transaction is user-defined.

• 0 or NONE (no auto transaction) — No transaction is initiated when you invoke INSERT. A failed INSERT operation can leave the database in an inconsistent state, with some rows inserted and some not inserted. To provide transaction support in this mode, you must use START TRANSACTION to initiate the transaction and COMMIT or ROLLBACK to end the transaction.

  A sharded table is always in no auto-transaction mode, which means all inserts, updates, and deletes to sharded tables are performed outside the scope of a transaction.

To determine the atomicity setting for the current process, use the GetOption("AutoCommit")Opens in a new tab method, as shown in this ObjectScript example:

  SET stat=$SYSTEM.SQL.Util.SetOption("AutoCommit",$RANDOM(3),.oldval)
  IF stat'=1 {WRITE "SetOption failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET x=$SYSTEM.SQL.Util.GetOption("AutoCommit")
    IF x=1 {
      WRITE "Default atomicity behavior",!
      WRITE "automatic commit or rollback" }
    ELSEIF x=0 {
      WRITE "No transaction initiated, no atomicity:",!
      WRITE "failed DELETE can leave database inconsistent",!
      WRITE "rollback is not supported" }
    ELSE { WRITE "Explicit commit or rollback required" }

If the %NOLOCK keyword is not specified, the system automatically performs standard record locking on INSERT, UPDATE, and DELETE operations. Each affected record (row) is locked for the duration of the current transaction.

The default lock threshold is 1000 locks per table. If you insert more than 1000 records from a table during a transaction, the lock threshold is reached and InterSystems IRIS automatically escalates the locking level from record locks to a table lock. This permits large-scale inserts during a transaction without overflowing the lock table.

InterSystems IRIS applies one of these lock escalation strategies:

• “E”-type escalation locks — InterSystems IRIS uses this lock escalation if the following are true:

  1. The class of the table uses %Storage.Persistent. You can determine this from the Catalog Details in the Management Portal SQL schema display.

  2. The class either does not specify an IDKey index, or specifies a single-property IDKey index.

  For details on “E”-type lock escalation, see LOCK.

• Traditional SQL lock escalation — This lock escalation can take place when the class has multi-property IDKey index. In this case, each %Save increments the lock counter. This means if you do 1001 saves of a single object within a transaction, InterSystems IRIS attempts to escalate the lock.

For both lock escalation strategies, you can determine the current system-wide lock threshold value using the $SYSTEM.SQL.Util.GetOption("LockThreshold")Opens in a new tab method. The default is 1000. You can configure the system-wide lock threshold using one of these options:

• Call the $SYSTEM.SQL.Util.SetOption("LockThreshold")Opens in a new tab method.

• In the Management Portal, select System Administration, Configuration, SQL and Object Settings, SQL. Display and edit the current setting of Lock escalation threshold. The default is 1000 locks. If you change this setting, any new process started after changing it will have the new setting.

To change the lock threshold, you must have USE permission on the %Admin Manage Resource. InterSystems IRIS immediately applies any change made to the lock threshold value to all current processes.

One potential consequence of automatic lock escalation is a deadlock situation that might occur when an attempt to escalate to a table lock conflicts with another process holding a record lock in that table. There are several possible strategies to avoid this:

1. Increase the lock escalation threshold so that lock escalation is unlikely to occur within a transaction.

2. Substantially lower the lock escalation threshold so that lock escalation occurs almost immediately, thus decreasing the opportunity for other processes to lock a record in the same table.

3. Apply a table lock for the duration of the transaction and do not perform record locks. This can be done at the start of the transaction by specifying LOCK TABLE, then UNLOCK TABLE (without the IMMEDIATE keyword, so that the table lock persists until the end of the transaction), then perform inserts with the %NOLOCK option.

Automatic lock escalation is intended to prevent overflow of the lock table. However, if you perform such a large number of inserts that a <LOCKTABLEFULL> error occurs, INSERT issues an SQLCODE -110 error.

For further details on transaction locking, see Transaction Processing.

During an INSERT operation on a child table, a shared lock is acquired on the corresponding row in the parent table. This row is locked while inserting the child table row. The lock is then released (it is not held until the end of the transaction). This ensures that the referenced parent row is not changed during the insert operation.