10.01 Introduction to ACID properties
Let’s look at an example of a banking application handling money transfers. If we transfer £100 from A to B, these are the stages:
Any one of these states can fail – at a minimum we can lose power mid-transfer. The issue here is the entire set of state transition only makes sense as a full block.
If anything fails, we need to roll everything back, somehow.
Another bigger problem would be if A has two outstanding transactions happening at the same time. What happens if A has exactly £900 in the account and schedules two simultaneous transactions, one for B of £100 and another for C of £900?
We can resolve these problems by ensuring our system has the ACID properties.
ACID is an acronym for Atomicity, Consistency, Isolation, Durability – a set of properties of database transactions intended to guarantee validity of data despite any possible errors that may arise, including power outages.
A group of databases operations that, together, satisfy the ACID properties is referred to as a transaction.
Further describing the purpose of each of the 4 tenets of ACID we have:
A more simple summary of this:
10.02 Transactions and serialisation
If we’re going to be able to make the guarantees that the ACID properties require, we’re going to need to treat a group of operations that have to be carried out en bloc in a specific way. The mechanism for that is called transactions.
Let’s imagine a three-stage transaction:
The database is only valid on green states, that is, either before starting operation 1 of after completing operation 3. The other two intermediary states must be considered invalid states and the database system should protect against those in the event of system failure.
Starting with Isolation, we can restrict access to data that might be affected by any operation in the block. In practice, the database system implements a lock that must be held in order to modify that particular set of data. This lock guarantees that no concurrent access to the data happens, thus forcing serialization.
Atomicity requires the implementation of rollback. This ensures that in case an operation in a block fails, we must be able to rollback to the state immediately before the block was started. In other words, we guarantee that the database returns to a green state in the event of a failed operation.
Durability means that valid states are reliably recorded. The obvious way of achieving that is to write both states to persistent (i.e. non-volatile) storage:
Consistency comes as a result of restricting access to intermediate states of the database. We only store initial and final states.
In practice, we only give operations access to the database if we know they won’t suffer side effects from this known inconsistent (intermediary) state of the database.
The mechanism to do that is called a Transaction. Once we decide that group of operations must be atomic, or carried out as a block, we declare them as a transaction by using START TRANSACTION; command. With that, any operations placed next until a call to COMMIT; will be treated as intermediate states of the database. In case of any errors, we can use the ROLLBACK; command to undo the inconsistent states.
So in other words we need to start a transaction; carry out the operations; and then either commit (if all good) or rollback (if there’s something we did wrong).
START TRANSCATION; SELECT ... INSERT ... UPDATE ... COMMIT; or ROLLBACK;
Some details to keep in mind:
Inconsistent Analysis is when two transactions access the same data. One has multiple queries which give inconsistent information. For example:
Transaction 1 – moving money from A to B
START TRANSACTION; UPDATE Balances SET Balance=Balance-100 WHERE Acc="A"; UPDATE Balances SET Balance=Balance+100 WHERE Acc="B"; COMMIT;
Transaction 2 – checking the balances
START TRANSACTION; SELECT Balance FROM Balances WHERE Acc="A"; SELECT Balance FROM Balances WHERE Acc="B"; COMMIT;
If these happen concurrently, it’s possible that transaction 2 could check A’s balance before transaction 1 takes place, and B’s balance after, which will obviously give an incorrect result.
Two options for solutions: