“This is the sixth day of my participation in the November Gwen Challenge.The final text challenge in 2021”.

Chapter 67 SQL command ROLLBACK

Rollback transactions.

The outline

ROLLBACK [WORK]

ROLLBACK TO SAVEPOINT pointname
Copy the code

parameter

  • pointname– The name of the existing savepoint specified as an identifier.

describe

The ROLLBACK statement rolls back the transaction, undoes work that has been executed but not committed, reduces the $TLEVEL transaction level counter, and releases the lock. ROLLBACK Is used to restore the database to a previous consistent state.

  • ROLLBACKRollback all work completed during the current transaction, will$TLEVELThe transaction level counter is reset to0And release all locks.

This will restore the database to the state before the transaction began. ROLLBACK and ROLLBACK WORK are equivalent statements; Both versions support compatibility.

  • ROLLBACK TO SAVEPOINT pointnameRolls back all work that has been done since the specified savepoint, decreasing by the number of outstanding savepoints$TLEVELTransaction level counters.

When all savepoints are rolled back or committed, and the transaction level counter is reset to zero, the transaction is complete. If the specified savepoint does not exist or has been rolled back, ROLLBACK will issue a SQLCODE -375 error and ROLLBACK the entire current transaction.

Rollback to SAVEPOINT must specify a roll call. Failure to do so will result in a SQLCODE -301 error.

If the transaction operation does not complete successfully, a SQLCODE -400 error is issued.

Don’t roll back

The ROLLBACK operation does not affect the following items:

  • Rollback does not reduce the default classIDKeyCounter.

IDKey is automatically generated by $INCREMENT(or $SEQUENCE) and maintains a count independent of SQL transactions.

  • A rollback does not reverse the creation, modification, or clearing of cached queries.

These operations are not considered part of the transaction.

  • Occurs in a transactionDDLActions or tuning table actions can create and run temporary routines.

This temporary routine is handled like a cached query. That is, the creation, compilation, and deletion of temporary routines are not considered part of the transaction. The execution of temporary routines is considered part of a transaction.

Roll back log

The Messages indicating that the rollback occurred and the errors encountered during the rollback operation are recorded in the messages.log file in the MGR directory. You can view messages. Log in the MANAGEMENT Portal System Operation, System Log, and Message Log options.

Transaction pending

TransactionsSuspending() methods of the % System.process class can be used to suspend and restore system-wide support for all current transactions. Suspending transactions suspends logging of changes. Therefore, if a transaction is suspended during the current transaction, ROLLBACK cannot ROLLBACK any changes made during the transaction suspension; However, rollback rolls back any changes made during the current transaction, either before or after the transaction suspension took effect.

ObjectScript transaction command

ObjectScript and SQL TRANSACTION commands are fully compatible and interchangeable, except in the following cases:

If there is no current TRANSACTION, both ObjectScript TSTART and SQL START TRANSACTION START the TRANSACTION. However, START TRANSACTION does not support nested transactions. Therefore, if you need (or may need) nested transactions, it is best to start a transaction using TSTART. To be compatible with the SQL standard, use START TRANSACTION.

ObjectScript transaction processing provides limited support for nested transactions. SQL transaction processing provides support for savepoints within transactions.

ClassMethod ROLLBACK(a)
{ &sql(SET TRANSACTION %COMMITMODE EXPLICIT) w ! ."Set transaction 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 a, SQLCODE=",SQLCODE w ! ."Transaction level=",$TLEVEL &sql(SAVEPOINT b) w ! ."Set Savepoint b, SQLCODE=",SQLCODE w ! ."Transaction level=",$TLEVEL &sql(SAVEPOINT c) w ! ."Set Savepoint c, SQLCODE=",SQLCODE w ! ."Transaction level=",$TLEVEL &sql(ROLLBACK) w ! ."Rollback transaction, SQLCODE=",SQLCODE w ! ."Transaction level=",$TLEVEL
}
Copy the code
DHC-APP>d ##class(PHA.TEST.SQLCommand).ROLLBACK(a)Set transaction mode.SQLCODE=0
Transaction level=0
Start transaction, SQLCODE=0
Transaction level=1
Set Savepoint a, SQLCODE=0
Transaction level=2
Set Savepoint b, SQLCODE=0
Transaction level=3
Set Savepoint c, SQLCODE=0
Transaction level=4
Rollback transaction, SQLCODE=0
Transaction level=0
Copy the code

The following embedded SQL example demonstrates how rolling back to savepoint names restores the transaction level ($TLEVEL) to the level immediately before the specified savepoint:

ClassMethod ROLLBACK1() { &sql(SET TRANSACTION %COMMITMODE EXPLICIT) w ! ."Set transaction 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 a, SQLCODE=",SQLCODE w ! ."Transaction level at a=",$TLEVEL &sql(SAVEPOINT b) w ! ."Set Savepoint b, SQLCODE=",SQLCODE w ! ."Transaction level at b=",$TLEVEL &sql(ROLLBACK TO SAVEPOINT b) w ! ."Rollback to b, SQLCODE=",SQLCODE w ! ."Rollback transaction level=",$TLEVEL &sql(SAVEPOINT c) w ! ."Set Savepoint c, SQLCODE=",SQLCODE w ! ."Transaction level at c=",$TLEVEL &sql(SAVEPOINT d) w ! ."Set Savepoint d, SQLCODE=",SQLCODE w ! ."Transaction level at d=",$TLEVEL &sql(COMMIT) w ! ."Commit transaction, SQLCODE=",SQLCODE w ! ."Transaction level=",$TLEVEL
}
Copy the code
DHC-APP>d ##class(PHA.TEST.SQLCommand).ROLLBACK1(a)Set transaction mode.SQLCODE=0
Transaction level=0
Start transaction, SQLCODE=0
Transaction level=1
Set Savepoint a, SQLCODE=0
Transaction level at a=2
Set Savepoint b, SQLCODE=0
Transaction level at b=3
Rollback to b, SQLCODE=0
Rollback transaction level=2
Set Savepoint c, SQLCODE=0
Transaction level at c=3
Set Savepoint d, SQLCODE=0
Transaction level at d=4
Commit transaction, SQLCODE=0
Transaction level=0
Copy the code