Part 1 of this series covered joins. Part 2 covered indexing. This part covers the territory that separates "I can write a query" from "I understand what happens when multiple transactions hit the same data at the same time" - stored procedures, views, ACID properties, and transaction isolation levels.
Stored Procedures: Precompiled SQL, Stored in the Database Itself
A stored procedure is a named, precompiled block of SQL stored inside the database, called by name instead of sending the full SQL text from application code every time.
-- Defining a stored procedure
CREATE PROCEDURE GetActiveCustomers
@MinOrderCount INT
AS
BEGIN
SELECT c.Id, c.Name, COUNT(o.Id) AS OrderCount
FROM Customers c
JOIN Orders o ON c.Id = o.CustomerId
GROUP BY c.Id, c.Name
HAVING COUNT(o.Id) >= @MinOrderCount;
END;
-- Calling it - the application sends this short
-- call instead of the full query text every time
EXEC GetActiveCustomers @MinOrderCount = 3;
The real benefit is that the database can cache and reuse a precompiled execution plan for the procedure, avoiding the overhead of re-parsing and re-planning the same query structure repeatedly. It also centralizes a piece of logic in one place if multiple applications need the exact same query.
The real cost, stated honestly: business logic now lives in two separate places, application code and the database itself, which makes it harder to track down where a piece of logic actually lives, harder to unit test compared to application-layer code, and adds real deployment friction, since a stored procedure change is a database migration, not just an application code change that ships through the normal build pipeline.
Views: A Saved Query That Looks Like a Table
A view is a named, saved SELECT query that can be queried as if it were a table, without actually storing any data of its own.
-- Defining a view
CREATE VIEW ActiveCustomersView AS
SELECT c.Id, c.Name, COUNT(o.Id) AS OrderCount
FROM Customers c
JOIN Orders o ON c.Id = o.CustomerId
GROUP BY c.Id, c.Name
HAVING COUNT(o.Id) >= 3;
-- Querying it looks exactly like querying a table
SELECT * FROM ActiveCustomersView
WHERE Name LIKE 'A%';
-- But underneath, the ENTIRE original query runs
-- again every single time this view is queried - a
-- view is not stored data, it's a stored QUERY
Think of a saved search on a shopping site. The saved search isn't a separate copy of matching products sitting somewhere - it's a remembered set of filters that re-runs against the live catalog every time it's opened. A view works the same way against a database.
Views are used for simplifying a complex, frequently-repeated query into something readable and reusable, and for hiding underlying table structure from application code that doesn't need to know about it. This is a genuine readability and maintenance win, not a performance optimization on its own.
Materialized Views: The Same Idea, But the Result Is Actually Stored
A materialized view is a view where the query's result is physically stored, not recalculated on every read, and refreshed on some schedule or trigger rather than live every time.
-- Materialized view syntax varies by database engine -
-- this shows the PostgreSQL form
CREATE MATERIALIZED VIEW ActiveCustomersSummary AS
SELECT c.Id, c.Name, COUNT(o.Id) AS OrderCount
FROM Customers c
JOIN Orders o ON c.Id = o.CustomerId
GROUP BY c.Id, c.Name
HAVING COUNT(o.Id) >= 3;
-- Reading it is now fast - genuinely stored data,
-- not a recalculated query
SELECT * FROM ActiveCustomersSummary;
-- But it does NOT automatically stay current -
-- someone has to refresh it
REFRESH MATERIALIZED VIEW ActiveCustomersSummary;
The tradeoff is genuinely fast reads at the cost of staleness between refreshes - the exact same fundamental tradeoff as caching, covered in an earlier System Design post on this blog. A materialized view is, conceptually, a cache implemented at the database layer specifically for one query's result.
ACID: The Four Guarantees a Real Transaction Makes
Atomicity means all operations in a transaction succeed together, or none of them apply at all - there's no half-finished state left behind. Consistency means a transaction can only move the database from one valid state to another valid state, never leaving constraints, foreign keys, or rules violated.
Isolation means concurrent transactions don't see each other's incomplete, in-progress work.
Durability means once a transaction is committed, it survives a crash immediately afterward - it's genuinely saved, not just held in memory.
-- A concrete example of Atomicity: a bank transfer
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100
WHERE AccountId = 1;
UPDATE Accounts SET Balance = Balance + 100
WHERE AccountId = 2;
COMMIT;
-- If the SECOND update failed for any reason
-- (constraint violation, crash, disconnect),
-- Atomicity guarantees the FIRST update is rolled
-- back too - the money never just vanishes from
-- account 1 without appearing in account 2
Transaction Isolation Levels: How Much Concurrent Transactions Can See of Each Other
Isolation levels are a dial between strict correctness and concurrency performance. Looser levels allow more simultaneous activity but permit specific, named anomalies. Stricter levels prevent those anomalies but require more locking, reducing how much can genuinely happen at the same time.
Read Uncommitted, the loosest level, can read another transaction's uncommitted changes. This allows a dirty read: one transaction updates a balance without committing, a second transaction reads that uncommitted value, and then the first transaction rolls back - meaning the second transaction read a value that never actually happened.
Read Committed only reads data that has actually been committed, eliminating dirty reads. It still allows a non-repeatable read: a transaction reads a row, a second transaction updates and commits a change to that same row, and when the first transaction reads the same row again within the same transaction, it gets a different value the second time.
Repeatable Read guarantees the same row read twice within one transaction returns the same value both times. It still allows a phantom read: a transaction counts rows matching a condition, a second transaction inserts a new row matching that same condition and commits, and when the first transaction runs the same count again, a "phantom" row appears that wasn't there moments ago.
Serializable, the strictest level, makes transactions behave as if they ran one at a time, completely sequentially - no dirty reads, no non-repeatable reads, no phantom reads. The cost is the most locking and the least real concurrency: genuinely correct, genuinely slower under contention.
Deadlocks: Two Transactions Waiting on Each Other Forever
A deadlock occurs when Transaction A holds a lock that Transaction B needs, while Transaction B simultaneously holds a lock that Transaction A needs. Neither can proceed, and neither will ever release its lock voluntarily, because each is waiting on the other.
The database engine detects this circular wait and picks one transaction to forcibly roll back, commonly called the deadlock victim, freeing its locks so the other transaction can proceed. The rolled-back transaction's application code typically needs to catch this specific error and decide whether to retry.
Deadlocks are a real, recurring production issue, not just an academic concept - they show up specifically when multiple transactions update the same set of rows in a different order from each other. A common, practical mitigation is ensuring application code always acquires locks on multiple rows in a consistent order across every code path, which prevents the circular wait from forming in the first place.
Key Lessons
Stored procedures gain a cached execution plan and centralized logic, at the cost of splitting business logic across two places and adding deployment friction.
A view is a saved query, not saved data - it re-runs the underlying query every time, and its main value is readability and abstraction, not performance.
A materialized view actually stores its result, trading staleness for speed, the same fundamental tradeoff as caching, implemented at the database layer.
ACID's four guarantees - Atomicity, Consistency, Isolation, Durability - are what make a transaction mean something more than just several statements run near each other.
Each stricter isolation level closes one specific, named gap the level below it leaves open - dirty reads, non-repeatable reads, and phantom reads each have a precise definition and a specific isolation level that prevents them.
Deadlocks are resolved automatically by the database killing one transaction, but the real fix is consistent lock ordering in application code to prevent the circular wait in the first place.
Where This Series Stands So Far
Part 1 covered joins - Inner, Left, Right, Full Outer, Cross, Self. Part 2 covered indexing - clustered, non-clustered, composite, and covering indexes, plus the honest write-cost tradeoff. Part 3 (this post) covered stored procedures, views, ACID, isolation levels, and deadlocks. Part 4 will cover normalization and schema design - 1NF through 3NF, and when denormalizing is actually the right call.
Summary
Stored procedures and views both package SQL for reuse, but in different ways - a stored procedure is precompiled and callable, a view is a saved query that looks like a table, and a materialized view actually stores its result at the cost of potential staleness. ACID defines what a real transaction guarantees. Isolation levels are the dial between strict correctness and concurrency, each stricter level closing one more specific gap. Deadlocks are the real, recurring cost of concurrent transactions competing for the same locked resources, resolved automatically but best prevented through consistent lock ordering. Together with joins and indexing, this rounds out three-quarters of the database knowledge that shows up constantly in both interviews and real production incidents - normalization completes the set in Part 4.
Originally published at TechStack Blog: https://www.techstackblog.com/post.html?slug=database-stored-procedures-transactions
Part 1 of this series (Joins): https://www.techstackblog.com/post.html?slug=database-joins-explained
Part 2 of this series (Indexing): https://www.techstackblog.com/post.html?slug=database-indexing-explained
More from TechStack Blog: Database: https://www.techstackblog.com/category.html?cat=database
CS Fundamentals: https://www.techstackblog.com/category.html?cat=cs-fundamentals
Top comments (0)