SQL and MySQL course Β· Module 10: Transactions and advanced SQL
The ACID principles - four pillars of trust
In this lesson5
Every solid database rests on four principles whose first letters spell the word ACID. They are what let you trust the data in the Library.
A - Atomicity
A transaction is indivisible. It runs entirely or not at all. There is no "halfway" state - either the book is borrowed and marked unavailable, or nothing changed.
C - Consistency
After a transaction finishes, the database remains in a correct state, in line with all the rules (e.g. foreign keys). No loans will appear that point to a non-existent reader.
I - Isolation
When several scribes work at once, their transactions do not mix. One transaction does not see another's uncommitted changes. Each one feels like it is working alone.
D - Durability
After COMMIT the changes are permanent forever. Not even a power failure or a server restart will erase them - the Keeper has carved them in stone.
Summary
| Letter | Principle | Meaning |
|---|---|---|
| A | Atomicity | all or nothing |
| C | Consistency | the database always correct |
| I | Isolation | transactions do not mix |
| D | Durability | COMMIT is forever |
Remember these four pillars - they are the foundation of trust in every database.
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. What does the Atomicity principle in ACID mean?
2. What does the Consistency principle in ACID guarantee?
These are 2 of 4 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Vertical ordering
Arrange the steps of a safe transaction in the correct order - from opening to commit:
- Click in order
Click the letters of the ACID acronym in the correct order:
- Code editor
Write a transaction: start it with START TRANSACTION, then UPDATE the ksiazki table set cena = 99 for id = 1, and finally undo the changes with ROLLBACK.
- Vertical ordering
Arrange the steps of a test transaction that ultimately undoes the changes (top to bottom):