“This is the 13th day of my participation in the November Gwen Challenge. See details: The Last Gwen Challenge 2021”.

Chapter 74 SQL SET TRANSACTION

Sets the transaction parameters.

The outline

SET TRANSACTION [%COMMITMODE commitmode]

SET TRANSACTION [transactionmodes]
Copy the code

parameter

  • %COMMITMODE commitmode– Optional – Specifies how transactions are committed to the database.

The value can be EXPLICIT, IMPLICIT, or NONE. The default is IMPLICIT.

  • transactionmodes– Optional – Specifies the isolation mode and access mode for the transaction.

Isolation mode, access mode, or the values of both modes can be specified as comma-separated lists. The valid values for ISOLATION mode are ISOLATION LEVEL READ COMMITTED, ISOLATION LEVEL READ UNCOMMITTED, and ISOLATION LEVEL READ VERIFIED. The default value 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 READ WRITE mode.

describe

The SET TRANSACTION statement sets the parameters that control the SQL TRANSACTION for the current process. These parameters take effect at the start of the next transaction and during the duration of the current process or until an explicit reset. They are not automatically reset to default values at the end of a transaction.

A single SET TRANSACTION statement can be used to SET commit mode parameters or TRANSACTION mode parameters, but not both.

The same parameters can be set using the START TRANSACTION command, which can either set parameters or START a new TRANSACTION. You can also set parameters using method calls.

SET TRANSACTION does not start a TRANSACTION and therefore does not increase the $TLEVEL TRANSACTION level counter.

SET TRANSACTION can be used in both dynamic SQL(including SQL Shell) and embedded SQL.

%COMMITMODE

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

  • IMPLICITImplicit: Automatic transaction commitment is turned on (default).

SQL automatically starts a transaction when a program issues a database modification operation (INSERT, UPDATE, or DELETE). The transaction continues until the operation completes successfully and SQL automatically commits the changes, or the operation fails 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 log, releases the lock, and reduces $TLEVEL. No COMMIT statements are required. This is the default setting.

  • EXPLICIT: Turns off automatic transaction commitments.

SQL automatically starts a transaction when the program issues the first database modification operation (INSERT, UPDATE, or DELETE). The deal will continue until a definitive agreement is reached. After successful completion, issue a COMMIT statement. If the database modification operation fails, a ROLLBACK statement is issued to restore the database to where it was before the transaction began. In EXPLICIT mode, the database operands for each transaction are user-defined.

  • NONE: No automatic transaction processing.

Transactions are not initialized unless explicitly called by the START Transaction statement. You must explicitly end the transaction by issuing a COMMIT or ROLLBACK statement. Therefore, whether or not a transaction contains database operations and the number of database operations in a transaction are user-defined.

TRUNCATE TABLE does not occur in automatically started transactions. To log and rollback TRUNCATE TABLE, you must explicitly specify START TRANSACTION and end with explicit COMMIT or rollback.

You can use the GetOption(” AutoCommit “) method to determine the %COMMITMODE setting for the current process, as shown in the following ObjectScript example:

