INSERT (SQL)

INSERT OR UPDATE

The INSERT OR UPDATE statement is a variant of the INSERT statement that performs both insert and update operations. First it attempts to perform an insert operation. If the insert request fails due to a UNIQUE KEY violation (for the field(s) of some unique key, there exists a row that already has the same value(s) as the row specified for the insert), then it automatically turns into an update request for that row, and INSERT OR UPDATE uses the specified field values to update the existing row.

INSERT OR UPDATE does not support Fast Insert.

Copy link to this sectionValue Assignment Syntax

When inserting a record, you can assign values to specified columns in a variety of ways. All non-specified columns must either accept NULL by default or have a defined default value.

• Explicit column names using the SET keyword, specify one or more column = scalar-expression pairs as a comma-separated list. For example:

  SET StatusDate='05/12/06',Status='Purged'

  Copy code to clipboard

• Explicit column names using the VALUES keyword, specify a list of columns equated to a corresponding scalar-expressions list. For example:

  (StatusDate,Status) VALUES ('05/12/06','Purged')

  Copy code to clipboard

  When assigning scalar-expression values to a column list, there must be a scalar-expression for each specified column.

• No column names. When using the VALUES keyword without a column list, specify a list of scalar-expressions that correspond to the table’s fields in order. For example:

  VALUES ('Fred Wang',65342,'22 Main St. Anytown MA','123-45-6789')

  Copy code to clipboard

  Values must be specified in column number order. You must specify a value for every base table column that takes a user-supplied value; an INSERT using column order cannot take defined field default values. An SQLCODE -62 error is issued if you specify fewer values than the number of table columns. An SQLCODE -116 error is issued if you specify more values than the number of table columns.

  The RowID column cannot be user specified, and is therefore not included in this syntax.

  By default, a table with a defined IDENTITY field or RowVersion field cannot be populated using this syntax. If one of these fields is defined, this INSERT syntax issues an SQLCODE -62 error if you do not specify a value for these fields, and issues an SQLCODE -138 Cannot INSERT/UPDATE a value for a read only field error if you do specify a value for these fields. (An IDENTITY field can be configured to allow user-supplied values; see IDENTITY field.)

  A table with a defined SERIAL (%Counter) field or %AutoIncrement field can be populated using this syntax, but a user-supplied value must be specified for these counter fields.

• No column names. When using the VALUES keyword without a column list, specify a dynamic local array of scalar-expressions that implicitly correspond to the columns of the row in column order. For example:

  VALUES :myarray()

  Copy code to clipboard

  This value assignment can only be performed from Embedded SQL using a host variable. Unlike all other value assignments, this usage allows you to delay specifying which columns are to be inserted until runtime (by populating the array at runtime). All other types of insert require that you specify which columns are to be inserted when the INSERT is prepared. This syntax cannot be used with a linked table; attempting to do so results in an SQLCODE -155 error.

  Values must be specified in column number order. You must specify a value for every base table column that takes a user-supplied value; an INSERT using column order cannot take defined field default values. Supplied array values must begin with array(2). Column 1 is the RowID field; you cannot specify a value for the RowID field. For further details, see “Host Variable as a Subscripted Array” in the “Using Embedded SQL” chapter of Using InterSystems SQL.

If you specify column names and corresponding data values, you can omit columns for which there is a defined default value or which accept NULL. An INSERT can insert a default value for most field data types, including stream fields.

If you do not specify column names, data values must correspond positionally to the defined column list. You must specify a value for every user-specifiable base table column; defined default values cannot be used. (You can, of course, specify an empty string as a column value.)

To list all of the column names and column numbers defined for a specified table, refer to Column Names and Numbers in the “Defining Tables” chapter of Using InterSystems SQL.

For required column privileges, refer to Privileges. For error codes, refer to SQLCODE Errors.

Copy link to this sectionDisplay to Logical Data Conversion

Data is stored in LOGICAL mode format. For example, a date is stored as an integer count of days, a time is stored as a count of seconds from midnight, a %List is stored as an encoded string. Most other data, such as strings and numbers, require no conversion; they are both input and stored in the same format regardless of the current mode.

For data that is converted, you can input data in LOGICAL mode (the default), or input data in a more human-readable format (DISPLAY mode or ODBC mode) by specifying the select mode. For example, by specifying the select mode you can input a date in DISPLAY format (for example, 2/22/2018), ODBC format (for example, 2018-02-22), or LOGICAL format (for example, 64701). For certain data types you can also specify data in LOGICAL format when in ODBC or DISPLAY select mode. You can explicitly set the select mode in SQL execution environments as follows:

