ACID

ATOMICITY CONCURRENCY ISOLATION DURABILITY

Please see the below powerpoint for a detailed run through of ACID and its importance/relevance within theatre booking



Last edited: Samantha Beaumont Last date: 18th October 2011

Atomicity
To understand atomicity, you need to understand the term **transaction **. 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 a seat is booked: Seat Allocation Table - holds a record for which seats are booked for a production 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.The customer looks up the seats available from a table that records all the seats available for a production on a certain night. 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, i.e. there are not enough seats to left, then the transaction will not take place.
 * Accounts Table - holds payment details by customer
 * Customer Table - holds all customer details including the delvery details.
 * 1) 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).
 * 2) 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.
 * 3) <span style="color: black; font-family: 'Calibri','sans-serif'; font-size: 16px;">(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).
 * 4) <span style="color: black; font-family: 'Calibri','sans-serif'; font-size: 16px;">The **<span style="font-family: 'Calibri','sans-serif';">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.

<span style="color: black; font-family: 'Calibri','sans-serif'; font-size: 16px;">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. //**<span style="color: black; font-family: 'Calibri','sans-serif'; font-size: 19px;">Durability **//<span style="color: black; font-family: 'Calibri','sans-serif'; font-size: 16px;">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 **<span style="font-family: 'Calibri','sans-serif';">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.
 * <span style="color: black; font-family: 'Calibri','sans-serif'; font-size: 19px;">Isolation **