What Is a Database Transaction? The Practical Guide for Developers Who Use Them Every Day

Most developers use database transactions every day without thinking about them. The SQL is BEGIN, COMMIT, ROLLBACK. The behavior is “all the operations succeed, or none of them do.” The reality is more nuanced. Transactions have isolation levels, lock modes, deadlock behaviors, and performance characteristics that affect every query you write. After fifteen years of working with databases (PostgreSQL, MySQL, SQLite, and a few exotic ones), the most common bugs I have seen are transaction bugs. The bugs are not exotic. The bugs are “I expected the data to be there, but it was not.” This is what a transaction actually is, what the isolation levels mean in practice, and the patterns that prevent the common bugs.

What a database transaction actually is

A transaction is a sequence of database operations that are treated as a single unit. The unit either succeeds completely or fails completely. The “completely” is the key. If you have five operations in a transaction and the third one fails, the first two are rolled back. The database is in the same state as before the transaction started. The classic example is a bank transfer. You want to debit account A and credit account B. If the credit fails, the debit must be rolled back. Otherwise the money vanishes. The transaction guarantees this. The guarantee is called “atomicity” (along with consistency, isolation, and durability, the ACID properties). The atomicity is the most important property. The atomicity is what makes transactions useful.

What the isolation levels actually mean

The isolation level determines what one transaction can see of another transaction’s uncommitted changes. There are four standard levels. The first is “read uncommitted.” A transaction can see uncommitted changes from other transactions. This is the most permissive level. This is also the most dangerous level (you can see data that might be rolled back). The second is “read committed.” A transaction can only see committed changes. This is the default in PostgreSQL and Oracle. This is the safest reasonable level. The third is “repeatable read.” A transaction sees the same data throughout the transaction, even if other transactions commit changes. This is the default in MySQL. This prevents the “I queried the data, another transaction changed it, I queried again, and the data is different” bug. The fourth is “serializable.” Transactions appear to run one at a time, in some order. This is the strictest level. This is the slowest. most applications is “read committed.” financial transactions is “serializable.” reporting queries is “repeatable read.” The choice depends on the use case.

What deadlocks actually look like

A deadlock happens when two transactions are waiting for each other to release a lock. Transaction A holds a lock on row 1 and is waiting for a lock on row 2. Transaction B holds a lock on row 2 and is waiting for a lock on row 1. Neither transaction can proceed. The database detects the deadlock and kills one of the transactions (the victim). The killed transaction gets an error. The application handles the error by retrying the transaction. The classic example is two bank transfers that touch the same accounts in opposite order. The mitigation is to always acquire locks in the same order (e.g., always lock account A before account B). The mitigation prevents the deadlock. The mitigation is the standard pattern. The pattern is well-documented. The pattern is not always followed. The bug is common. The bug is preventable.

What the performance trade-offs actually are

Transactions are not free. Each transaction requires the database to maintain locks, write to the transaction log, and coordinate with other transactions. The cost is real. The cost is usually small. The cost becomes significant when you have long-running transactions, transactions that touch many rows, or transactions that hold locks while waiting for user input. The mitigation is to keep transactions short. The mitigation is to not do user input inside a transaction. The mitigation is to acquire locks at the end of the transaction, not the beginning. The mitigation is to use the appropriate isolation level. most applications is short transactions, the default isolation level, and consistent lock ordering. The combination prevents the common bugs. The combination is not always followed. The bugs are common.

What the common transaction bugs actually look like

Four bugs I have seen in production. The first is the “lost update.” Two transactions read the same data, both update it, and one update is lost. The classic example is two users editing the same row in a web app. The first user reads the row (balance = 100). The second user reads the row (balance = 100). The first user updates the row to 90. The second user updates the row to 80. The first update is lost. The final balance is 80 instead of 70. The mitigation is to use SELECT ... FOR UPDATE to acquire a lock when reading the row, or to use optimistic locking with a version number. The second is the “phantom read.” A transaction reads a set of rows, another transaction inserts a new row that matches the query, and the first transaction reads again and sees the new row. The mitigation is to use the “serializable” isolation level. The third is the “dirty read.” A transaction reads uncommitted data from another transaction, the other transaction rolls back, and the first transaction has read data that never actually existed. The mitigation is to use the “read committed” isolation level or higher. The fourth is the “long transaction.” A transaction holds locks for minutes while waiting for user input. The mitigation is to not do user input inside a transaction. The bugs are common. The mitigations are well-known. The mitigations are not always followed. The follow-up is the bug.

What the right patterns actually are

Five patterns I follow in every database code I write. The first is “always acquire locks in the same order.” The order should be documented in a comment near the top of the file. The order should be enforced by code review. The order should never change without a migration plan. The second is “keep transactions short.” The transaction should contain only the database operations. No user input. No external API calls. No file I/O. The third is “use the appropriate isolation level.” The default is usually fine. The “serializable” level is for financial transactions. The fourth is “handle the deadlock error.” The application should retry the transaction. The retry should have a maximum number of attempts. The retry should have a backoff. The fifth is “test the failure modes.” Write tests that simulate deadlocks. Write tests that simulate constraint violations. Write tests that simulate network partitions. The tests catch the bugs before production does. The patterns are not exotic. The patterns are the standard. The patterns are not always followed. The follow-up is the bug.

Here is the developer checklist I use before merging transaction code:

• Are all locks acquired in the same order? Document the order in a comment near the top of the file
• Is the transaction short? No user input, no external API calls, no file I/O inside the transaction
• Is the isolation level appropriate? Use the default unless you have a specific reason to change it
• Is the deadlock error handled? The application should retry with a backoff and a maximum number of attempts
• Are the failure modes tested? Write tests that simulate deadlocks, constraint violations, and network partitions

What the tooling actually looks like

PostgreSQL has pg_stat_activity to see active transactions. MySQL has SHOW PROCESSLIST to see active queries. SQLite has PRAGMA lock_status to see lock state. The tools are different. The concepts are the same. Use to monitor active transactions in production. Use to alert on transactions that have been running for more than 60 seconds. Use to kill transactions that have been running for more than 5 minutes (the application should retry). The tools are available. The discipline to use them is the harder part. The discipline is worth it. The bugs that the tools prevent are the bugs that take down production.

What this means for the day-to-day developer

The transactions you write today will be the source of the bugs you debug next year. The mitigation is to write them carefully. The mitigation is to use the right isolation level. The mitigation is to keep them short. The mitigation is to handle the errors. The mitigation is to test the failure modes. The mitigations are well-known. The mitigations are not always followed. The follow-up is the bug. The recommendation is to learn the patterns. The recommendation is to apply the patterns. The recommendation is to review your transaction code with the patterns in mind. The recommendation is to set up monitoring. The recommendation is to handle the errors. The mitigations are the right answer. The mitigations are not optional. The mitigations are the difference between a system that works and a system that breaks under load.