SET TRANSACTION (SQL)

%COMMITMODE

The %COMMITMODE keyword allows you to specify whether automatic transaction commitment is performed. The available options are:

• IMPLICIT: automatic transaction commitment is on (the default). SQL automatically initiates a transaction when a program issues a database modification operation (INSERT, UPDATE, or DELETE). The transaction continues until either the operation completes successfully and SQL automatically commits the changes, or the operation is unable to complete successfully on all rows and SQL automatically rolls back the entire operation. Each database operation (INSERT, UPDATE, or DELETE) constitutes a separate transaction. Successful completion of the database operation automatically clears the rollback journal, releases locks, and decrements $TLEVEL. No COMMIT statement is needed. This is the default setting.

• EXPLICIT: automatic transaction commitment is off. SQL automatically initiates a transaction when a program issues the first database modification operation (INSERT, UPDATE, or DELETE). This transaction continues until it is explicitly concluded. Upon successful completion you issue a COMMIT statement. If a database modification operation fails you issue a ROLLBACK statement to revert the database to the point prior to the beginning of the transaction. In EXPLICIT mode the number of database operations per transaction is user-defined.

• NONE: no automatic transaction processing. A transaction is not initiated unless explicitly invoked by a START TRANSACTION statement. The transaction must be explicitly concluded by issuing either a COMMIT or ROLLBACK statement. Thus whether a database operation is included in a transaction, and the number of database operations in a transaction are both user-defined.

TRUNCATE TABLE does not occur within an automatically initiated transaction. If journaling and rollback of TRUNCATE TABLE is required, you must explicitly specify a START TRANSACTION and conclude with an explicit COMMIT or ROLLBACK.

You can determine the %COMMITMODE setting for the current process using the GetOption("AutoCommit")Opens in a new tab 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 "%COMMITMODE IMPLICIT (default behavior):",!,
          "each database operation is a separate transaction",!,
          "with automatic commit or rollback" }
  ELSEIF x=0 {
    WRITE "%COMMITMODE NONE:",!,
          "No automatic transaction support",!,
          "You must use START TRANSACTION to start a transaction",!,
          "and COMMIT or ROLLBACK to conclude one" }
  ELSE { 
    WRITE "%COMMITMODE EXPLICIT:",!,
          "the first database operation automatically",!,
          "starts a transaction; to end the transaction",!,
          "explicit COMMIT or ROLLBACK required" }

The %COMMITMODE can be set in ObjectScript using the SetOption()Opens in a new tab method, as follows SET status=$SYSTEM.SQL.Util.SetOption("AutoCommit",intval,.oldval). The available method values are 0 (NONE), 1 (IMPLICIT), and 2 (EXPLICIT).

ISOLATION LEVEL

You specify an ISOLATION LEVEL for a process that is issuing a query. The ISOLATION LEVEL options permit you to specify whether changes that are in progress should be available for read access by the query. If another concurrent process is performing inserts or updates to a table and those changes to the table are in a transaction, those changes are in progress, and could, potentially, be rolled back. By setting the ISOLATION LEVEL for your process that is querying that table, you can specify whether you wish to include or exclude these changes in progress from the query results.

• READ UNCOMMITTED states that all changes are immediately available for query access. This includes changes that may subsequently be rolled back. READ UNCOMMITTED ensures that your query will return results without waiting for a concurrent insert or update process, and will not fail due to a lock timeout error. However, the results of a READ UNCOMMITTED may include values that are not committed; these values may be internally inconsistent because the insert or update operation has only partially completed, and these values may be subsequently rolled back. READ UNCOMMITTED is the default if your query process is not in an explicit transaction, or if the transaction does not specify an ISOLATION LEVEL. READ UNCOMMITTED is incompatible with READ WRITE access; attempting to specify both in the same statement results in an SQLCODE -92 error.

• READ VERIFIED states that uncommitted data from other transactions is immediately available, and no locking is performed. This includes changes that may subsequently be rolled back. However, unlike READ UNCOMMITTED, a READ VERIFIED transaction will re-check any conditions that could be invalidated by uncommitted or newly committed data which would result in output that does not satisfy the query conditions. Because of this condition re-check, READ VERIFIED is more accurate but less efficient than READ UNCOMMITTED and should only be used when concurrent updates to the data being checked by the conditions is likely to occur. READ VERIFIED is incompatible with READ WRITE access; attempting to specify both in the same statement results in an SQLCODE -92 error.

• READ COMMITTED states that only those changes that have been committed are available for query access. This ensures that a query is performed on the database in a consistent state, not while a group of changes are being made, a group of changes which may be subsequently rolled back. If requested data has been changed, but the changes have not been committed (or rolled back), the query waits for transaction completion. If a lock timeout occurs while waiting for this data to be available, an SQLCODE -114 error is issued.

