Indexes
To find Kai, the database has no shortcut. It reads every row, top to bottom, and checks each name: 7 rows, 7 reads. With a million rows it would read a million. The work grows with n, written O(n).
An index is a second, sorted copy of one column. Each entry is a name plus the id of its row. The entries are filed in a B-tree: a short tree of sorted nodes, A on the left, Z on the right.
Run the same query again. The database now starts at the root of the tree. Kai comes after Dee in the alphabet, so it follows the right branch and never looks at Ana, Ben or Cal.
In the leaf, Kai's entry says row 4, so the database reads just that row. Two tree nodes and one row instead of seven rows. Each level cuts the search down, so the work grows like log n: O(log n).
An index is not free. It takes extra space on disk, and every INSERT, UPDATE or DELETE that touches the column must update the tree too, so writes get slower. Index the columns you search often.
Transactions
Now move 50 from Ana to Ben. That takes two changes, and they must never be split. BEGIN starts a transaction: every change until COMMIT counts as one unit. All of it happens, or none of it does.
Lines 6 and 7 take 50 from Ana: 100 becomes 50. Before it changes the row, the database saves the old value, 100, in an undo journal. That note is what lets it take the change back.
Meanwhile another user reads Ana's balance and gets 100, not 50. Changes inside an unfinished transaction stay hidden from everyone else until COMMIT. That is isolation: no one sees half-done work.
Crash and commit
Now the program crashes before line 8 runs. Ana has lost 50, but Ben never got it: 50 has vanished. If the database kept this state, the bank would simply lose money.
When the database opens again, it finds the unfinished transaction and rolls it back: the old 100 goes from the journal back into Ana's row. Atomicity: all or nothing. ROLLBACK does the same on request.
Run the transfer again with no crash. Both updates run, then COMMIT makes them final: Ana 50, Ben 70. The total is still 120. Consistency: a transaction moves the data from one valid state to another.
Once COMMIT returns, the change is safely on disk: a crash a second later cannot undo it. That is durability. Atomicity, Consistency, Isolation and Durability together spell ACID.
Try it with Python's sqlite3
-- step 1/12 · lines 1-2
>>> import sqlite3
>>> db = sqlite3.connect("bank.db", isolation_level=None) # we write BEGIN/COMMIT
>>> db.execute("EXPLAIN QUERY PLAN SELECT * FROM accounts WHERE name = 'Kai'").fetchone()[3]
'SCAN accounts'
>>> db.execute("SELECT * FROM accounts WHERE name = 'Kai'").fetchall()
[(4, 'Kai', 60)]
Real index nodes are disk pages holding hundreds of entries, so the tree stays very shallow: if every node points to 100 children, three levels already reach 100 × 100 × 100 = 1,000,000 rows. SQLite (in its default mode) copies the original content into a rollback journal before it changes the database. PostgreSQL instead keeps a write-ahead log; both let the database recover a consistent state after a crash.