• In an ObjectScript program or from the Terminal interface: invoke the SetOption() method, as follows SET status=$SYSTEM.SQL.Util.SetOption("SelectMode",n,.oldval), where the integer n is 0=Logical (the default), 1=ODBC, 2=Display.

• In Dynamic SQL, specify the %SelectMode=n property, where the integer n is 0=Logical (the default), 1=ODBC, 2=Display.

• From the SQL Shell, specify SET SELECTMODE keyword, where keyword = logical, odbc, or display.

• From the Management Portal select System Explorer, SQL, then use the Display Mode drop-down list to specify Logical Mode, ODBC Mode, or Display Mode.

Input data that is not in LOGICAL mode format must be converted to LOGICAL mode format for storage. Compiled SQL supports automatic conversion of input values from DISPLAY or ODBC format to LOGICAL format. An input value that cannot be converted results in an SQLCODE error, for example SQLCODE -146 and SQLCODE -147. Automatic conversion of input data requires two factors: when compiled, the SQL must specify RUNTIME mode; when executed, the SQL must execute in a LOGICAL mode environment.

• In Embedded SQL, if you specify #SQLCompile Select=runtime, InterSystems IRIS will compile the SQL statement with code that converts input values from a display format to LOGICAL mode storage format. InterSystems IRIS performs this mode conversion both for single values and for arrays of values. For further details, see #SQLCompile Select in the “ObjectScript Macros and the Macro Preprocessor” chapter of Using ObjectScript.

• In an SQL CREATE FUNCTION, CREATE METHOD, or CREATE PROCEDURE statement, if you specify SELECTMODE RUNTIME, InterSystems IRIS will compile the SQL statement with code that converts input values from a display format to LOGICAL mode storage format.

The data is stored in LOGICAL format if the SQL execution environment is in LOGICAL mode. This is the default mode for all InterSystems SQL execution environments.

Copy link to this section%SerialObject Properties

When inserting data into a %SerialObject, you must insert into the table (persistent class) that references the embedded %SerialObject; you cannot insert into a %SerialObject directly. From the referencing table, you can either:

• Use the referencing field to insert values for multiple %SerialObject properties as a %List structure. For example, if the persistent class has a property PAddress that references a serial object contain the properties Street, City, and Country (in that order), you insert SET PAddress=$LISTBUILD('123 Main St.','Newtown','USA') or (PAddress) VALUES ($LISTBUILD('123 Main St.','Newtown','USA')) or (PAddress) VALUES (:vallist). The %List must contain values for the properties of the serial object (or placeholder commas) in the order that these properties are specified in the serial object.

  This type of insert may not perform validation of %SerialObject property values. Therefore, it is strongly suggested that you use the $SYSTEM.SQL.Schema.ValidateTable() method to perform Table Data Validation after inserting %SerialObject property values using a %List structure.

• Use underscore syntax to insert values for individual %SerialObject properties in any order. For example, if the persistent class has a property PAddress that references a serial object contain the properties Street, City, and Country, you insert SET PAddress_City='Newtown',PAddress_Street='123 Main St.',PAddress_Country='USA'. Unspecified serial object properties default to NULL.

  This type of insert performs validation of %SerialObject property values.

Copy link to this sectionNon-Display Characters

You can insert non-display characters using the CHAR function and the concatenation operator. For example, the following example inserts a string consisting of the letter “A”, a line feed, and the letter “B”:

INSERT INTO MyTable (Text) VALUES ('A'||CHAR(10)||'B')

Copy code to clipboard

Note that to concatenate the results of a function you must use the || concatenation operator, not the _ concatenation operator.

A query can determine if a non-display character is present using the LENGTH or $LENGTH function.

Copy link to this sectionSpecial Variables

You can insert into a column the value of the following special variables:

A %TABLENAME, or %CLASSNAME pseudo-field variable keyword. %TABLENAME returns the current table name. %CLASSNAME returns the name of the class corresponding to the current table.

One or more of the following ObjectScript special variables (or their abbreviations): $HOROLOG, $JOB, $NAMESPACE, $TLEVEL, $USERNAME, $ZHOROLOG, $ZJOB, $ZNSPACE, $ZPI, $ZTIMESTAMP, $ZTIMEZONE, $ZVERSION.

Copy link to this sectionStream Data

You can insert the following types of data values into a stream field:

• For any table: A string literal or a host variable containing a string literal, for example:

      set literal="Technique 1"
  
      //do the insert; use a string
      &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:literal))
  

  Copy code to clipboard

