If you’ve spent any time working with databases, you’ve probably noticed that most of your SQL statements just work. You run an INSERT, UPDATE, or DELETE, and the changes happen. You don’t need to do anything special to make them stick. Your changes were committed automatically as soon as you ran the statement. No need for a separate COMMIT keyword.
But then there are other cases where you need to explicitly use a COMMIT keyword.
So why is COMMIT required in some cases and not in others?
The truth is, commits are happening whether you see them or not. When you execute a statement and it works, there’s almost always a commit involved. You just might not be typing it yourself. Understanding what’s actually happening behind the scenes will help you write better database code and avoid errors.
Understanding Commit Modes
Different DBMSs and configurations handle transactions in different ways. The “commit mode” determines what happens automatically versus what you need to control explicitly. Understanding these modes will help clarify when and why you need to use COMMIT statements in your code, and when the database is handling commits for you behind the scenes.
Explicit Transactions
Explicit transactions are what most developers think of when they hear “transaction control”. This is when you deliberately mark the beginning and end of a transaction using statements like BEGIN TRANSACTION, COMMIT, and ROLLBACK.
When you start an explicit transaction, you’re taking full control. You decide exactly which statements belong together as a unit of work, and you decide when that work is complete and should be committed. The transaction boundaries are clear and visible in your code. For example:
BEGIN TRANSACTION;
INSERT INTO orders (customer_id, total) VALUES (123, 99.99);
INSERT INTO order_items (order_id, product_id) VALUES (1001, 456);
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 456;
COMMIT;Here, all three operations are grouped together as a single transaction. If any of them fails, they all fail, leaving the database unchanged.
In some database systems, you might see variations like COMMIT WORK, COMMIT TRANSACTION, COMMIT TRAN, but they all do the same thing.
Explicit transactions work regardless of your commit mode setting. Whether you have autocommit enabled or implicit transactions enabled, issuing a BEGIN TRANSACTION statement starts an explicit transaction that you control. The commit mode setting only affects what happens to statements that aren’t wrapped in an explicit transaction.
This is the most portable and clearest approach to transaction management. When someone reads your code, they can immediately see where transactions begin and end. There’s no ambiguity about whether a statement is auto-committing or leaving a transaction open. You’ve made the transaction boundaries explicit.
In application development, explicit transactions are the standard practice. You begin a transaction when you start a multi-step operation, execute your statements, handle any errors, and then commit or rollback based on whether everything succeeded. This gives you complete control over data consistency and error handling.
Autocommit Mode
Autocommit is the default behavior in most database systems and clients. When autocommit is enabled, every individual SQL statement is automatically (and invisibly) wrapped in its own transaction and committed immediately upon successful execution. You run an UPDATE, it commits. You run an INSERT, it commits. Each statement is its own complete transaction.
This is what most people experience when they first start working with databases. You don’t think about transactions or commits. You just run statements and they take effect. The database handles all the transaction management behind the scenes.
The main thing to understand is that autocommit doesn’t prevent you from using explicit transactions. If you issue a BEGIN TRANSACTION or START TRANSACTION statement, you’re explicitly starting a transaction, and autocommit steps aside until that transaction completes with either a COMMIT or ROLLBACK. Once the explicit transaction ends, autocommit resumes its normal behavior for subsequent statements.
The autocommit behavior is typically determined by a combination of factors:
- Server-level defaults – The database server may have a default autocommit setting that applies to new sessions unless overridden. This allows you to set a server-level default for autocommit behavior that applies to new sessions.
- Session-level settings – Individual sessions can override the server default for their own connection (e.g.,
SET autocommit = 0in MySQL) - Client/driver defaults – Database drivers and tools often have their own autocommit settings that they apply when establishing a connection
- Application code – Your application can explicitly set autocommit on or off for a connection
So it’s a layered system. The client might set autocommit when it connects, which affects the session on the server side. Or the server might have session-level defaults that apply unless the client overrides them.
When you run a command, the system checks the active settings based on the above. So it might look like something this:
- Is there an active transaction? If a
BEGINorSTART TRANSACTIONhas been issued, autocommit is effectively disabled until that transaction is committed or rolled back. - What is the session’s autocommit setting? This is the immediate setting for your current connection, which can be explicitly changed with
SET autocommit(depending on your DBMS). - What is the client’s default? If the session setting has not been changed, the client library or tool’s default is used.
- What is the server’s default? If none of the above has been explicitly set, the connection uses the server’s configured default.
Implicit Transactions Mode
Implicit transactions mode works differently. This is mainly a SQL Server concept (controlled by the SET IMPLICIT_TRANSACTIONS ON setting), though the underlying behavior exists in other databases under different names.
When implicit transactions mode is enabled, the database automatically starts a transaction before certain types of statements, but it doesn’t automatically commit that transaction. You must explicitly issue a COMMIT (or ROLLBACK) to end the transaction. After you commit, the next statement that requires a transaction will automatically start a new one.
The “implicit” part refers to the transaction starting automatically, so you don’t need to type BEGIN TRANSACTION. But the commit is still explicit, and so you do need to type COMMIT.
In SQL Server with IMPLICIT_TRANSACTIONS ON, statements like SELECT, INSERT, UPDATE, DELETE, and others will automatically begin a transaction if one isn’t already active. That transaction remains open until you explicitly commit or roll it back.
Explicit vs Autocommit vs Implicit Transactions
So the basic difference between the various transaction/commit modes comes down to what happens automatically:
- Explicit: You explicitly specify exactly where each transaction starts and ends. You do this using statements like
BEGIN TRANSACTIONandCOMMIT. This allows you to override your commit mode and control your transaction completely. - Autocommit: Each statement automatically starts and commits its own transaction (unless you explicitly start a transaction with
BEGIN TRANSACTION). - Implicit transactions: Statements automatically start a transaction, but you must explicitly
COMMITorROLLBACKto end it.
This means the default behaviors are quite different. With autocommit, the default path is for changes to become permanent immediately. With implicit transactions, the default path is for changes to remain uncommitted until you explicitly finalize them.
Most developers working with SQL Server stick with autocommit mode (which is the default, meaning IMPLICIT_TRANSACTIONS OFF). When they need transaction control, they explicitly use BEGIN TRANSACTION and COMMIT. The implicit transactions mode exists largely for compatibility with other database systems that work this way by default, and it’s less commonly used in typical SQL Server development.
In databases like PostgreSQL, the behavior is closer to implicit transactions for interactive sessions. Each statement starts a transaction block automatically if you’re not already in one, but the transaction commits automatically at the end of the statement unless an error occurs. However, in application code, you typically use explicit BEGIN/COMMIT statements to control transaction boundaries.
The takeaway is that in most modern database work, you’re operating in autocommit mode by default. When you need to group operations together, you explicitly start a transaction with BEGIN TRANSACTION, do your work, and then explicitly COMMIT or ROLLBACK. The autocommit behavior resumes after your explicit transaction ends.
When to Use Explicit Transactions
While autocommit mode handles simple operations just fine, there are many situations where you need the control and safety that explicit transactions provide. Examples include:
- Multi-Step Operations: Anytime you need to execute multiple related statements that should succeed or fail as a unit, you want explicit transaction control. Financial transactions, inventory management, and user registration flows all benefit from wrapping related operations in a single transaction with an explicit commit at the end.
- Testing and Verification: Sometimes you want to make changes, examine the results, and then decide whether to keep them. With auto-commit off, you can run your statements, query the data to see what changed, and then either commit or roll back based on what you find. This can be very handy during development and debugging.
- Performance Optimization: If you’re inserting or updating large amounts of data, auto-committing after every single row can be painfully slow. Each commit involves writing to transaction logs and potentially flushing data to disk. By batching your operations and committing every few thousand rows instead of every row, you can dramatically improve performance.
- Application Logic: In application code, you typically want full control over transactions. Your application might need to execute queries, perform business logic checks, and then decide whether to commit based on the results. This level of control is only possible when you’re managing transactions explicitly.
COMMIT vs ROLLBACK
If COMMIT makes changes permanent, ROLLBACK does the opposite – it discards them. If an error occurs or a business rule check fails inside a transaction, you can execute ROLLBACK to undo everything since the transaction began. Here’s an overly simplistic example:
BEGIN TRANSACTION;
UPDATE products
SET price = price * 1.1;
-- Oops, I didn’t mean to update all products!
ROLLBACK;In this case, the UPDATE never happened as far as the database is concerned. That’s because we used ROLLBACK to undo everything.
But it’s important to remember that once you COMMIT, ROLLBACK is no longer an option. At that stage, the changes are permanent.
In practice, you’d use conditional logic to only trigger the rollback in the event something goes wrong. For example:
-- Begin the transaction with error handling
BEGIN TRY
BEGIN TRANSACTION;
-- Deduct from Nola
UPDATE accounts
SET balance = balance - 200
WHERE account_id = 1;
-- Add to Dylan
UPDATE accounts
SET balance = balance + 200
WHERE account_id = 2;
-- If both succeed, commit
COMMIT TRANSACTION;
PRINT 'Transaction committed successfully.';
END TRY
BEGIN CATCH
-- Something went wrong, rollback
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT 'Transaction failed. Rolling back changes.';
PRINT 'Error Message: ' + ERROR_MESSAGE();
END CATCH;Here, we used a TRY/CATCH block to identify any problems and rollback the transaction if required. We also used COMMIT TRANSACTION in the event everything went fine. The two were separated by the TRY/CATCH block, and so we would never have a situation where they both occur.
Transaction Isolation Levels
When you’re working with transactions and commits, there’s another factor at play in the form of isolation levels. These determine how your uncommitted changes interact with other database sessions.
Generally speaking, other users won’t see your changes until you commit them. If you update a row but haven’t committed yet, other sessions will still see the old value. This prevents them from seeing inconsistent or incomplete data. Once you commit, your changes become visible to everyone (depending on the specific isolation level in use).
Performance Considerations
Commits aren’t instantaneous. When you commit, the database needs to ensure your changes are durably saved, which typically involves writing to transaction logs and potentially syncing data to disk. This takes time.
For bulk operations, you need to find a balance. Committing after every single row is slow because of all that overhead. But making one giant transaction for millions of rows uses lots of memory and can lock resources for a long time. A common approach when working with lots of rows is to batch your commits – maybe commit every 1,000 or 10,000 rows depending on your specific situation.
Wrapping Up
Commits are happening all the time in your database work, whether you’re explicitly typing COMMIT or letting auto-commit handle it for you. For simple, one-off statements, auto-commit is convenient and works perfectly well. But when you need to group related operations together, ensure consistency across multiple steps, or have fine-grained control over when changes become permanent, explicit transaction management with manual commits is the way to go.
