In an effort to provide information for those of you who are
newcomers to databases, especially with the wonderul new Sql Server
Express, I have committed myself to about half a dozen posts or so
targeted directly at you to help you understand and build better
databases. Stay tuned to this blog for weekly updates to this series, or subscribe to the rss feed.
The ACID model has been a cornerstone of database modeling for a
very long time. Hopefully, its something that is still taught in CS
courses. ACID lays out 4 goals that must be met before your database
can even begin to be considered useful and reliable.
Atomicity – Every transaction that occurs within
the scope of the database is a single piece of work: Atomic. The atomic
nature of transactions is maintained by ensuring that if any one part
of a transaction fails, then the entire transaction fails. This is easy
to do because of the singular purpose served by the transaction. This
is also referred to as the "all or nothing" approach.
Consistency - All data in the database must
exist in a consistent state once a transaction completes. To help out
with this, we use rules, keys, normalization, etc all help to
maintain data integrity. All of these things combined helps to ensure
our data always exists in a consistent state.
Isolation – Modifications to your data must be
isolated from other concurrent transactions. No concurrent transaction
(Transaction A) should see any data being modified by another
concurrent transaction (Transaction B) while it is in an intermediate
state. Transaction A should see the data as it existed prior to being
modified by Transaction B, or after Transaction B has completed and
committed its changes.
Durability – After a transaction has successfully
completed and committed it changes, the data is permanently in place in
the database, even in the event of hardware or software failures.
Database backups and transaction logs help you out with this by
facilitating a means of restoring committed transactions.
Next topic in this series: Normalization.