• For a non-sharded table: An object reference (OREF) to a stream object. InterSystems IRIS opens this object and copies its contents into the new stream field. For example:

      set oref=##class(%Stream.GlobalCharacter).%New()
      do oref.Write("Technique non-shard 1")
  
      //do the insert; use an actual OREF
      &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:oref))
  

  Copy code to clipboard

  or a string version of an OREF of a stream, for example:

      set oref=##class(%Stream.GlobalCharacter).%New()
      do oref.Write("Technique non-shard 2")
  
      //next line converts OREF to a string OREF
      set string=oref_""
  
      //do the insert
      &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:string))
  

  Copy code to clipboard

• For a sharded table: An object ID (OID) using a temporary stream object stored in the ^IRIS.Stream.Shard global:

      SET clob=##class(%Stream.GlobalCharacter).%New("Shard")
      DO clob.Write("Technique Sharded Table 1")
      SET sc=clob.%Save() // Handle $$$ISERR(sc)
        set ClobOid=clob.%Oid()
      //do the insert
      &sql(INSERT INTO MyStreamTable (MyStreamField) VALUES (:ClobOid))
  

  Copy code to clipboard

Attempting to insert an improperly defined stream value results in an SQLCODE -412 error: General Stream Error.

For further details, refer to Inserting Data into Stream Data Fields.

Copy link to this sectionList Structured Data

InterSystems IRIS supports the list structure data type %List (data type class %Library.List). This is a compressed binary format, which does not map to a corresponding native data type for InterSystems SQL. It corresponds to data type VARBINARY with a default MAXLEN of 32749. For this reason, Dynamic SQL cannot use INSERT or UPDATE to set a property value of type %List. For further details, refer to the Data Types reference page in this manual.

Copy link to this sectionInsert Counter Values

A table can optionally have one field defined as IDENTITY. By default, this field receives an integer from an automatically incremented table counter whenever a row is inserted into the table. By default, an insert cannot specify a value for this field. However, this default is configurable. An IDENTITY field value cannot be modified by an update operation. This counter is reset by a TRUNCATE TABLE operation.

A table can optionally have one field defined as data type ROWVERSION. If this field is defined, an insert operation automatically inserts an integer from the namespace-wide RowVersion counter into this field. An update operation automatically updates this integer with the current namespace-wide RowVersion counter value. No user-specified, calculated, or default value can be inserted for a ROWVERSION field. This counter cannot be reset.

A table can optionally have one or more fields defined as data type SERIAL (%Library.Counter). By default, this field receives an integer from an automatically incremented table counter whenever a row is inserted into the table. However, a user can specify an integer value for this field during an insert, overriding the table counter default. A SERIAL (%Counter) field value cannot be modified by an update operation. This counter is reset by a TRUNCATE TABLE operation.

Copy link to this sectionInsert Computed Values

A field defined with COMPUTECODE may insert a value as part of the INSERT operation, unless the field is CALCULATED. If you supply a value for a COMPUTED field or if this field has a default value, INSERT stores this explicit value. Otherwise, the field value is computed, as follows:

• COMPUTECODE: value is computed and stored upon INSERT, value is not changed upon UPDATE.

• COMPUTECODE with COMPUTEONCHANGE: value is computed and stored upon INSERT, is recomputed and stored upon UPDATE.

• COMPUTECODE with DEFAULT and COMPUTEONCHANGE: default value is stored upon INSERT, value is computed and stored upon UPDATE.

• COMPUTECODE with CALCULATED or TRANSIENT: you cannot INSERT a value for this field because no value is stored. The value is computed when queried. However, InterSystems IRIS does perform validation on this field as part of the insert operation:

  • If you attempt to insert a value into a calculated field, InterSystems IRIS performs validation on the supplied value and issues an error if the value is invalid. If the value is valid, InterSystems IRIS proceeds with the row insert: it does not insert the value in this field, issues no SQLCODE error, and increments ROWCOUNT.

  • If a field of this type is part of a foreign key constraint, a value for this field is computed during the insert in order to perform the referential integrity check; this computed value is not stored.

If the compute code contains a programming error (for example, divide by zero), the INSERT operation fails with an SQLCODE -415 error.

For further details, refer to Computing a field value on INSERT or UPDATE.

Copy link to this sectionDEFAULT VALUES Clause

You can insert a row into a table that has all of its field values set to default values. Fields that have a defined default value are set to that value. Fields without a defined default value are set to NULL. This is done using the following command:

INSERT INTO Mytable DEFAULT VALUES

Copy code to clipboard

Fields defined with the NOT NULL constraint and no defined DEFAULT fail this operation with an SQLCODE -108.

