SQL COMMIT

What is the purpose of the SQL COMMIT statement?

Permanently saves all changes made in the current transaction.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL COMMIT:

SQL COMMIT Full Explanation

COMMIT ends the current database transaction and makes all data modifications performed during the transaction permanent and visible to other sessions. After a successful COMMIT, locks acquired by the transaction are released, and the transaction context is cleared. If the session is in autocommit mode, each individual statement is implicitly committed and an explicit COMMIT has no effect.Key behaviors:- Durability: Once committed, changes survive crashes and restarts.- Visibility: Other connections can see the committed data immediately.- Savepoints: COMMIT releases all savepoints defined in the transaction.- Read only transactions: A COMMIT still finalizes the transaction even if no data was modified.Caveats:- Cannot COMMIT while inside an aborted transaction; issue ROLLBACK instead.- Long-running transactions that wait before COMMIT can hold locks and block other users.- Some databases automatically commit DDL statements, so COMMIT may be redundant after certain operations.

SQL COMMIT Syntax

-- Explicit transaction
BEGIN;
-- statements
COMMIT;

SQL COMMIT Parameters

Example Queries Using SQL COMMIT

-- 1. Update salary and commit
BEGIN;
UPDATE employees SET salary = salary * 1.05 WHERE dept_id = 3;
COMMIT;

-- 2. Using savepoint and commit
BEGIN;
INSERT INTO orders(customer_id,total) VALUES (42, 199.99);
SAVEPOINT after_order;
INSERT INTO payments(order_id, amount) VALUES (currval('orders_id_seq'), 199.99);
COMMIT;

Expected Output Using SQL COMMIT

All changes executed since BEGIN are permanently written to the database
Locks are released and the next statement starts a new implicit transaction (in most systems)

Use Cases with SQL COMMIT

Finalizing a batch of related DML statements as an atomic unit.
Ensuring data integrity after complex inserts or updates.
Ending a read only transaction to release shared locks.
Explicitly controlling transaction boundaries in scripts or stored procedures.

Common Mistakes with SQL COMMIT

Issuing COMMIT in autocommit mode and assuming it controls rollback behavior.
Forgetting to COMMIT, leaving the transaction open and locking tables.
Trying to COMMIT after an error without first handling or rolling back.
Assuming COMMIT undoes changes; that is the role of ROLLBACK.

First Introduced In

SQL-92

Frequently Asked Questions

What happens if I forget to run COMMIT?

The transaction remains open. Locks stay in place, other sessions cannot see your changes, and eventually the database may abort the transaction if the session disconnects.

Does COMMIT work the same for DDL and DML?

Most databases implicitly commit DDL statements before and after they run. DML changes (INSERT, UPDATE, DELETE) rely on explicit COMMIT when not in autocommit mode.

Can I COMMIT part of a transaction?

Yes. Use SAVEPOINT to mark a point, then ROLLBACK TO SAVEPOINT for partial rollback before issuing a final COMMIT.

Is COMMIT slower than ROLLBACK?

Usually no. COMMIT writes changes to disk which can be costlier than discarding them with ROLLBACK, but the difference is minimal for small transactions.