Computer science fundamentals · Lesson 48 of 60 · Concept lesson · Optional

Data integrity and transactions

Data integrity means the data stays correct and consistent, which databases protect with constraints such as unique keys and required values.

A transaction groups several changes so they all succeed or all fail together.

ACID stands for atomicity, consistency, isolation and durability, the four promises a reliable transaction makes.

Picture it

BEGIN
  take 50 from Ada
  give 50 to Mia
COMMIT      all changes stick

an error?  ROLLBACK, nothing changed

A money transfer as a transaction. Either both steps happen and are committed, or an error undoes everything and the balances stay as they were.

Code example

import sqlite3

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))")
db.execute("INSERT INTO accounts VALUES ('Ada', 100)")
db.commit()
try:
    with db:
        db.execute("UPDATE accounts SET balance = balance - 30 WHERE name = 'Ada'")
        db.execute("UPDATE accounts SET balance = balance - 90 WHERE name = 'Ada'")
except sqlite3.IntegrityError:
    print("rolled back")
print(db.execute("SELECT balance FROM accounts").fetchone()[0])

Output:

rolled back
100

The second update breaks the CHECK constraint, so the whole transaction is rolled back, including the first update, and the balance is still 100.

Check yourself

Pick an answer for each question. You can retry as many times as you like.

Loading the questions…