Explicit Transactions - Teradata Meta Data Services

Teradata Meta Data Services Programmer Guide
Product
Teradata Meta Data Services
Release Number
15.00
ft:locale
en-US
ft:lastEdition
2018-09-28
Product Category
Teradata Tools and Utilities

Explicit Transactions

Explicit transactions are user-controlled MDS transactions that can be used to bracket changes requiring multiple MDS API calls. An MDS user application starts an explicit transaction by calling the CMetaRepository::BeginTransaction API and ends it by explicitly calling either the CMetaRepository::Commit or the CMetaRepository::Rollback API. Until the user expressly closes an explicit MDS transaction, the transaction remains open. Only one explicit transaction can be active at a time.

Explicit transactions may be pseudo nested by calling CMetaRepository::BeginTransaction multiple times before calling CMetaRepository::Commit or CMetaRepository::Rollback.

If this is done, each instance of CMetaRepository::BeginTransaction must be paired with a matching subsequent call to CMetaRepository::Commit in order to finally close the transaction and commit the changes.

Following is an example of an explicit MDS transaction management syntax. It shows a legal call sequence for an MDS explicit transaction.

CMetaRepository::BeginTransaction
 
      CMetaRepository::BeginTransaction
					    CMetaObject::ReadObject
					    CMetaObject::ReadObject
					    CMetaObject::WriteObject
				CMetaRepository::Commit
 
				CMetaRepository::BeginTransaction
					    CMetaObject::WriteObject
					    CMetaObject::ReadObject
				CMetaRepository::Commit
 
      CMetaRepository::Commit

This enables greater flexibility for the application that is using MDS transactions, but it does not provide true nested transactions. The term “pseudo nesting” will be used throughout this document to refer to this kind of nesting.

The entire transaction from the first CMetaRepository::BeginTransaction to the final CMetaRepository::Commit is in reality a single flat transaction. This is because MDS uses standard ANSI transaction management, which does not include nested transactions. Therefore, the syntactically nested subtransactions are not independent transactions of their own, and their calls to CMetaRepository::Commit do not commit work to the database but instead merely close the current pseudo nesting level.

The reason pseudo nesting is allowed is that the added flexibility it provides can simplify the coding of applications that use MDS explicit transactions. For instance, suppose an application has two methods, A() and B(), that use MDS explicit transactions. An example is shown below of how pseudo nesting can simplify MDS explicit transaction coding.

A()
{
	CMetaRepository::BeginTransaction
				Call MDS APIs;
	CMetaRepository::Commit
}
B()
{
	CMetaRepository::BeginTransaction
				Call MDS APIs;
				if (condition) call A();
	CMetaRepository::Commit
}

Because MDS allows pseudo nested transactions, this works correctly. If MDS did not allow this, however, the above example code would break when B() called A(), because A() attempts to open a transaction after B() has already opened one. Without the ability to pseudo nest transactions, in such a situation the application programmer would need to add data members and conditional code to keep track of whether or not A() should attempt to open a transaction depending on whether or not one of its ancestors had already done so.

Closing Explicit Transactions

The user must always specifically close an explicit MDS transaction. After an explicit transaction is closed, either all of the work it performed will have been successfully committed to the database, or none of it will have. (See “Behavior of Transactions Containing DDL Statements” for the only exception – transactions containing multiple DDL statements.)

While ANSI’s definition of commit allows individual SQL statements that succeeded to commit while other SQL statements that failed to roll back, MDS must disallow this statement-level commit behavior to ensure object-level consistency, as a single MDS object may require more than one SQL statement to update it correctly. If MDS allowed statement-level commits, part of an MDS object might be updated while other parts were not, leaving the repository data in an inconsistent state.

There are always two ways the user application can close an explicit transaction: by calling CMetaRepository::Rollback once or by calling CMetaRepository::Commit the appropriate number of times for the current pseudo nesting level (that is, once for each CMetaRepository::BeginTransaction call that has not yet been paired with a CMetaRepository::Commit call). The following subsections describe the different scenarios under which transactions will be committed or rolled back.

Committing a Successful Transaction

An explicit transaction can only be committed if no errors occurred anywhere within the entire transaction and the user never called CMetaRepository::Rollback within the transaction. To commit the transaction, the user application must call CMetaRepository::Commit enough times to close all open pseudo nesting levels. At the time the final CMetaRepository::Commit is issued, the transaction will be committed to the repository if and only if there have been no errors, and MDS will return the success code S_OK. If any errors were detected, the entire explicit transaction will be rolled back, and MDS will return the error code META_E_TX_ROLLED_BACK.

User-Initiated Transaction Rollback

