COMMIT

Hook: Think of COMMIT like signing the receipt at checkout: until you sign, the store can still undo the cart.

Question: What does COMMIT do in SQL?

Answer: COMMIT ends the current transaction and makes all changes in that transaction permanent. After it succeeds, the changes are visible to other sessions, and the database can release the locks that the transaction was holding. If something goes wrong before the commit, you usually use ROLLBACK instead.

Interview-Ready Answer: I use COMMIT to finalize a transaction. It tells the database, “make everything I did since BEGIN permanent and visible.” The key idea is all-or-nothing: if the work is correct, I commit; if it fails or I need to undo it, I roll back. One detail interviewers like is that committing too often can hurt performance because the database may need to flush transaction logs to durable storage.

What COMMIT really means

A transaction is a group of SQL statements that should succeed or fail together. COMMIT is the final step that says, “this group is complete.” A good mental model is a shopping basket: until you pay, the items are still in the cart; after payment, the store records them as final.

1. The database starts a transaction, either because you ran BEGIN or because your client is in manual transaction mode.
2. You run INSERT, UPDATE, and DELETE statements. The database tracks the changes as part of that transaction, but they are not yet final.
3. You run COMMIT. The database checks that the transaction is still valid and can be completed.
4. The database writes the transaction outcome to its durable log. In many systems, this means a log flush to disk so the change survives a crash. This durability step is what makes commit meaningful.
5. The transaction ends, locks are released, and other sessions can now see the committed data according to the isolation level.

When and why to use it

Use COMMIT when you want a set of related changes to become permanent together. Classic examples are money transfers, order placement, booking inventory, or saving a parent row and its child rows. If the steps depend on each other, one commit at the end protects you from half-finished data.

Do not think of commit as something you sprinkle after every statement. If five statements are really one business action, committing after each one can leave the system in an impossible intermediate state if a later statement fails.

COMMIT vs alternatives

If your database or client is in autocommit mode, each statement is committed immediately unless you explicitly start a transaction. That is convenient for simple scripts, but it is risky for multi-step business logic because you lose the safety of grouping statements together.

How it works under the hood

Under the hood, most databases use a transaction log. A transaction log is a durable record of changes that lets the database recover after a crash. When you commit, the database ensures the log is safely written before it tells you the transaction is final. That extra safety is why commit is not free.

For a rough performance model, the logical cost of committing is O(1) per transaction, but the physical cost depends on disk flushes, replication, and lock release. On a fast SSD-backed system, a commit may take fractions of a millisecond to a few milliseconds; in a busy replicated system, it can be longer. This is why batching 100 row changes into one commit is often much faster than doing 100 separate commits. A common real-world rule of thumb is that committing every row can be 10x to 100x slower than batching, depending on storage and workload.

One more important term: MVCC means Multi-Version Concurrency Control. It is a technique where the database keeps multiple row versions so readers and writers can work at the same time. In MVCC systems like PostgreSQL, committed changes become visible to new transactions, and old versions are cleaned up later.

Important edge cases

1. Aborted transaction: If a statement fails, some databases mark the whole transaction as failed. In PostgreSQL, you usually must run ROLLBACK before you can do anything else. A later COMMIT will not rescue a broken transaction.
2. Savepoints: A SAVEPOINT is a named checkpoint inside a transaction. You can roll back part of the work and still commit the rest. This is useful when one small step is optional.
3. Isolation effects: In stricter isolation levels, commit can also be the point where the database detects a serialization conflict. That means the commit itself may fail and force a retry.
4. Long transactions: A transaction that stays open too long holds locks and, in MVCC systems, keeps old row versions alive. That can slow down vacuuming, increase storage use, and block other sessions.

Memory rule: if the work is one business event, commit once at the end; if the work is not safe to keep, roll it back.

Real-world story

Imagine a checkout service for an online store. When a customer clicks “Place order,” the app must create the order row, reserve inventory, and record payment intent. Those steps belong to one transaction. The service does the work, then COMMIT makes the whole order official.

What goes wrong when someone misunderstands COMMIT? A common bug is committing too early, for example after inserting the order but before reserving stock. In that case the database can show a paid order with no inventory reserved, which causes support tickets, duplicate shipments, and angry customers. Another failure mode is forgetting to commit at all in a manually managed session: the app may show success to the user, but the data disappears when the connection closes or the transaction is rolled back.

Symptoms usually look like this: order confirmations appear in the UI, but later queries cannot find matching rows; logs show long-running sessions with messages like “idle in transaction”; and the warehouse team sees stock levels drifting because reservations never finalized. In production, this is one of the fastest ways to get a “ghost order” outage.

Follow-up & Tricky Questions:

• What is the difference between COMMIT and ROLLBACK? COMMIT makes the transaction permanent; ROLLBACK discards its changes. They are the two opposite outcomes of transaction control.
• What happens if a statement fails before commit? In many databases the transaction becomes unusable until you roll it back. The important point is that a failed transaction should never be silently committed.
• What is autocommit mode? It means each statement is its own transaction unless you explicitly open one. It is convenient for simple queries, but dangerous when several statements must succeed together.
• Why can commit be slow? Because the database may need to flush logs to durable storage and release locks. That durability guarantee is the price you pay for safety after crashes.
• What is a savepoint and how does it relate to commit? A savepoint is an in-transaction checkpoint. You can roll back part of the work to the savepoint and still commit the remaining changes.
• Tricky: Does COMMIT always make data visible immediately? Usually yes for transactions that become committed, but visibility still depends on isolation level and whether other sessions start after the commit. Existing transactions may continue to see their own snapshot.
• Tricky: Can you commit a transaction after an error? Not safely in most systems. For example, PostgreSQL requires a rollback after a transaction error before you can start clean work again.
• Tricky: If I close the connection, is that the same as commit? No. If the transaction was not committed, many databases will roll it back when the session ends.

Common Mistakes:

• Committing too early: Fix it by grouping all dependent statements into one transaction, then commit once at the end.
• Forgetting to commit: Fix it by making transaction boundaries explicit in your code or script so work is not lost on disconnect.
• Leaving long transactions open: Fix it by keeping transactions short; do not hold them open while waiting on user input or remote services.
• Assuming commit is free: Fix it by remembering that durability usually means a log flush, so batching work often improves throughput.

Memory Hook: “Commit is the cashier’s final receipt.” Until the receipt prints, the basket can still change; once it prints, the sale is final.

Cheat Sheet:

• COMMIT finalizes a transaction.
• It makes all successful changes permanent and visible.
• Use it after a complete business action, not after each small statement.
• ROLLBACK is the undo button.
• Autocommit means each statement commits by itself.
• Long transactions hurt performance and concurrency.

Practice Tasks:

• Write a two-statement transfer between two rows, then commit it.
• Repeat the same transfer, but roll back instead of committing and confirm the rows stay unchanged.
• Add a savepoint, make a small update, roll back to the savepoint, and commit the rest.