ACID - atomicity, consistency, isolation, durability



Definition:

The ACID model defines 4 goals that every database management system must try to achieve to be considered reliable.

Atomicity

The database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails. It is critical that the database management system maintain the atomic nature of transactions in spite of any DBMS, operating system or hardware failure.

Consistency

Data cannot be written that would violate the database’s own rules for valid data. If a certain transaction occurs that attempts to introduce inconsistent data, the entire transaction is rolled back and an error returned to the user.

Isolation

Isolation means other operations should not be able to see the transaction half way through. No operation outside the transaction can ever see the data in an intermediate state. This is frequently done by the server locking the table or more likely the rows that are being updated. This is called record-locking.

Durability

Durability means that once the transaction has been committed successfully it cannot be undone. This means it will survive system failure, and that the database system has checked the integrity constraints and won't need to abort the transaction. Many databases write all transactions into a log that can be played back to recreate the system state just before it failed. In that case a transaction is only considered committed after it is safely in the log.

No database that fails to meet any of these four goals can be considered reliable.


Description & examples specific to the case study

Atomicity

To understand atomicity, you need to understand the term transaction. In

In relational databases, often data in multiple tables needs to be updated. For example in the theatre booking system may need to update these tables when an seat is booked:
  • Seat Allocation Table - holds a record for which seats are booked for a production
  • Accounts Table - holds payment details by customer
  • Customer Table - holds all customer details including the delvery details.

Here are some of the steps that may take place in one transaction when a customer wants to book 4 tickets in a show. The transaction would be written in a query language, such as SQL.

  1. The customer looks up the seats available from a table that records all the seats available for a production on a certain night.
  2. The customer reserves seats, for example 4 and so the seats table is updated to show that the seats are no longer available (and perhaps details of who has reserved the seats).
  3. The customer must also pay and so the accounts table needs to be updates to give details of the customer and the amount to pay.
  4. (Calculations such as any discount may be required for some seats and there may also be a field to show the full amount that needs to be recorded).
  5. The customer table may also need to be updated with the customer address or contact details so that the tickets can be dispatched to the correct location.

The problem arises if something goes wrong while writing to these three tables. Maybe a hardware failure or a program bug, say a duplicate data row added or a crash due to hardware. If you work with databases, you will experience a failure at some time or other. You are most likely to get problems during development if a SQL query goes wrong. Say it takes too long to run and you have to kill the process. To ensure that the database is reliable, you need to ensure that all 5 steps of the transaction are completed. If there is a system failure, then the database must be restored to it's state before the transaction was started. This is resolved in SQL by using a command at the start of the transaction, begin tran then do the update and if it gets the correct results, finish off with a commit tran.

Consistency
For example, in the theatre booking system, customers may need to give details of their date of birth as they may have discounts if they are a certain age. The customer table might have a check on a date of birth age field preventing birthdays say 120 years before or after today so a living person cannot have a date of birth in 1776 or 2070. Alternatively the customer may want to book 15 seats when there are only 10 setas available. The rules of the database may check to see of the number of seats requested are greater than the number of seats available before allowing the transaction to continue otherwise there may be overbooking of seats in some productions that may not require fixed seating. If data is not valid, ie there are not enough seats to left, then the transaction will not take place.

Isolation

It must be impossible to read the seats allocation table in a different operation immediately after the seats have been reserved but before the accounts table row is added and the customer table is updated.

Durability
Durability in relational database systems is usually achieved by means of transaction logs- recyclable files - files used to store all database transactions in a session. Once a user issues a commit command, then the transaction is first written to the database files stored on a non-volatile medium such as a hard disk, which is done before confirming to the user that the save has occurred. If a database crashes before the save, the data is still on the transaction logs the next time the database is restarted, but any uncommitted changes are undone or rolled back. In distributed computing where servers are geographically dispersed, this guarantee is difficult or tricky to implement, so the same is achieved by use of the two-phase commit.


References and Resources:


Mike Chapple, The ACID Model, http://databases.about.com/od/specificproducts/a/acid.htm, 11 January 2012
David Bolton, What is a Transaction?, http://cplus.about.com/od/thebusinessofsoftware/a/transactions.htm, 11 January 2012