READ UNCOMMITTED or READ VERIFIED?

The difference between READ UNCOMMITTED and READ VERIFIED is demonstrated by the following example:

SELECT Name,SSN FROM Sample.Person WHERE Name >= 'M' 

The query optimizer may choose first to collect all RowID's containing Names meeting the >= 'M' condition from a Name index. Once collected, the Person table is accessed one RowID at a time to retrieve the Name and SSN fields for output. A concurrently running updating transaction could change the Name field of a Person with RowID 72 from 'Smith' to 'Abel' in-between the query's collection of RowID's from the index and its row-by-row access to the table. In this case, the collection of RowID's from the index would contain the RowID for a row that no longer conforms to the Name >= 'M' condition.

READ UNCOMMITTED query processing assumes that the Name >= 'M' condition has been satisfied by the index, and will output whatever Name is present in the table for each RowID it collected from the index. In this example it would therefore output a row with a Name of 'Abel', which does not satisfy the condition.

READ VERIFIED query processing notes that it is retrieving a field from a table for output (Name) that participates in a condition which should have been previously satisfied by the index, and re-checks the condition in case the field value has changed since the index was examined. Upon re-check, it notes that the row no longer satisfies the condition and omits it from the output. Only values that are needed for output have their conditions re-checked: SELECT SSN FROM Person WHERE Name >= 'M' would output the row with RowID 72 in this example.

ISOLATION LEVEL in Effect

You can set the ISOLATION LEVEL for a process using SET TRANSACTION (without starting a transaction), START TRANSACTION (setting isolation mode and starting a transaction), or a SetOption("IsolationMode")Opens in a new tab method call.

The specified ISOLATION LEVEL remains in effect until explicitly reset by a SET TRANSACTION, START TRANSACTION, or a SetOption("IsolationMode")Opens in a new tab method call. Because COMMIT or ROLLBACK is only meaningful for changes to the data, not data queries, a COMMIT or ROLLBACK operation has no effect on the ISOLATION LEVEL setting.

The ISOLATION LEVEL in effect at the start of a query remains in effect for the duration of the query.

You can determine the ISOLATION LEVEL for the current process using the GetOption("IsolationMode")Opens in a new tab method call. You can also set the isolation mode for the current process using the SetOption("IsolationMode")Opens in a new tab method call. These methods specify READ UNCOMMITTED (the default) as 0, READ COMMITTED as 1, and READ VERIFIED as 3. Specifying any other numeric value leaves the isolation mode unchanged. No error or change occurs if you set the isolation mode to the current isolation mode. Use of these methods is shown in the following example:

   WRITE $SYSTEM.SQL.Util.GetOption("IsolationMode")," default",!
   &sql(START TRANSACTION ISOLATION LEVEL READ COMMITTED,READ WRITE)
   WRITE $SYSTEM.SQL.Util.GetOption("IsolationMode")," after START TRANSACTION",!
   DO $SYSTEM.SQL.Util.SetOption("IsolationMode",0,.stat)
   IF stat=1 {
     WRITE $SYSTEM.SQL.Util.GetOption("IsolationMode")," after IsolationMode=0 call",! }
   ELSE { WRITE "Set IsolationMode error" }
   &sql(COMMIT)

The isolation mode and the access mode must always be compatible. Changing the access mode changes the isolation mode, as shown in the following example:

   WRITE $SYSTEM.SQL.Util.GetOption("IsolationMode")," default",!
   &sql(SET TRANSACTION ISOLATION LEVEL READ COMMITTED,READ WRITE)
   WRITE $SYSTEM.SQL.Util.GetOption("IsolationMode")," after SET TRANSACTION",!
   &sql(START TRANSACTION READ ONLY)
   WRITE $SYSTEM.SQL.Util.GetOption("IsolationMode")," after changing access mode",!
   &sql(COMMIT)

%COMMITMODE commitmode

An optional argument specifying the manner in which transactions are committed to the database. Available values are EXPLICIT, IMPLICIT, and NONE. The default is IMPLICIT.

transactionmodes

An optional argument that specifies the isolation mode and access mode for the transaction. You can specify a value for either an isolation mode, an access mode, or for both modes as a comma-separated list.

Valid values for isolation mode are ISOLATION LEVEL READ COMMITTED, ISOLATION LEVEL READ UNCOMMITTED, and ISOLATION LEVEL READ VERIFIED. The default is ISOLATION LEVEL READ UNCOMMITTED.

Valid values for access mode are READ ONLY and READ WRITE. Note that only ISOLATION LEVEL READ COMMITTED is compatible with access mode READ WRITE.