SQL and MySQL course Β· Module 10: Transactions and advanced SQL

The ACID principles - four pillars of trust

2 min read Β·
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

LetterPrincipleMeaning
AAtomicityall or nothing
CConsistencythe database always correct
IIsolationtransactions do not mix
DDurabilityCOMMIT 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. 1. What does the Atomicity principle in ACID mean?

  2. 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):

  1. QuestionAtomicity
  2. QuestionConsistency
  3. Put in orderSteps of a safe transaction
  4. QuestionIsolation
  5. QuestionDurability
  6. Click in orderThe letters of the ACID acronym
  7. Coding taskA transaction with a rollback
  8. Put in orderA test transaction with ROLLBACK

Useful articles