Phase 2: APIs & Databases

Transactions, isolation levels & locking

Intermediate ~3 min read
Think of it this way A friendly analogy. Read this if the technical version feels dense. Show Hide

Imagine your local library, which uses computers to keep track of all its books and members. When you borrow a book, a few important things happen: the librarian finds your account, scans the book, and marks it as "checked out." It’s super important that all these steps succeed, or none of them happen at all. If the computer crashed right after marking the book "checked out" but before you got it, that would be a big problem! The system would think you have the book, but you don't. A "transaction" is like the library's firm rule: either every step for borrowing a book is completed perfectly, or if anything goes wrong, it’s like nothing ever started. This keeps the library's records completely accurate.

Now, think about many people using the library at the same time. Some are borrowing, some are returning, and others are just checking a book's availability. What if one librarian is in the middle of checking out a book, but hasn't quite finished? If another person quickly looks up that book, should they see it as "available" or "checked out"? Seeing a "half-finished" status could lead to mistakes. Or what if you look up a book, see it's there, and then a minute later, someone else has borrowed it? The library needs clear rules to make sure everyone sees reliable information, even when lots of things are happening simultaneously.

These "rules" are like different levels of strictness for how library users can see what others are doing in the system. Some rules are very strict: no one can see a book's status change until all the borrowing steps are completely done. To follow these rules, the library system uses something like a temporary "DO NOT DISTURB" sign on a book's record when a librarian is actively working. This "locks" that specific book for a moment, so no one else can change its status or even see it in a confusing, unfinished state until the first librarian is entirely finished. So, when building a system where many people do things at once, you pick the right "strictness level" to prevent mix-ups.

This means you can build a super reliable online store, a game with many players, or a school record system. Even if hundreds of people are doing things at the exact same moment, you'll know the information will always stay correct and consistent. You won't have two people accidentally buying the last item at the same time, or your game characters ending up with weird, impossible scores.

When you're building backend systems, data integrity is paramount. A transaction in a relational database is a sequence of operations performed as a single logical unit of work. Think of a bank transfer: debiting one account and crediting another must either both succeed or both fail. If only one succeeds, your data is corrupted. Transactions ensure this "all or nothing" principle, guaranteeing that your database transitions from one consistent state to another. This atomicity is one of the foundational ACID properties (Atomicity, Consistency, Isolation, Durability) that make relational databases reliable.

In a multi-user environment, many transactions often run concurrently. If not managed carefully, concurrent transactions can interfere with each other, leading to data inconsistencies like "dirty reads" (reading uncommitted data), "non-repeatable reads" (reading the same row twice and getting different values), or "phantom reads" (a query returns a different set of rows when executed multiple times). Isolation levels define how and when changes made by one transaction become visible to others. Common levels include READ COMMITTED (prevents dirty reads, default in PostgreSQL), REPEATABLE READ (prevents dirty and non-repeatable reads, default in MySQL), and SERIALIZABLE (the strongest level, prevents all concurrency issues but impacts performance most). Choosing an isolation level is a trade-off between strict data consistency and application performance.

Databases achieve these isolation levels primarily through locking. When a transaction reads or modifies data, the database might acquire locks on rows, pages, or even entire tables to prevent other transactions from concurrently modifying or reading that data in conflicting ways. For instance, an exclusive lock is typically held on a row being updated, preventing other transactions from updating or even reading that row until the first transaction commits. While necessary for data integrity, extensive locking can lead to performance bottlenecks and, in some cases, deadlocks – where two or more transactions are waiting indefinitely for each other to release locks. Understanding how locking works helps diagnose performance issues and design more efficient concurrent applications.

Key Takeaways

  • Transactions ensure multiple database operations complete entirely or not at all, maintaining data integrity.
  • Isolation levels control how concurrent transactions see each other's changes, mitigating issues like dirty reads.
  • Databases use various locking mechanisms to enforce isolation levels, which can impact performance and lead to deadlocks.
  • The default isolation level (e.g., PostgreSQL's READ COMMITTED, MySQL's REPEATABLE READ) is crucial to understand for your application.
  • Choose isolation levels carefully, balancing consistency requirements with performance needs.

Code Example

sql
-- Example of a transaction in PostgreSQL/MySQL
BEGIN; -- Start a transaction

-- Transfer money from account 1 to account 2
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

-- Simulate an error condition (uncomment to see ROLLBACK behavior)
-- SELECT 1/0; 

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- If both updates succeed, commit the changes
COMMIT;

-- If an error occurs, the database would automatically ROLLBACK, or you can do it manually
-- ROLLBACK;

-- Example of setting an isolation level for a transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Any subsequent operations within this transaction will use SERIALIZABLE isolation

How this code works

This code demonstrates a fundamental concept in relational databases: transactions. Its primary job is to ensure that a series of related database operations, like transferring money between accounts, either all succeed completely or all fail and revert, maintaining data integrity. The BEGIN statement initiates this atomic unit of work. Inside, two UPDATE statements modify balance values for different ids. The commented-out SELECT 1/0 line shows how an error would halt the transaction. If all updates complete without issue, COMMIT permanently saves these changes. Conversely, if an error occurs, the database automatically performs a ROLLBACK, undoing any partial changes, or ROLLBACK can be invoked manually.

A subtle but crucial point is the order of operations for SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. This statement configures the isolation behavior for the current transaction. If BEGIN has already been executed, the isolation level applies to the operations that follow within that transaction. SERIALIZABLE is chosen here to illustrate a strong isolation guarantee, ensuring that concurrent transactions will not interfere, making them appear as if they executed one after another. Without explicitly setting it, transactions often default to a less strict isolation level, which could allow different kinds of concurrency issues.

Resources

Related Articles

Hand-picked reads to go deeper on relational databases (postgresql / mysql).

Back to Backend Developer Roadmap