Fields defined with the UNIQUE constraint can be inserted using this statement. If a field is defined with a UNIQUE constraint and no DEFAULT value, repeated invocations insert multiple rows with this UNIQUE field set to NULL. If a field is defined with a UNIQUE constraint and a DEFAULT value, this statement can only be used once. A second invocation fails with an SQLCODE -119.

DEFAULT VALUES inserts a row with a system-generated integer values for counter fields. These include the RowID, and the optional IDENTITY field, SERIAL (%Counter) field, and ROWVERSION field.

Copy link to this sectionCopying Data into a Duplicate Table

You can use INSERT with SELECT * to copy the data from a table to a duplicate table, as long as the column order matches and the data types are compatible. The column names do not have to match.

INSERT INTO Sample.DupTable SELECT * FROM Sample.SrcTable

Copy code to clipboard

• Data type compatible with data values: You can, for example, insert integer data from an INTEGER field into a VARCHAR field. The INSERT would fail with an SQLCODE -104 if any data value was incompatible with the destination data type. Refer to Data Type Precedence. You can use the CONVERT function to convert inserted data to the destination data type.

• Data type length compatible with data values: The defined column data lengths do not have to match each other, they just have to match the actual data. For example, SrcTable can have a column FullName VARCHAR(60) and DupTable can have a corresponding PersonName VARCHAR(40). The INSERT will succeed as long as no existing FullName value is longer than 40 characters. The INSERT would fail with an SQLCODE -104 if any FullName value was longer than 40 characters.

• Compatible column order: The two tables must have the same column order. Otherwise an SQLCODE -64 error is generated. The DDL CREATE TABLE operation lists the columns in the order defined. A persistent class that defines a table lists the columns in alphabetical order.

• Compatible column count: The destination table can have additional columns beyond the ones copied. For example, SrcTable can have the columns FullName VARCHAR(60),Age INTEGER and DupTable can have PersonName VARCHAR(60),Years INTEGER,ShoeSize INTEGER. However, note that a persistent class that defines a table lists the columns in alphabetical order.

• Private RowID: When a table is defined, the RowID field is defined as either public or private (hidden). The DDL CREATE TABLE operation defines the RowID as private by default. A persistent class that defines a table defines the RowID as public by default; to make it private you must specify the SqlRowIdPrivate class keyword when defining the persistent class.

  The easiest way to copy a table is to for both the Source and Destination tables to be defined with a private RowID. However, a foreign key can only refer to a table with a public RowID. Table copy operations behave as follows:

  • If Source private and Destination private: you can use INSERT SELECT with SELECT * to copy data to a duplicate table.

  • If Source public and Destination public: you cannot use INSERT SELECT to copy data to a duplicate table. An SQLCODE -111 error is generated.

  • If Source private and Destination public: you cannot use INSERT SELECT to copy data to a duplicate table. An SQLCODE -111 error is generated.

  • If Source is public and Destination private: you cannot use INSERT SELECT with SELECT * to copy data to a duplicate table. An SQLCODE -64 error is generated because of the presence of the RowID in one select list makes the select lists incompatible. You can use an INSERT SELECT with a list of all field names (not including the RowID) to copy data to a duplicate table. However, if the Source has a foreign key public RowID, the foreign key relationship will not be preserved for the destination table. The Destination will have new system-generated RowIDs.

    If Source has a foreign key public RowID, and you want Destination to have the same foreign key relationship, you must define Destination with the ALLOWIDENTITYINSERT=1 parameter. When defining Destination as a persistent class, specify Parameter ALLOWIDENTITYINSERT=1;. When using CREATE TABLE to define Destination, specify %CLASSPARAMETER ALLOWIDENTITYINSERT=1. If a table is defined as ALLOWIDENTITYINSERT=1, this setting cannot be changed by the SetOption("IdentityInsert") method.

• Whether or not one of persistent classes that define these tables is Final has no effect on copying data into a duplicate table.

This operation can be used to copy existing data into a redefined table that will accept future column data values that would not have been valid in the original table.

Copy link to this sectionPrivileges

To insert one or more rows of data into a table, you must have either table-level privileges or column-level privileges for that table.

Copy link to this sectionFast Insert

When inserting rows in a table using JDBC, InterSystems IRIS by default automatically performs highly efficient Fast Insert operations. Fast Insert moves the normalization and formatting of the data being inserted from the server over to the client. The server can then directly set the whole row of data for a table into the global without manipulation on the server. This offloads these tasks from the server onto the client and can dramatically improve INSERT performance. Because the client is assuming the task of formatting the data, there may be an unforeseen usage increase in your client environment. You can use the FeatureOption property to disable Fast Insert if this is an issue.

Fast Insert must be supported on both the server and the client. To enabled or disabled Fast Insert in the client, use the FeatureOption property in the definition of the class instance as follows:

