Database Level Transactions

• Feb 01, 2021
• 3 minutes to read

A database level transaction (explicit transaction) is a database transaction that is explicitly started within a Session or UnitOfWork to isolate and finalize transactional changes at the database level.

XPO starts a short database level transaction to commit changes made in an ORM level transaction to a database, and then automatically closes the transaction, to avoid concurrency issues.

Database Level Transaction Within Session or UnitOfWork

Use the following methods to work with database level transactions within a Session or UnitOfWork:

• Session.ExplicitBeginTransaction
• Session.ExplicitCommitTransaction
• Session.ExplicitRollbackTransaction

C#

using (UnitOfWork uow = new UnitOfWork(session1.DataLayer))
{
    Person person = new Person(uow);
    person.Name = "Thomas Brown";
    person.Age = 33;

    // Start a database level transaction.
    uow.ExplicitBeginTransaction();
    try {
        uow.CommitChanges();

        // A person has been temporarily stored to a database, thus FindObject locates this person.
        Person savedPerson = uow.FindObject<Person>(CriteriaOperator.Parse(
            "Name = ? And Age = ?", person.Name, person.Age));

        savedPerson.Name = "James Smith";
        savedPerson.Age = 60;

        // Update person with new data.
        uow.CommitChanges();
        // Finalize changes and close the database level transaction.
        uow.ExplicitCommitTransaction();
    } catch {
        // Roll back changes and close the database level transaction.
        uow.ExplicitRollbackTransaction();
        throw;
    }
}

VB.NET

Using uow As New UnitOfWork(session1.DataLayer)
    Dim person As New Person(uow)
    person.Name = "Thomas Brown"
    person.Age = 33

    ' Start a database level transaction.
    uow.ExplicitBeginTransaction() 
    Try
        uow.CommitChanges()

        ' A person has been temporarily stored to a database, thus FindObject locates this person.
        Dim savedPerson As Person = uow.FindObject(Of Person)(CriteriaOperator.Parse( _
            "Name = ? And Age = ?", person.Name, person.Age))

        savedPerson.Name = "James Smith"
        savedPerson.Age = 60

        ' Update person with new data.
        uow.CommitChanges()
        ' Finalize changes and close the database level transaction.
        uow.ExplicitCommitTransaction()
    Catch
        ' Roll back changes and close the database level transaction.
        uow.ExplicitRollbackTransaction()
        Throw
    End Try
End Using

Database Level Transaction Within ExplicitUnitOfWork

XPO provides the ExplicitUnitOfWork. The ExplicitUnitOfWork automatically starts a database level transaction before object changes are temporarily saved to a database for the first time. Intermediate object changes are also automatically tracked, so that you can access modified objects without having to commit the changes beforehand.

The following code snippet demonstrates how to simplify the code demonstrated above, using an ExplicitUnitOfWork.

C#

using (ExplicitUnitOfWork euow = new ExplicitUnitOfWork(session1.DataLayer)) {
    // Starts tracking changes to persistent objects.
    Person person = new Person(euow);
    person.Name = "Thomas Brown";
    person.Age = 33;

    // Starts a database level transaction and flushes all intermediate object changes to a database.
    // Thus, person has been temporarily stored to a database and FindObject locates this person.
    Person savedPerson = euow.FindObject<Person>(CriteriaOperator.Parse(
        "Name = ? And Age = ?", person.Name, person.Age));

    savedPerson.Name = "James Smith";
    savedPerson.Age = 60;

    // Updates person with new data and commits the database level transaction.
    euow.CommitChanges();
}

VB.NET

Using euow As New ExplicitUnitOfWork(session1.DataLayer)
    ' Starts tracking changes to persistent objects.
    Dim person As New Person(euow)
    person.Name = "Thomas Brown"
    person.Age = 33

    ' Starts a database level transaction and flushes all intermediate object changes to a database.
    ' Thus, person has been temporarily stored to a database and FindObject locates this person.
    Dim savedPerson As Person = euow.FindObject(Of Person)(CriteriaOperator.Parse( _
    "Name = ? And Age = ?", person.Name, person.Age))

    savedPerson.Name = "James Smith"
    savedPerson.Age = 60

    ' Updates person with new data and commits the database level transaction.
    euow.CommitChanges()
End Using

Note

Don’t use the following methods with ExplicitUnitOfWork because they are automatically called at appropriate moments:

• ExplicitBeginTransaction
• ExplicitCommitTransaction
• ExplicitRollbackTransaction

Important

A Session or UnitOfWork that starts a database level transaction must be the exclusive owner of the database connection. Therefore, only a single database level transaction can be open at a time.