Microsoft Office 2000/Visual Basic Programmer's Guide

Using Transactions

Defined as a "logical unit of work," a transaction is one of the features common to most database management systems. By wrapping multiple database operations into a single unit, transactions offer the developer the ability to enforce data integrity by making sure multiple operations can be treated by the engine as an "all or nothing" proposition, thereby never allowing the database to end up in an inconsistent state.

The most common example of transaction processing involves a bank's automated teller machine. The processes of dispensing cash and then debiting the user's account are considered to constitute a logical unit of work and are therefore wrapped in a transaction: The cash is not dispensed unless the system is also able to debit the account. By using a transaction, the entire operation either succeeds or fails. This maintains the consistent state of the ATM database.

Transactions can be defined by what are known as the ACID properties. The following attributes of transactions make up the ACID acronym:

• Atomic denotes that transactions are all-or-nothing operations. Each operation wrapped in a transaction must be successful for all operations to be committed.
  
  
• Consistent denotes that a transaction enables data operations to transform the database from one consistent state to another, even though at any point during the transaction the database may be inconsistent.
  
  
• Isolated denotes that all transactions are "invisible" to other transactions. That is, no transaction can see another transaction's updates to the database until the transaction is committed.
  
  
• Durable denotes that after a transaction is committed, its updates survive — even if there is a subsequent system crash.

Important   File-server databases, such as the Jet database engine, can't guarantee durable transactions. There are currently no file-server—based database engines that can fully support this criterion of true transactions. For example, a database connected to a file server can't be expected to fully support the durability rule if the file server crashes before a transaction has had time to commit its changes. If you require true transaction support with respect to durability, you should investigate the use of a client/server database engine such as SQL Server or the Microsoft Data Engine (MSDE).

Note   The behavior of transactions with Microsoft Jet databases differs in other respects from the behavior of Microsoft SQL Server and MSDE.