ClassMethod SetTransaction(a)
{
	s stat = $SYSTEM.SQL.SetOption("AutoCommit",$RANDOM(3),.oldval)
	if stat '= 1 { w "SetOption failed:" d $System.Status.DisplayError(stat) q } s x = $SYSTEM.SQL.GetOption("AutoCommit") if x = 1 { w "%COMMITMODE IMPLICIT (default behavior):",! Each database operation is an independent transaction. } elseIf x = 0 {w "%COMMITMODE NONE:",! "No automatic transaction support ",! START TRANSACTION must be used to START a TRANSACTION. ", "and COMMIT or ROLLBACK to end a"} else {w "%COMMITMODE EXPLICIT:",! "First database operation automatically ",! , "Start a transaction; End the transaction ",! , "Requires an explicit COMMIT or ROLLBACK"}}Copy the code

% COMMITMODE can use in ObjectScript SetOption () method is set up, the following set status = $SYSTEM. SQL. Util. SetOption (” the AutoCommit mode “, intval. Oldval). The available method values are 0 (NONE), 1 (IMPLICIT), and 2 (EXPLICIT).

Isolation level

You can specify an Isolation level for the process that issues the query. The Isolation Level option allows you to specify whether the changes being made are available for read access to the query. If another concurrent process is performing an insert or update to the table, and the changes to the table are in a transaction, those changes are in progress and may be rolled back. By setting ISOLATION LEVEL for the process that is querying the table, you can specify whether you want these ongoing changes to be included or excluded from the query results.

  • READ UNCOMMITTEDIndicates that all changes are immediately available for query access.

This includes changes that can be rolled back later. READ UNCOMMITTED ensures that queries will return results without waiting for concurrent inserts or updates to the process and will not fail due to lock timeout errors. However, the results of READ UNCOMMITTED can include UNCOMMITTED values; These values may be internally inconsistent because the insert or update operation is only partially complete and these values may then be rolled back. READ UNCOMMITTED is the default if the query process is not in an explicit transaction, or if the isolation level is not specified for the transaction. READ UNCOMMITTED is incompatible with read-write access. Attempting to specify both variables in the same statement results in a SQLCODE -92 error.

  • READ VERIFIEDDeclares that uncommitted data from other transactions is immediately available and that no lock operation is performed.

This includes changes that can be rolled back later. However, unlike READ UNCOMMITTED, the READ VERIFIED transaction rechecks any conditions that may be invalidated by UNCOMMITTED or newly committed data, which results in output that does not satisfy the query conditions. Because of this conditional rechecking, READ VERIFIED is more accurate than READ UNCOMMITTED, but less efficient, and should only be used when concurrent updates to the conditionally checked data are likely to occur. READ VERIFIED is incompatible with read-write access. Attempting to specify both variables in the same statement results in a SQLCODE -92 error.

  • READ COMMITTEDIndicates that only those changes that have already been committed are available for query access.

This ensures that queries are executed in a consistent state on the database, rather than when a set of changes is made, which may then be rolled back. If the requested data has been changed, but the change has not been committed (or rolled back), the query waits for the transaction to complete. If a lock timeout occurs while waiting for this data to become available, a SQLCODE -114 error is issued.

READ UNCOMMITTED or READ VERIFIED?

The following example illustrates the difference between READ UNCOMMITTED and READ VERIFIED:

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

The query optimizer may first choose to collect all RowID containing names that meet the >= ‘M’ condition from the Name index. After the collection, access the RowID one at a time to retrieve the Name and SSN fields for output. An update transaction run concurrently can change the Name field of a RowID 72 Person from “Smith” to “Abel” between the query’s RowWID collection and its row-by-row access to the table. In this case, the RowID collection in the index will contain rowiDs for rows that no longer meet the condition Name >= ‘M’.

READ UNCOMMITTED query processing assumes that the Name >= ‘M’ condition has been indexed and prints any names that appear in the table for each RowID collected from the index. Therefore, in this case, it will output a line named ‘Abel’ that does not satisfy the condition.

It notices that it is retrieving a field from the table for output (Name) that participates in a condition that should have been met by the index before, and then rechecks the condition in case the value of the field changes after the index is checked. On rechecking, it notices that the line no longer meets the criteria and removes it from the output. The condition is rechecked only if it outputs the desired value: in this case, SELECT SSN FROM Person WHERE Name >= ‘M’ outputs the line with a RowID of 72.

The READ COMMITTED abnormal

When ISOLATION LEVEL Read Committed takes effect, you can set the ISOLATION LEVEL Read Committed or SetOption() method, The following SET status = $SYSTEM. SQL. Util. SetOption (” IsolationMode “, 1,. Oldval). SQL can only retrieve changes to committed data. However, there are some notable exceptions:

  • A query never returns a deleted row, even though a transaction to delete the row is in progress, and the deletion may then be rolled back.

ISOLATION LEVEL READ COMMITTED ensures that inserts and updates are in a consistent state, not deletes.

  • If the query contains an aggregation function, the aggregation result returns the current state of the data, regardless of the isolation level specified.

Therefore, the aggregation results contain in-progress inserts and updates (which may be rolled back later). Ongoing deletions, which may be rolled back later, are not included in the aggregate results. This is because the aggregation operation requires access to many rows of data in the table.

  • containsDISTINCTClause orGROUP BYclauseSELECTQueries are not affected by isolation level Settings.

A query containing one of these clauses will return the current state of the data, including ongoing changes that may then be rolled back. This is because these query operations require access to many rows of data in the table.

  • with%NOLOCKKeyword query.

Note: On IRIS implementations using ECP(Enterprise Caching Protocol), using READ COMMITTED can result in a significant performance degradation compared to READ UNCOMMITTED. When defining transactions that include ECP, developers should weigh the superior performance of READ UNCOMMITTED against the higher data accuracy of READ COMMITTED.

Effective isolation level

Isolation levels can be set for processes using set TRANSACTION(not starting the TRANSACTION), START TRANSACTION(setting IsolationMode and starting the TRANSACTION), or SetOption(” IsolationMode “) method calls.

The isolation level specified remains in effect until it is explicitly reset by the SET TRANSACTION, START TRANSACTION, or SetOption(” IsolationMode “) method call. Because COMMIT or ROLLBACK is meaningful only for data changes and not for data queries, COMMIT or ROLLBACK operations have no impact on ISOLATION LEVEL Settings.

The Isolation level in effect at the start of the query remains in effect for the duration of the query.

The isolation level of the current process can be determined using the GetOption(” IsolationMode “) method call. You can also use the SetOption(” IsolationMode “) method call to set the IsolationMode for the current process. These methods specify READ UNCOMMITTED(the default) as 0,READ COMMITTED as 1, and READ VERIFIED as 3. Specifying any other value will leave the isolation mode unchanged. If isolation mode is set to the current isolation mode, no errors or changes occur. The following example shows the use of these methods:

ClassMethod SetTransaction1(a)
{
	w $SYSTEM.SQL.GetOption("IsolationMode"),"Default",!
	&sql(
		START TRANSACTION ISOLATION LEVEL READ COMMITTED,READ WRITE
	)
	w $SYSTEM.SQL.GetOption("IsolationMode"),"After the TART TRANSACTION",!
	d $SYSTEM.SQL.SetOption("IsolationMode".0,.stat)
	if stat=1 {
		w $SYSTEM.SQL.GetOption("IsolationMode")," after IsolationMode=0 call",! 
	} else { WRITE "Set IsolationMode error" 
	}
	&sql(COMMIT)
}
Copy the code

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

ClassMethod SetTransaction2(a)
{
	w $SYSTEM.SQL.GetOption("IsolationMode")," default",!
	&sql(
		SET TRANSACTION ISOLATION LEVEL READ COMMITTED,READ WRITE
	)
	w $SYSTEM.SQL.GetOption("IsolationMode")," after SET TRANSACTION",!
	&sql(START TRANSACTION READ ONLY)
	w $SYSTEM.SQL.GetOption("IsolationMode")," after changing access mode",!
	&sql(COMMIT)
}
Copy the code

The sample

The embedded SQL example below uses two SET TRANSACTION statements to SET TRANSACTION parameters. Note that SET TRANSACTION does not increase the TRANSACTION level ($TLEVEL). The START TRANSACTION command starts a TRANSACTION and increases $TLEVEL:

ClassMethod SetTransaction3(a)
{ &sql(SET TRANSACTION %COMMITMODE EXPLICIT) w ! ."Set transaction commit mode, SQLCODE=",SQLCODE w ! ."Transaction level =",$TLEVEL &sql(SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED) w ! ."Set transaction isolation mode, SQLCODE=",SQLCODE w ! ."Transaction level =",$TLEVEL &sql(START TRANSACTION) w ! ."Start transaction, SQLCODE=",SQLCODE w ! ."Transaction level ==",$TLEVEL &sql(SAVEPOINT a) w ! ."Set savepoint, SQLCODE=",SQLCODE w ! ."Transaction level ==",$TLEVEL &sql(COMMIT) w ! ."Commit transaction, SQLCODE=",SQLCODE w ! ."Transaction level ==",$TLEVEL
}

Copy the code
DHC-APP>d ##class(PHA.TEST.SQLCommand).SetTransaction3() Set transaction submission mode,SQLCODE=0Transaction level =0Set transaction isolation mode, SQLCODE=0Transaction level =0Start transaction, SQLCODE=0Transaction level ==1Set the savepoint, SQLCODE=0Transaction level ==2Commit transaction, SQLCODE=0Transaction level ==0
Copy the code