When to Use Transactions and Why

Often, to enforce data integrity, a set of operations must be considered as a single unit. For example, the transfer of funds from one bank account to another consists of two operations: entering a debit in one account and a matching credit in the other account. In practice, either both operations must succeed or neither operation should be processed.

In this situation, the debit and credit operations are a single unit, or a transaction. A transaction is a set of operations that are committed (saved) if, and only if, all of the operations succeed. If any of the operations fails, all of the operations that succeeded are rolled back (canceled), and the data is returned to the state it was before the transaction began.

DAO supports three transaction methods: one for starting, one for committing, and one for rolling back a transaction:

• The BeginTrans method begins a new transaction.

• The CommitTrans method commits all changes made to data since the most recent BeginTrans method.

• The Rollback method rolls back, or cancels, all changes made to data since the most recent BeginTrans method.

BeginTrans, CommitTrans, and Rollback are all methods of the Workspace object.

Transactions are also a powerful way to improve the performance of updates as well as multiuser concurrency. They enable the Jet database engine to accumulate multiple updates and write them as a single batch. To improve the performance of your application, use a transaction with:

• A sequence of recordset updates, insertions, and/or deletions.
• A sequence of action queries.

• A sequence of pass-through queries that change data.

• Any combination of these operations in a sequence.

On most servers, transactions generate locks that prevent other users from updating or even reading data affected by the transaction until it's committed or rolled back. You should therefore keep your transactions as short as possible and avoid beginning a transaction and then waiting for the user's response before completing it.