Properties p = new Properties();
p.setProperty("FeatureOption","3");   / 2 is fast Insert, 1 is fast Select, 3 is both

Copy code to clipboard

If Fast Insert is active, an INSERT executed using a cached query is performed using Fast Insert. This initial INSERT that generated the cached query is not performed using Fast Insert. This enables you to compare the performance of the initial insert with subsequent Fast Inserts executed using the cached query. If Fast Insert is not supported (for any of the following reasons), an ordinary INSERT is performed.

Fast Insert must be performed on a table. It cannot be performed on an updateable view. Fast Insert is not performed when the table has any of the following characteristics:

• The table uses embedded (nested) storage structure (%SerialObject).

• The table is a linked table.

• The table is a child table.

• The table has an explicitly defined multi-field IDKEY index.

• The table has a SERIAL (%Counter), AUTO_INCREMENT, or %RowVersion field.

• The table has a property (field) with a defined VALUELIST parameter.

• The table has a defined insert trigger.

• The table performs LogicalToStorage conversion of field values.

• The table is a Shard Master table.

Fast Insert cannot be performed if the INSERT statement has any of the following characteristics:

• It specifies a stream field ( (data type %Stream.GlobalCharacter or %Stream.GlobalBinary), a collection field (lists or arrays), or a ReadOnly field. These types of fields can exist in the table, but cannot be specified in the INSERT.

• It specifies a literal value enclosed with double parentheses that suppresses literal substitution. For example, (('A')).

• It specifies a {ts } timestamp value that omits the date value.

• It includes a DEFAULT VALUES clause.

For SQL xDBC Statement auditing events, an INSERT statement that uses the Fast Insert interface has a description of SQL fastINSERT Statement. If the Fast Insert interface is used, the Audit event does not include any parameter data, but includes the message Parameter values are not available for a fastInsert statement.

Copy link to this sectionReferential Integrity

If you do not specify the %NOCHECK keyword, InterSystems IRIS uses the system-wide configuration setting to determine whether to perform foreign key referential integrity checking; the default is to perform foreign key referential integrity checking. You can set this default system-wide, as described in Foreign Key Referential Integrity Checking. To determine the current system-wide setting, call $SYSTEM.SQL.CurrentSettings().

This setting does not apply to foreign keys that have been defined with the NOCHECK keyword.

During an INSERT operation, for every foreign key reference a shared lock is acquired on the corresponding row in the referenced table. This row is locked while performing referential integrity checking and inserting the row. The lock is then released (it is not held until the end of the transaction). This ensures that the referenced row is not changed between the referential integrity check and the completion of the insert operation.

However, if you specify the %NOLOCK keyword, no locking is performed either on the specified table or on the corresponding foreign key row in the referenced table.

Copy link to this sectionChild Table Insert

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.

Copy link to this sectionAtomicity

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() method, as follows SET status=$SYSTEM.SQL.Util.SetOption("AutoCommit",intval,.oldval). The following intval integer options are available:

• 1 or IMPLICIT (autocommit on) — The default behavior, as described above. Each INSERT constitutes a separate transaction.

• 2 or EXPLICIT (autocommit off) — If no transaction is in progress, an 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 of the specified 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.

You can determine the atomicity setting for the current process using the GetOption("AutoCommit") method, as shown in the following 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" }

Copy code to clipboard

Copy link to this sectionTransaction Locking

If you do not specify the %NOLOCK keyword, 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. This means that 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 the two following lock escalation strategies:

• “E”-type lock escalation: InterSystems IRIS uses this type of lock escalation if the following are true: (1) the class 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. “E”-type lock escalation is described in the LOCK command in the ObjectScript Reference.

• Traditional SQL lock escalation: The most likely reason why a class would not use “E”-type lock escalation is the presence of a 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 will attempt 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") method. The default is 1000. This system-wide lock threshold value is configurable:

• Using the $SYSTEM.SQL.Util.SetOption("LockThreshold") method.

• Using 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.

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

On 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 refer to Transaction Processing in the “Modifying the Database” chapter of Using InterSystems SQL.

Copy link to this sectionRow-Level Security

InterSystems IRIS row-level security permits INSERT to add a row even if the row security is defined so that you will not be permitted to subsequently access the row. To ensure that an INSERT does not prevent you from subsequent SELECT access to the row, it is recommended that you perform the INSERT through a view that has a WITH CHECK OPTION. For further details, refer to CREATE VIEW.

Copy link to this sectionMicrosoft Access

To use INSERT to add data to an InterSystems IRIS table using Microsoft Access, either mark the table RowID field as private or define a unique index on one or more additional fields.