DAO Workspace: Managing Transactions

Article
09/15/2006

This article explains the MFC facilities available for managing transactions and refers you to additional information in DAO Help. For general transaction information in DAO, see the topic "BeginTrans, CommitTrans, Rollback Methods" in DAO Help.

Topics covered include:

Transaction: defined
Transaction spaces
Transaction example
Additional reading about transactions

Transaction: Defined

A transaction is a series of changes made to a database's data and/or schema. Mark the beginning of a transaction by calling the member function of class . Commit the transaction using the member function, or undo all your changes since BeginTrans using the member function.

The key idea of transactions is that the operation is “atomic” — a group of related smaller operations must all succeed for the whole operation to succeed. If one small operation fails, the whole operation fails.

There is an implicit transaction while action queries are running. If a query doesn't complete for any reason, it is automatically rolled back. Transactions are optional and can be nested up to five levels. (This is in contrast to ODBC, which does not permit nested transactions.) Transactions increase the speed of data changing operations and enable changes to be reversed easily.

The current transaction consists of all changes made to a recordset object after you last called the member function and before you call the or member functions.

Transaction Spaces

The workspace object defines a transaction space. Transactions are global to the workspace in which they occur. They affect all open databases, recordsets, and querydefs in the same workspace. If you have several open recordsets and/or databases in a workspace, each call to , , and applies to all of the objects.

For example, suppose you have called BeginTrans for a workspace and you begin updates through two recordsets that belong to database objects in the same workspace. If you call CommitTrans or RollBack in the workspace, the call affects both recordsets, even if they are open on different databases.

If this transaction model is not what you need, you can open separate transaction spaces by opening separate workspaces. Create a new object for each separate transaction space. For more information, see the article DAO Workspace: Opening a Separate Transaction Space.

Transaction Example

The following example illustrates transactions by using two recordsets to delete a student's enrollment from a school registration database. First it removes the student from all classes in which the student is enrolled. Then it removes the student's master record, after which the student no longer exists in the database.

The Delete calls in both recordsets must succeed, so a transaction is required.

****Important ****The example shown illustrates correct transaction procedure, but for the illustrated case this is not the most efficient way to do the job. For details, see the discussion in Efficiency Considerations for This Example.

The example assumes the existence of:

m_dbStudentReg, a document data member that contains a object already open on the database.
m_rsStudentSet, a document data member that contains a recordset object based on class CStudentSet, derived from . This recordset returns all enrolled students.
CEnrollmentSet, a second CDaoRecordset-derived class. This recordset, as written by ClassWizard, returns all students enrolled in all classes. The example code filters the recordset to return only the records representing classes in which the specified student is enrolled.

The example modifies the default SQL string defined with ClassWizard before opening the recordset. The modification filters the records with a student ID passed in as a parameter.

BOOL CEnrollDoc::RemoveStudent(CString strStudentID)
{
    // Construct a recordset for courses student is in
    CEnrollmentSet rsEnrollmentSet( &m_dbStudentReg );

    // Define the SQL string for the recordset to
    // Filter records with the SQL keyword WHERE
    CString strSQL = rsEnrollmentSet.GetDefautlSQL( ) +
                "WHERE [Student ID] = " + strStudentID;
    try
    {
        // Open the recordset using
        // the modified SQL string
        rsEnrollmentSet.Open( dbOpenDynaset, strSQL );

        // Start the transaction
        m_dbStudentReg.m_pWorkspace->BeginTrans( );

        // Remove the student from all classes the
        // student is enrolled in
        while ( !rsEnrollmentSet.IsEOF( ) )
        {
            rsEnrollmentSet.Delete( );
            rsEnrollmentSet.MoveNext( );
        }
        // Delete the student's master record
        m_rsStudentSet.Delete( );

       // Commit the transaction
       m_dbStudentReg.m_pWorkspace->CommitTrans( );
    }
    catch(CDaoException* e)
    {
        m_dbStudentReg.m_pWorkspace->Rollback( );
        AfxMessageBox( "Failed to remove student." );
        e->Delete( );
        return FALSE;
    }
    m_rsStudentSet.Close( );
}

For information about the try/catch exception handling shown here, see the article Exceptions: Database Exceptions and the class.

Efficiency Considerations for This Example

The transaction example shown in Transaction Example shows you how to do transactions. But in some cases, as in deleting records, transactions may not be most efficient approach. In fact, there are two more efficient approaches to deleting the student record along with all related records for that student:

Use cascade deletes.

If the student registration database defines a relation between the STUDENT and ENROLLMENT tables with the cascade delete attribute set, you can delete a single student record in the STUDENT table and let cascade deletes remove all related records in the ENROLLMENT table.

For information about cascade deletes, see the topic "Relation Object" in DAO Help. Relations in MFC are discussed under class in the Class Library Reference.
Use a bulk query.

A bulk query would delete all records in any tables you specify that contain the student ID for the student you want to delete.

The query’s SQL statement looks like this:
```
DELETE FROM STUDENT,ENROLLMENT WHERE STUDENT.StudentID = ENROLLMENT.StudentID AND StudentID = strStudentID
```
The expression “STUDENT.StudentID = ENROLLMENT.StudentID” “joins” the tables on the StudentID field. The expression “StudentID = strStudentID” finds those records in the join that have the particular student ID in strStudentID. The SQL deletes those records.

Transactions do have a role to play, of course. The point is that you should use the best approach for the particular task.

Additional Reading About Transactions

For more information about transactions, see the following topics in DAO Help:

BeginTrans, CommitTrans, Rollback Methods
CreateWorkspace Method
BOF, EOF Properties
IsolateODBCTrans Property

In the Class Library Reference, see: , especially the , , , and member functions.

Also see , especially the , , , , , and member functions.

See Also DAO: Where Is..., DAO Recordset

Share via