A database transaction is a sequence of one or more SQL operations treated as a single, indivisible unit of work. Transactions ensure that your database remains accurate and consistent even when errors occur or multiple users access data simultaneously.
A transaction groups multiple read and write operations so they either all succeed or all fail together. For example, transferring money between two bank accounts requires both a debit and a credit — if one fails, neither should persist. You start a transaction with BEGIN, finalize it with COMMIT, or undo it with ROLLBACK. Most modern relational databases like PostgreSQL, MySQL, and SQL Server support transactions natively.
Transactions are defined by four guarantees known as ACID: Atomicity (all-or-nothing execution), Consistency (data always moves from one valid state to another), Isolation (concurrent transactions do not interfere with each other), and Durability (committed data survives crashes). These properties together prevent data corruption in multi-user, high-availability systems. Understanding ACID helps you choose the right database and configuration for your workload.
Databases use a Write-Ahead Log (WAL) to record every change before it is applied to the actual data pages. On COMMIT, the log entry is flushed to disk, making the change durable even if the server crashes moments later. On ROLLBACK or a crash before COMMIT, the database replays the log to restore the previous state. This mechanism is why databases can recover cleanly after unexpected failures.
SQL defines four isolation levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — that trade performance for protection against concurrency anomalies. Read Committed (the PostgreSQL default) prevents dirty reads but allows non-repeatable reads, while Serializable prevents all anomalies at the cost of throughput. Choosing the wrong isolation level can cause subtle bugs like phantom reads or lost updates in high-concurrency applications. Always match the isolation level to the actual consistency requirements of each use case.
A savepoint lets you mark a point within a transaction so you can partially roll back to it without aborting the entire transaction. This is useful for complex workflows where one step fails but prior steps should be retained. Syntax is SAVEPOINT my_point, ROLLBACK TO SAVEPOINT my_point, and RELEASE SAVEPOINT my_point. Not all databases support true nested transactions, so savepoints are often the practical substitute.
Holding a transaction open for a long time locks rows or pages, blocking other queries and degrading throughput significantly. Long transactions also cause the WAL or undo log to grow, consuming disk space and slowing replication. Always keep transactions as short as possible — move application logic and external API calls outside the transaction boundary. Monitor for idle-in-transaction sessions and set statement_timeout or lock_timeout guards in production environments.
© RM Full Stack & AI Engineer · All guides · Roadmaps · Open the app