DB Transactions & Query Optimization
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.
Learn DB Transactions & Query Optimization in our free Flask course — a beginner-friendly interactive lesson with worked examples, a practice exercise and a…
Part of the free Flask course at LearnCodingFast — hands-on lessons with examples you run in your browser, 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.
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.
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).
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.
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.
Complete the safe write below. Replace each ___ so it commits on success and rolls back on failure.
A previous commit failed and you never rolled back. Always call db.session.rollback() in your except block so the session is usable again.
You are probably hitting N+1 or loading too many rows. Add joinedload / selectinload , index your filter columns, and paginate.
Insert several rows as a single atomic transaction that rolls back on any failure.
Lesson 23 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
Which method permanently saves the changes in a SQLAlchemy session?
- db.session.commit()
- db.session.flush()
- db.session.write()
- db.session.save()
Answer: db.session.commit(). db.session.commit() ends the transaction and writes all pending changes to the database.
What does db.session.rollback() do?
- Saves changes faster
- Closes the connection
- Undoes all uncommitted changes in the session
- Deletes the database
Answer: Undoes all uncommitted changes in the session. rollback() discards every pending change so the database is left as it was before the transaction.
What does 'atomic' mean for a transaction?
- It uses no memory
- All operations succeed together or none take effect
- It runs on atoms
- It is encrypted
Answer: All operations succeed together or none take effect. Atomicity means a transaction is all-or-nothing; partial changes are never persisted.
Where should you place db.session.rollback() to handle a failed write?
- In the import section
- Never call it
- Before the commit
- In an except block
Answer: In an except block. Wrap the commit in try/except and call rollback() in the except block so a failure leaves the data consistent.
The SQLAlchemy session implements which design pattern?
- Unit of Work
- Factory
- Singleton
- Observer
Answer: Unit of Work. The session tracks changes and writes them as one unit of work when you commit.
What is the N+1 query problem?
- A missing primary key
- Running one query per related row instead of loading them together
- Having too many tables
- A syntax error
Answer: Running one query per related row instead of loading them together. N+1 happens when you load N parent rows then fire one extra query per parent for its related data.
Which loader option eagerly loads related rows in one JOIN to avoid N+1?
- deferload
- noload
- lazyload
- joinedload
Answer: joinedload. joinedload (and selectinload) fetch related data eagerly so you avoid a query per row.
What does adding a database index primarily improve?
- Disk space usage
- Password security
- The speed of lookups and filters on a column
- Backup frequency
Answer: The speed of lookups and filters on a column. An index lets the database find matching rows quickly instead of scanning the whole table.
Which SQL keyword shows a query's execution plan?
- ANALYZE ONLY
- EXPLAIN
- DESCRIBE
- SHOW
Answer: EXPLAIN. EXPLAIN reveals how the database will run a query, including whether it uses an index or a full scan.
Why prefer query.count() over len(query.all()) for a total?
- count() asks the database to count without loading every row
- They are identical
- count() is deprecated
- len() is more accurate
Answer: count() asks the database to count without loading every row. count() issues a COUNT query, while len(query.all()) loads every row into memory just to count them.