Caché SQL Reference
SET TRANSACTION
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Sets parameters for transactions.
Synopsis
SET TRANSACTION %COMMITMODE commitmode

SET TRANSACTION transactionmodes
Arguments
%COMMITMODE commitmode Optional — Specifies the manner in which transactions are committed to the database. Available values are EXPLICIT, IMPLICIT, and NONE. The default is IMPLICIT.
transactionmodes
Optional — 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.
Description
A SET TRANSACTION statement sets parameters that govern SQL transactions for the current process. These parameters take effect at the beginning of the next transaction and continue in effect for the duration of the current process or until explicitly reset. They do not automatically reset to defaults at the end of a transaction.
A single SET TRANSACTION statement can be used to set either the commitmode parameter or the transactionmodes parameters, but not both.
The same parameters can be set using the START TRANSACTION command, which can both set parameters and begin a new transaction. The parameters can also be set using method calls.
SET TRANSACTION does not begin a transaction, and therefore does not increment the $TLEVEL transaction level counter.
%COMMITMODE
The %COMMITMODE keyword allows you to specify whether or not automatic transaction commitment is performed. The available options are:
You can determine the %COMMITMODE setting for the current process using the GetAutoCommit() method, as shown in the following ObjectScript example:
  DO $SYSTEM.SQL.SetAutoCommit($RANDOM(3))
  SET x=$SYSTEM.SQL.GetAutoCommit()
  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 SetAutoCommit() method call. 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 or not 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 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.
Exceptions to READ COMMITTED
When ISOLATION LEVEL read committed is in effect, either through setting ISOLATION LEVEL READ COMMITTED or $SYSTEM.SQL.SetIsolationMode(1), SQL can retrieve only those changes to the data that have been committed. However, there are significant exceptions to this rule:
Note:
On Caché implementations with ECP (Enterprise Cache Protocol) use of READ COMMITTED may result in significantly slower performance when compared to READ UNCOMMITTED. Developers should weigh the superior performance of READ UNCOMMITTED against the greater data accuracy of READ COMMITTED when defining transactions that involve ECP.
For further details, refer to Transaction Processing in the “Modifying the Database” chapter of Using Caché SQL.
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 SetIsolationMode() method call.
The specified ISOLATION LEVEL remains in effect until explicitly reset by a SET TRANSACTION, START TRANSACTION, or a SetIsolationMode() 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 GetIsolationMode() method call. You can also set the isolation mode for the current process using the SetIsolationMode() 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.GetIsolationMode()," default",!
   &sql(START TRANSACTION ISOLATION LEVEL READ COMMITTED,READ WRITE)
   WRITE $SYSTEM.SQL.GetIsolationMode()," after START TRANSACTION",!
   DO $SYSTEM.SQL.SetIsolationMode(0,.stat)
   IF stat=1 {
     WRITE $SYSTEM.SQL.GetIsolationMode()," after SetIsolationMode(0) call",! }
   ELSE { WRITE "SetIsolationMode() 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.GetIsolationMode()," default",!
   &sql(SET TRANSACTION ISOLATION LEVEL READ COMMITTED,READ WRITE)
   WRITE $SYSTEM.SQL.GetIsolationMode()," after SET TRANSACTION",!
   &sql(START TRANSACTION READ ONLY)
   WRITE $SYSTEM.SQL.GetIsolationMode()," after changing access mode",!
   &sql(COMMIT)
 
Examples
The following Embedded SQL example uses two SET TRANSACTION statements to set transaction parameters. Note that SET TRANSACTION does not increment the transaction level ($TLEVEL). The START TRANSACTION command initiates a transaction and increments $TLEVEL:
  &sql(SET TRANSACTION %COMMITMODE EXPLICIT)
    WRITE !,"Set transaction commit mode, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED)
    WRITE !,"Set transaction isolation mode, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(START TRANSACTION)
    WRITE !,"Start transaction, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(SAVEPOINT a)
    WRITE !,"Set Savepoint a, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(COMMIT)
    WRITE !,"Commit transaction, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
 
See Also