Database Reference
In-Depth Information
When to Use Transactions
You should use transactions when several operations must succeed or fail as a unit. The following are
some frequent scenarios where using transactions is recommended:
•
In batch processing, where multiple rows must be inserted, updated, or deleted as
a single unit
•
Whenever a change to one table requires that other tables be kept consistent
•
When modifying data in two or more databases concurrently
•
In distributed transactions, where data is manipulated in databases on different
servers
When you use transactions, you place locks on data pending permanent change to the database. No
other operations can take place on locked data until the lock is released. You could lock anything from a
single row up to the whole database. This is called
concurrency,
which means how the database handles
multiple updates at one time.
In the bank example, locks ensure that two separate transactions don't access the same accounts at
the same time. If they did, either deposits or withdrawals could be lost.
Note
It's important to keep transactions pending for the shortest period of time. A lock stops others from
accessing the locked database resource. Too many locks, or locks on frequently accessed resources, can seriously
degrade performance.
Understanding ACID Properties
A transaction is characterized by four properties, often referred to as the
ACID properties:
atomicity,
consistency, isolation, and durability.
Note
The term ACID was coined by Andreas Reuter in 1983.
Atomicity:
A transaction is atomic if it's regarded as a single action rather than a
collection of separate operations. So, only when all the separate operations
succeed does a transaction succeed and is committed to the database. On the
other hand, if a single operation fails during the transaction, everything is
considered to have failed and must be undone (rolled back) if it has already
taken place. In the case of the order-entry system of the Northwind database,
when you enter an order into the Orders and Order Details tables, data will be
saved together in both tables, or it won't be saved at all.
