As we covered in the prior tutorial, a transaction is a single unit of work that has to be fully executed or completely aborted if there are any issues within the transactions. There are no states in between the two that are acceptable for a database to be in. With our example in the prior tutorial where James made a purchase for $500 for a computer from a store, all of the SQL statement that we defined in that transaction must be executed entirely:
BEGIN; UPDATE customer_account SET balance = balance – 500 WHERE account_id = 1000; UPDATE store_inventory SET quantity = quantity – 1 WHERE store_id = 5 AND product_name = ‘Computer’; INSERT INTO customer_order(account_id, product_name,store_id, quantity,cost) VALUES (1000, ‘Computer’,5, 1, 500); UPDATE store_account SET balance = balance + 500 WHERE store_id = 5; COMMIT;
It is not acceptable to only deduct James’ account balance or removing inventory from the store. If any of the SQL statements in the transaction fails, the entire transaction is rolled back (not committed) to the original state before the truncation occurred. If the transaction was successful, the changes to the database bring it from one consistent state to another. A consistent state is a state in which all of the data integrity constraints on the database are satisfied.
To ensure that we have consistency in the database, every transaction has to begin with the database in a known consistent state. If the database is not in a consistent state, what can occur is that the transaction could result in a database that is inconsistent due to violations of integrity or business rules. As such, all of the transactions that occur in the database are controlled and executed to ensure database integrity. These criteria fall under the ACID properties which include atomicity, consistency, isolation, and durability.
Atomicity requires that all SQL statements of a transaction be completed. If any of the SQL statements are not completed, the entire transaction should be aborted. For example, in our transaction above, if the first two statements executed:
UPDATE customer_account SET balance = balance – 500 WHERE account_id = 1000; UPDATE store_inventory SET quantity = quantity – 1 WHERE store_id = 5 AND product_name = ‘Computer’;
Then we ran into an error with the data that stopped the transaction, the entire transaction will be reverted prior to executing both of the statements. However, if all four statements in the transactions executed successfully, the entire transaction would be committed to the database.
Consistency ensures that the database is in a consistent state. This means that a transaction takes a database from one consistent state to another. When a transaction starts, the database must be in a consistent state and when the transaction ends, the database must be in a consistent state. If any of the transaction parts end up violating one of the integrity constraints, the entire transaction is aborted.
The isolation means that the data that is used during the first transaction cannot be used in another transaction until the first transaction has finished executing. In looking at the example above, if James and another customer purchased a computer and James has started the transaction, the second customer cannot attempt to purchase the same computer until James’ transaction is completed. Otherwise, they may have both tried to purchase the single available computer. This is especially important for multiuser databases where you will have many users accessing and updating the database at the same time.
Durability is the last ACID property and it ensures that when the transaction changes are done and committed, they cannot be undone or removed even if there is a system failure.
We will get into each of these properties in further detail in the upcoming tutorials.
Source: Authored by Vincent Tran