A commit is the operation that successfully ends a database transaction and makes every change made within it durable and visible to other sessions as one indivisible unit. In transactions, it is the counterpart of rollback: rollback discards the work, while commit confirms it.
In SQL the operation is issued with the COMMIT statement or happens implicitly in autocommit mode. Once the database acknowledges a commit, it guarantees that the changes survive a crash or power loss, which is the durability promise in ACID.
What happens inside the database at commit
A commit is cheap in terms of data movement but strict in terms of ordering. Modified data pages usually stay in memory; what must reach stable storage is the transaction log record. This principle, write-ahead logging (WAL), means the database can always replay committed work from the log even if the data pages were never written.
- The engine writes a commit record for the transaction into the log buffer.
- The log buffer is flushed to disk with an fsync or equivalent call, so the record is durable.
- The transaction is marked committed, which makes its row versions visible to new snapshots under MVCC.
- Locks held by the transaction are released, allowing waiting transactions to proceed.
- The client receives the acknowledgment; dirty data pages are written later by checkpoints.
Because the flush in step two is the expensive part, engines use group commit: several transactions that finish at nearly the same moment share one disk flush. This raises throughput sharply on busy systems without weakening durability.
Commit modes and durability settings
Databases let administrators trade durability against latency by changing when the commit record is flushed. These settings do not affect atomicity, but they decide how much recently committed work can be lost if the server crashes.
| Setting | System | Behavior at commit | Risk on crash |
|---|---|---|---|
| synchronous_commit = on | PostgreSQL | Waits for local WAL flush | No committed data lost |
| synchronous_commit = off | PostgreSQL | Returns before WAL flush | Last fraction of a second lost |
| innodb_flush_log_at_trx_commit = 1 | MySQL, MariaDB | Flushes redo log every commit | No committed data lost |
| innodb_flush_log_at_trx_commit = 2 | MySQL, MariaDB | Writes to OS cache, flushes about once per second | Up to about one second on OS crash |
| DELAYED_DURABILITY | SQL Server | Log flushed asynchronously | Recent transactions may be lost |
Relaxed settings never corrupt the database; they only allow the most recent commits to disappear after a crash. That is acceptable for logs or caches but unacceptable for payments or orders, where the application has already told a user the action succeeded.
Autocommit, explicit transactions and implicit commits
Most client libraries start in autocommit mode, where every statement is its own transaction and commits immediately. Grouping several statements requires an explicit BEGIN or START TRANSACTION, followed by COMMIT or ROLLBACK.
| Mechanism | How it is triggered | Effect |
|---|---|---|
| Explicit commit | COMMIT statement or driver call | All changes made permanent |
| Autocommit | Each statement outside a block | One statement per transaction |
| Implicit commit | DDL such as ALTER TABLE in MySQL | Pending work committed without request |
| Rollback | ROLLBACK statement or error handling | All changes discarded |
| Abort on failure | Deadlock, crash or lost connection | Uncommitted work rolled back automatically |
Implicit commits are a frequent surprise. In MySQL and MariaDB, most DDL statements commit the open transaction, while PostgreSQL supports transactional DDL that can be rolled back. Code that mixes schema changes and data changes should be written with that difference in mind.
Commit in distributed and replicated systems
When one transaction spans several databases, a local commit is not enough. Two-phase commit (2PC) first asks every participant to prepare, guaranteeing it can commit, and only then sends the final commit. If the coordinator fails between the phases, participants may stay in doubt with locks held until it recovers.
In replicated systems the question is where a commit is durable. Asynchronous replication acknowledges the commit on the primary alone, semi-synchronous replication waits until a replica has received the change, and synchronous replication waits until a replica has stored it. Each step adds round-trip latency but reduces data loss on failover.
Commit frequency and common mistakes
How often an application commits has a large effect on both performance and correctness. The table pairs typical symptoms with their causes and remedies.
| Symptom | Cause | Fix |
|---|---|---|
| Slow bulk load | Commit after every single row | Commit in batches of 1,000-10,000 rows |
| Lock waits and deadlocks | Very long transactions | Keep transactions short, commit sooner |
| Growing undo or table bloat | Idle session holding an open transaction | Set idle transaction timeouts |
| Partial data after an error | Autocommit used for multi-step logic | Wrap related statements in one transaction |
| Lost confirmed orders after crash | Relaxed flush settings | Use full durability for critical data |
Explained without jargon
Think of filling in a bank transfer form. You can write, correct and cross out as much as you like, but nothing happens until you press the final confirm button. A commit is that button for a database.
Until the commit, the changes are a private draft that other people cannot see and that can be thrown away. After the commit, they are saved for good and visible to everyone, even if the power fails a second later. All the changes count together, or none of them do.
A typical use case
An online store processes an order in three steps: it inserts the order row, reduces the stock count for each item, and records the payment authorization. The developers wrap the three statements in one transaction and issue a single COMMIT only after all three succeed. If the stock update fails because an item sold out, the code issues ROLLBACK and no partial order remains.
During a sales event, the store handles hundreds of orders per second. The database’s group commit combines many of these commits into shared log flushes, so the server keeps full durability with innodb_flush_log_at_trx_commit = 1 while still meeting its latency target. The outcome is that every order the customer saw confirmed survives any later crash.