The user application may force the termination and rollback of an open explicit transaction at any time by calling the MDS CMetaRepository::Rollback API once. When this API is called, MDS will abort and roll back the entire explicit transaction, regardless of the current nesting level. The user application must be aware that calling CMetaRepository::Rollback terminates all open nesting levels of the explicit transaction, ending the transaction entirely. This is different from CMetaRepository::Commit, which only closes a single nesting level. MDS therefore treats further MDS API calls as either new implicit transactions if CMetaRepository::BeginTransaction is not called again, or as part of a new explicit transaction if it is.

Transaction Rollbacks Due to Errors

If an error occurs anywhere within an explicit MDS transaction, the entire transaction will be aborted and rolled back, and MDS will return error codes from the current and all subsequent MDS API calls within that transaction.

However, because the user by definition always has control over explicit transactions, the user must explicitly close the transaction before MDS will consider the transaction closed. In this way, both the user and MDS are in agreement as to the boundaries of the explicit transaction. As an optimization, MDS will not perform any additional work in an explicit transaction after it has detected an error, because the entire transaction must be aborted and rolled back. However, until the user application closes the transaction, MDS will continue to treat MDS API calls as part of the currently open (failed) explicit transaction.

If the user application checks the MDS return code and sees that MDS has returned an error code, it can call CMetaRepository::Rollback to close the entire explicit transaction immediately, regardless of the current pseudo nesting level.

However, if the user application does not check the error code returned by MDS, it will not realize the transaction must ultimately be rolled back and could continue issuing MDS API calls that are all part of the original explicit transaction, including pseudo nested CMetaRepository::BeginTransaction and CMetaRepository::Commit calls.

In this case, MDS will simply return error codes for all MDS APIs the user calls, rather than performing the work, until the user closes the explicit transaction, at which point MDS will return the META_E_TX_ROLLED_BACK error code. The following shows an example of a rollback of an explicit transaction due to an error.

CMetaRepository::BeginTransaction
	CMetaRepository::BeginTransaction
			MDS API call 1
			MDS API call 2
	CMetaRepository::Commit
	CMetaRepository::BeginTransaction
			MDS API call 3 – generates an error
			MDS API call 4
	CMetaRepository::Commit
			MDS API call 5
CMetaRepository::Commit

Assume that MDS API call 3 generates an error. At this point, MDS aborts the entire explicit transaction, rolls back the work performed thus far (MDS API calls 1-2 and potentially parts of call 3 if they have been executed), and returns an error code for MDS API call 3.

Remember, because ANSI transactions do not provide true transaction nesting, the call to CMetaRepository::Commit after MDS API call 2 does not commit the work of MDS API calls 1-2 to the database.

The next API call MDS receives is MDS API call 4, indicating that the user application is continuing work within the currently open (ultimately doomed) explicit transaction. Because the fate of this entire explicit transaction is to be rolled back, MDS will not attempt to perform any more work as part of this transaction. Instead, it will immediately return an error code from MDS API call 4. The user application continues processing, calling CMetaRepository::Commit, MDS API call 5, and CMetaRepository::Commit again.

In response to each of these calls, MDS will return an error code instead of performing the work. The final call to CMetaRepository::Commit closes the transaction, and MDS will return the error code META_E_TX_ROLLED_BACK. The end result is that the entire transaction failed, and no part of the work it performed was committed to the database. Subsequent MDS API calls will initiate implicit transactions on an API level if the user application does not begin another explicit transaction, or will begin a new explicit transaction if the user calls CMetaRepository::BeginTransaction again.

The reason MDS requires the user application to expressly close all explicit transactions, even those in which an error occurs, is to preserve the guarantee that everything between the user’s initial call to CMetaRepository::BeginTransaction and the final closing of the transaction by using either CMetaRepository::Rollback or CMetaRepository::Commit must succeed or fail as a unit regardless of the behavior of the user application. The user will thus be able to view MDS’s internal transaction management as a black box and not be required to exhibit any special behavior to make it work. Specifically, the user application should not be required to check error codes returned by MDS APIs in the middle of an explicit transaction and detect that MDS “wants” to end the transaction early because an error occurred. Therefore, MDS will not close the transaction when it detects an error, but will wait for the user application to close the transaction explicitly.

To understand what would happen if MDS implicitly aborted the transaction when an error occurred without the user application explicitly closing it, look at the previous example of a rollback of an explicit transaction due to an error.

In that case, when MDS API call 3 generated an error, MDS would terminate the transaction, roll back the work of MDS API calls 1-3, and assume the user application realized the transaction had been aborted.

