DB Transactions & Query Optimization

Reviewed & published by Brayan K

A transaction groups database operations so they succeed or fail together — and query optimization makes the operations that do run as fast as possible. Together they keep your data both correct and quick.

Part of the free Flask course at LearnCodingFast — hands-on lessons with worked examples and the output they print, plus practice exercises and a quick quiz.

In this lesson you'll commit and roll back transactions with SQLAlchemy, handle failures safely with try/except, then speed up reads with indexes, eager loading, and smarter counting.

What You'll Learn in This Lesson

• Committing and rolling back transactions
• The unit-of-work pattern and atomicity
• Safe writes with try/except and rollback
• Avoiding the N+1 problem with eager loading
• Adding indexes and reading EXPLAIN
• count() vs len() and pagination

1 Transactions: Commit and Rollback

A transaction is a group of database changes that must succeed or fail together — this all-or-nothing property is called atomicity. With SQLAlchemy you stage changes in db.session, then db.session.commit() writes them all at once. If something goes wrong, db.session.rollback() undoes every pending change.

The session implements the unit-of-work pattern: it tracks your adds, updates, and deletes and sends them as a single transaction at commit time.

# Transfer money atomically: both updates commit, or neither does
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()  # assume this is bound to your app

def transfer(from_acct, to_acct, amount):
    try:
        from_acct.balance -= amount
        to_acct.balance += amount
        # Both changes are written together as one transaction.
        db.session.commit()
        print("Transfer committed")
    except Exception as error:
        # Any failure undoes BOTH updates - no half-finished transfer.
        db.session.rollback()
        print("Rolled back:", error)
        raise

# The key idea: the two balance changes are atomic.
# You can never debit one account without crediting the other.
print("Wrap related writes in one commit; roll back on error")

# ✅ Expected output:
# Wrap related writes in one commit; roll back on error

2 Avoiding the N+1 Query Problem

The N+1 problem is a classic performance trap. You load N rows (say, 50 authors) with one query, then loop over them and accidentally fire one more query per author to fetch their books — 51 queries in total. The fix is eager loading: tell SQLAlchemy to fetch the related rows up front with joinedload (one JOIN) or selectinload (a second batched query).

# Slow N+1 vs fast eager loading
from sqlalchemy.orm import joinedload, selectinload

# SLOW: 1 query for authors, then 1 per author for their books.
def list_books_slow(Author):
    authors = Author.query.all()              # 1 query
    for author in authors:
        for book in author.books:             # +1 query EACH TIME
            print(author.name, book.title)

# FAST: load authors and their books together up front.
def list_books_fast(Author):
    authors = (
        Author.query
        .options(joinedload(Author.books))    # one JOIN, no N+1
        .all()
    )
    for author in authors:
        for book in author.books:             # already loaded
            print(author.name, book.title)

# For one-to-many you often prefer selectinload:
#   Author.query.options(selectinload(Author.books)).all()
print("Eager loading turns 51 queries into 1 or 2")

# ✅ Expected output:
# Eager loading turns 51 queries into 1 or 2

3 Indexes, EXPLAIN, Counting & Pagination

An index is a sorted lookup structure on a column. Without one the database scans every row; with one it jumps straight to matches. Add indexes to columns you filter or join on often. Run EXPLAIN to see whether a query uses an index or does a full table scan.

When counting, prefer query.count() (a database COUNT) over len(query.all()), which loads every row into memory. And never return thousands of rows at once — use pagination to fetch one page at a time.

# Index a column, count efficiently, and paginate
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    # index=True speeds up lookups and filters on email.
    email = db.Column(db.String(120), unique=True, index=True)

# Counting: ask the DB to count, do NOT load every row.
def how_many():
    total = User.query.count()          # SELECT COUNT(*) - fast
    # Avoid: len(User.query.all())       # loads ALL rows just to count
    return total

# Pagination: fetch one page instead of everything.
def page(n=1, per_page=20):
    result = User.query.order_by(User.id).paginate(page=n, per_page=per_page)
    return result.items                 # only this page's rows

# Diagnose a slow query with EXPLAIN:
#   EXPLAIN SELECT * FROM user WHERE email = 'a@b.com';
print("Index hot columns, count() not len(), and paginate")

# ✅ Expected output:
# Index hot columns, count() not len(), and paginate

Indexes speed up reads but slightly slow down writes and use disk, so index the columns that matter, not every column. Let EXPLAIN guide you to the queries that need help.

🎯 Your Turn: Fill in the Blanks

Complete the safe write below. Replace each ___ so it commits on success and rolls back on failure.

# YOUR TURN: replace each ___ to make the write safe
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

def save_user(user):
    try:
        db.session.add(user)
        db.session.___()          # save the change permanently
    except Exception as error:
        db.session.___()          # undo the pending change on failure
        raise
    print("User saved (or safely rolled back)")

print("safe save ready")

Common Errors & Quick Reference

❌ PendingRollbackError on the next query

A previous commit failed and you never rolled back. Always call db.session.rollback() in your except block so the session is usable again.

❌ A page loads slowly as data grows

You are probably hitting N+1 or loading too many rows. Add joinedload/selectinload, index your filter columns, and paginate.

🏆 Mini Challenge: Bulk Insert in One Transaction

Insert several rows as a single atomic transaction that rolls back on any failure.

• Add all the new objects to the session
• Commit once so they save together
• Roll back in an except block if anything fails

# Mini Challenge: insert many rows atomically
from flask_sqlalchemy import SQLAlchemy

db = SQLAlchemy()

def bulk_add(User, names):
    try:
        for name in names:
            db.session.add(User(name=name))
        # One commit saves every new user together.
        db.session.commit()
        print(f"Added {len(names)} users in one transaction")
    except Exception as error:
        # If any insert fails, NONE of them are saved.
        db.session.rollback()
        print("Bulk insert rolled back:", error)
        raise

print("Bulk insert is atomic: all rows save, or none do")

❓ Frequently Asked Questions

Lesson complete — and you've finished the Flask course!

You can now wrap related writes in atomic transactions, recover safely with rollback(), and make reads fast with indexes, eager loading, smarter counting, and pagination. That is the same data discipline production apps rely on.

🏁 Congratulations — you've completed the Flask course! From your first route to a fully documented, transaction-safe, optimized application, you've built real backend skills. Keep building: ship a project, read the official docs, and make it your own.

Practice quiz

Continue this course

• Previous: API Docs with OpenAPI & Swagger
• Quick reference: Flask cheat sheet

Frequently asked questions

What is a database transaction?

A transaction is a group of database operations that succeed or fail together. With SQLAlchemy, you make changes in db.session and call db.session.commit() to save them all at once. If anything goes wrong you call db.session.rollback() to undo everything, leaving the data consistent.

What is the unit-of-work pattern?

The SQLAlchemy session collects all your adds, updates, and deletes and tracks them as a single unit of work. Nothing is written until you commit, at which point the session sends the changes as one atomic transaction. This keeps related changes consistent.

What is the N+1 query problem and how do I fix it?

N+1 happens when you load N rows and then run one extra query per row to fetch related data, which is slow. You fix it by eagerly loading the related data in advance with joinedload or selectinload, so the database returns everything in far fewer queries.

How do indexes speed up queries?

An index is a sorted lookup structure on a column. Without one, the database scans every row to find matches; with one, it jumps straight to the matching rows. Add indexes to columns you filter or join on often, and use EXPLAIN to confirm the query uses them.