Updating Data with Transactions

A very important feature of most industrial-strength databases is support for transactions. A transaction is a set of database operations that must all complete or fail together. That is, either all the operations must complete successfully (commit the transaction), or all must be undone (roll back the transaction) so that the database is left in the state it was in before the transaction began.

The canonical transaction is depositing a check. If I write a check to you for $50 and you deposit it, we both expect that once the bank transaction is completed, your account will have increased by $50 and mine will have decreased by $50. Presumably the bank computer accomplishes this in two steps:

  1. Reduce my account by $50.

  2. Increase your account by $50.

If the system fails between steps 1 and 2 or for any reason your account cannot be increased by $50, the transaction should be rolled back; that is, it should fail as a whole (neither account should be affected).

If my account is reduced by $50 and your account is not increased, then the database has become corrupted. This should never be allowed, and it is the job of transactions to ensure either that both actions are taken or that neither is.

Tip

The remaining alternative, in which my account is not decreased but yours is increased, may be a happy outcome for you (“Bank Error In Your Favor -- Collect $50”), but the bank would not be pleased.

The ACID Test

Database designers define the requirements of a transaction ...

Get Programming ASP .NET now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.