However, in fact the user application may not have checked the return code and so might not realize this. Believing the original explicit transaction was still open, the user application would proceed with MDS API call 4, which would in fact commit that API’s changes to the database through an implicit MDS transaction. The user application would follow this with CMetaRepository::Commit, generating another MDS error (because there is no open explicit transaction as far as MDS is concerned); MDS API call 5, which would again commit work to the database as an implicit MDS transaction; and a final CMetaRepository::Commit, generating another MDS error.

At this point, work from MDS API calls 1-3 has been rolled back, but work from MDS API calls 4-5 has been committed to the database, violating the guarantee that work bracketed within an explicit transaction is either all committed or all rolled back. This is why MDS only closes an explicit transaction if instructed to do so by the user application.

Rollbacks on Application Termination, Server Crashes, Network Outages

If the user application terminates while an explicit transaction it has initiated is still open, the transaction will be rolled back. An open explicit transaction will also be rolled back if the Teradata Database Systems crashes or is reset, if the ODBC connection between the MetaManager and Teradata is lost, or if the MDS Manager crashes.

Behavior of Transactions Containing DDL Statements

The Teradata Database Systems places a restriction on data definition language (DDL) calls within transactions (for example, the SQL commands CREATE, REPLACE, DROP, and ALTER). At most one DDL statement may occur in a transaction, and it must be the last statement in that transaction. Several MDS APIs may execute DDL statements:

  • CMetaAIM::CreateMDSClassDesc
  • CMetaClassDesc::CreateMDSPropertyDesc
  • CMetaObject::WriteObject – if writing a new class or property description object (an object whose class is CLSLOID_ClassDescClass or CLSLOID_PropertyDescClass or the corresponding GUIDs)
  • CMetaObject::Delete – if called with a class or property description object or if deletion propagates to such an object
  • CMetaObject::RemoveFromDestCollection, CMetaObject::RemoveFromOrigCollection – if the relationship instance removed is of the ClassHasProperties type (RELLOID_ClassHasProperties or RELGUID_ClassHasProperties)

    • In the case of CMetaObject::Delete, the API may need to execute multiple DDL statements, for example, to remove columns or tables when deletion propagates to multiple class or property description objects.

    In order to accommodate Teradata’s restriction on DDL in transactions, both implicit and explicit MDS transactions will postpone executing their DDL statements until the end of the transaction. The CMetaStorage class will log specifications of postponed DDL statements associated with a transaction as rows in a table named metaddl.

    When a transaction is ready to commit (either because the user issues the final CMetaRepository::Commit of an explicit transaction, or the end of an implicit transaction is reached), CMetaStorage will check the metaddl table for any DDL statements associated with the transaction which must be executed. If there are any, it will execute the first DDL as the last statement of the transaction and then attempt the commit.

    If the transaction could not be committed (for example, an error occurred during it), it will be rolled back, and any other DDLs that were associated with that transaction will not be attempted. All DDL specification rows in metaddl that were associated with that transaction will be removed as a side effect of the rollback, because they were inserted into that table in the same transaction. At this point, the repository is in a consistent state, as no part of the original transaction, including any of its DDLs, was persisted in the database. MDS will return the error code META_E_TX_ROLLED_BACK.

    If the transaction committed successfully and additional DDLs are associated with it, CMetaStorage will attempt all of these DDLs, even if some of them produce errors. If they all succeed, MDS will return the success code S_OK. In this case, the repository is consistent, as both the transaction and all of its associated DDLs were persisted. If any of the additional DDLs fail, MDS will return the META_E_TX_COMMITTED_WITH_INCOMPLETE_DDL error code specifying that the original transaction committed but some of its associated DDLs failed. This is the only case where the repository will be left in an inconsistent state. The user can call the CMetaRepository::RetryDDL API to clean up by retrying the failed DDLs.

    The reason MDS will attempt all the associated DDLs for a committed transaction even after some of them produce errors is to complete as many of the DDLs associated with the transaction as possible because that transaction did commit. Because Teradata allows only one DDL in a transaction, it is impossible to guarantee all-or-nothing behavior of the original transaction plus all of its DDLs if there are multiple DDLs.

    Because MDS postpones DDL execution to the end of each transaction, it is possible for the user to create an entire AIM within an explicit transaction and be guaranteed that everything either succeeds or fails as a unit except the DDL statements. In the case of AIM creation, the DDL statements creates tables and columns for the unique properties of classes, so if processing fails during the DDLs, the only cleanup necessary is to create the missing tables and columns. Note that the user must close such a transaction before attempting to add data to any new classes created or any classes to which they have added new properties, as the tables and columns designated to contain that data will not exist until the DDL that creates them is executed.