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

Begins a transaction.
Synopsis
START TRANSACTION [%COMMITMODE commitmode]

START TRANSACTION [transactionmodes]
Arguments
commitmode Optional — Specifies how future transactions will be committed to the database during the current process. Valid values are EXPLICIT, IMPLICIT, and NONE. The default is to maintain the existing commit mode; the initial commit mode default for a process 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 START TRANSACTION statement initiates a transaction. START TRANSACTION immediately initiates a transaction, regardless of the current commit mode setting. A transaction begun with START TRANSACTION must be concluded by issuing an explicit COMMIT or ROLLBACK, regardless of the current commit mode setting.
START TRANSACTION is optional.
When START TRANSACTION initiates a transaction it increments the $TLEVEL transaction level counter from 0 to 1, indicating a transaction is in progress. You can also determine if a transaction is in progress by checking the SQLCODE set by the %INTRANSACTION statement. Issuing a START TRANSACTION when a transaction is in progress has no effect on $TLEVEL or %INTRANSACTION.
Caché SQL does not support nested transactions. Issuing a START TRANSACTION when a transaction is already in progress does not initiate another transaction and does not return an error code. Caché SQL does support savepoints, allowing a partial rollback of a transaction.
If a transaction is not in progress when you issue a SAVEPOINT statement, SAVEPOINT initiates a transaction. However, this means of initiating a transaction is not recommended.
An SQLCODE -400 is issued if a transaction operation fails to complete successfully.
%BEGTRANS (deprecated)
The %BEGTRANS statement is functionally identical to START TRANSACTION with no arguments. %BEGTRANS cannot take arguments, and is considered deprecated. Use START TRANSACTION for all new SQL program code.
Setting Parameters
Optionally, START TRANSACTION can be used to set parameters. The parameter settings you specify take effect immediately. However, any transaction initiated with a START TRANSACTION must be concluded with an explicit COMMIT or ROLLBACK, regardless of how you set the commitmode parameter. Parameter settings 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 START TRANSACTION statement can be used to set either the commitmode parameter or the transactionmodes parameters, but not both. To set both, you may issue a SET TRANSACTION and a START TRANSACTION, or two START TRANSACTION statements. Only the first START TRANSACTION initiates a transaction.
After issuing a START TRANSACTION, you can change these parameter settings during the transaction by issuing another START TRANSACTION, a SET TRANSACTION, or a method call. Changing the commitmode parameter does not remove the requirement to conclude the current transaction with an explicit COMMIT or ROLLBACK.
You can use the SET TRANSACTION statement to set the commitmode or transactionmodes parameters without starting a transaction. These parameters can also be set using method calls, either outside of a transaction or within a transaction.
%COMMITMODE
The %COMMITMODE keyword allows you to specify automatic transaction initiation and commitment behavior for the current process. A START TRANSACTION %COMMITMODE changes the commit mode setting for all future transactions on the current process. It does not affect the transaction initiated by the START TRANSACTION statement. Regardless of the current or set commit mode, a START TRANSACTION immediately initiates a transaction, and this transaction must be concluded by issuing an explicit COMMIT or ROLLBACK.
The available %COMMITMODE options are:
You can set the %COMMITMODE 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)
 
ObjectScript and SQL Transactions
ObjectScript and SQL transaction commands are fully compatible and interchangeable, with the following exception:
ObjectScript TSTART and SQL START TRANSACTION both start a transaction if no transaction is current. However, START TRANSACTION does not support nested transactions. Therefore, if you need (or may need) nested transactions, it is preferable to start the transaction with TSTART. If you need compatibility with the SQL standard, use START TRANSACTION.
ObjectScript transaction processing provides limited support for nested transactions. SQL transaction processing supplies support for savepoints within transactions.
If a transaction involves SQL data modification statements, the transaction should be started with the SQL START TRANSACTION statement and committed with the SQL COMMIT statement. (These statements may be explicit or implicit, depending on the %COMMITMODE setting.) Methods that use TSTART/TCOMMIT nesting can be included in the transaction, as long as they don't initiate the transaction. Methods and stored procedures should not normally use SQL transaction control statements, unless, by design, they are the main controller of the transaction. Stored procedures should not normally use SQL transaction control statements, because these stored procedures are normally called from ODBC/JDBC, which has its own model of transaction control.
Examples
The following Embedded SQL example uses two START TRANSACTION statements to start a transaction and set its parameters. Note that the first START TRANSACTION initiates a transaction, setting the commit mode and incrementing the $TLEVEL transaction level counter. The second START TRANSACTION sets the isolation mode for query read operations in the current transaction, but does not increment $TLEVEL, because the transaction has already been started. The SAVEPOINT statement increments$TLEVEL:
    WRITE !,"Transaction level=",$TLEVEL
  &sql(START TRANSACTION %COMMITMODE EXPLICIT)
    WRITE !,"Start transaction commit mode, SQLCODE=",SQLCODE
    WRITE !,"Transaction level=",$TLEVEL
  &sql(START TRANSACTION ISOLATION LEVEL READ COMMITTED)
    WRITE !,"Start transaction isolation mode, 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