 |
The ACID Test -
A mini database tutorial for those that are interested... |
|
By Jason Parker, President
Atomicity
Consistency
Isolation
Durability
When talking databases that handle mission-critical business transactions and information you are talking ACID features. If customer satisfaction and revenue depend on the quality of your business information, ACID is the database feature set that delivers peace of mind.
Atomicity
Atomicity (pronounced "atom-ih-sit-ee") means “all or nothing”. Derived from the word “atom” for indivisible, atomicity describes database operations that are defined within a single indivisible transaction. If any single operation fails then the whole transaction fails. This ensures that the database is in a valid state at all times. For instance, a transaction to transfer funds from one account to another involves making a withdrawal operation from the first account and a deposit operation on the second. If the deposit operation failed, you don’t want the withdrawal operation to happen either. Otherwise that money would disappear! Lumping both operations into a single atomic transaction ensures data integrity. To provide atomicity database systems support the concept of transactions and rollback, to rescind operations if a portions of a transaction fail.
Atomicity across a network involving a client process and the database server is often achieved through a process called "Two-Phase Commit". Roughly like this:
|
1)
|
Client sends SQL commands to database within a single transaction. It is
not yet committed as a whole unit (a database does a lot of work to support
this).
|
|
2)
|
Client sends a "pre-" commit command to the database (or multiple
DBs).
|
|
3)
|
Server(s) verifies that a commit would result in an ACID complete
transaction and returns this acknowledgement to the client.
|
|
4)
|
Client receives this acknowledgment and issues the commit command with
assurance that it will result in a good transaction.
|
|
5)
|
Database(s) completes "commits" the transaction and responds with a
success acknowledgement.
|
Note that from the developer point of view two-phase commit is often automatic. The program will send a "commit" command and this entire cycle occurs. If something wrong happens then the error message will indicate where the problem happened.
Consistency
Consistency ensures that only operations that comply with database validity constraints are allowed. For instance, a database tracking a checking account may only allow unique check numbers to exist for each transaction. An operation that repeats a check number should fail due to consistency and ensure that the information in the database is correct and accurate. Another example could be that the database designer decided that people added to the database must have a first and last name defined. Other factors relating to the system can also cause a consistency problem, such as a network failure or a lack of disk space. Consistency rules enforced by the database will make sure that these situations do not leave information in an “inconsistent” state.
Isolation
Isolation ensures that every transaction has consistent view of the database regardless of other concurrent transactions. In other words isolation gives each transaction the appearance of happening by itself either before or after all other transactions. This can be very challenging for a database system when many concurrent transactions are expected. The database system can either provide a prior view during a separate concurrent transaction, or it can “lock” the resource until the transaction completes. For instance a teller looking up a balance must be isolated from a concurrent transaction involving a withdrawal from the same account. Only when the withdrawal transaction commits successfully and the teller looks at the balance again will the new balance be reported. Locking the information can be effective technique for isolation but can introduce additional problems if transactions are lengthy or response times critical.
Durability
Durability ensures that once a transaction is complete the information as changed will survive failures of any kind. A system crash or any other failure must not be allowed to lose the results of a transaction or the contents of the database. Durability is often achieved through separate transaction logs that can "re-create" all transactions from some picked point in time (like a backup). Other ways include database "mirrors" or other type of data replication or multiple and simultaneous database servers running on different machines. Backups by themselves do not provide "durability" of all transactions.
On Line Transaction Processing – OLTP
The ACID features of a database are important in any mission-critical business application, but they are particularly important to support on-line transaction processing systems. OLTP allows full computerization of an entire business transaction in real-time. E-business is the extension of this principle beyond individual business transactions to the entire operation of business and its interactions with customers as well.
Database systems will often provide a variety of mechanisms that when utilized correctly provide the ACID features necessary for business executives to sleep at night. It is important to note however that competent programming is necessary to make good use of these mechanisms and realize the promise robust, safe and secure data.
|
