COMMIT permanently saves changes made during a transaction, while ROLLBACK undoes them. A transaction is a sequence of SQL operations treated as a single unit to ensure data integrity, following ACID properties. For example, in a money transfer, if one account is debited but the other isn't credited, the transaction ensures no partial updates occur. When all operations succeed, COMMIT makes the changes permanent and visible to others; once committed, changes can’t be undone without reversal. If an error occurs or changes shouldn’t persist, ROLLBACK reverts everything to the state before the transaction began. Common rollback scenarios include constraint violations, application errors, testing, or unexpected disconnections. To use COMMIT and ROLLBACK effectively: 1) wrap related operations in transactions; 2) test carefully in development; 3) avoid long-running transactions to prevent resource locking; 4) understand auto-commit settings; and 5) use savepoints for partial rollbacks when supported. Proper use of these commands ensures reliable data handling in database applications.

When you're working with databases, especially in applications that require reliable data handling, COMMIT and ROLLBACK are two essential commands that help manage transactions. They determine whether changes made during a transaction should be saved or discarded.

In simple terms:

• COMMIT saves the changes permanently to the database.
• ROLLBACK undoes the changes made during the current transaction.

What is a Transaction?

Before diving into COMMIT and ROLLBACK, it helps to understand what a transaction is. A transaction is a sequence of one or more SQL operations treated as a single unit of work. Either all of them succeed, or none do — this is part of the ACID properties (Atomicity, Consistency, Isolation, Durability) that databases follow.

For example, imagine transferring money from one bank account to another:

1. Deduct money from Account A
2. Add money to Account B

If something goes wrong after step 1 but before step 2, you don’t want the deduction to stick. That’s where transactions come in handy.

How Does COMMIT Work?

Once you've executed your SQL statements inside a transaction block and everything looks good, you use COMMIT to make those changes permanent.

Here's a basic example using SQL:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance   100 WHERE account_id = 2;
COMMIT;

After the COMMIT runs, both updates become visible to other users and are stored permanently in the database.

Key points:

• Once committed, the changes can't be undone unless explicitly reversed.
• Other sessions will now see the updated data.
• Some databases auto-commit by default, meaning each statement is treated as its own transaction unless told otherwise.

When to Use ROLLBACK

If at any point during the transaction an error occurs or you decide not to proceed, ROLLBACK lets you undo everything since the start of the transaction.

Example:

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Oops! Something went wrong
ROLLBACK;

Now, no changes were applied — Account A still has its original balance.

Common scenarios for rollback:

• Detecting constraint violations (e.g., negative balance)
• Handling application-level errors
• Testing without affecting real data

Rollbacks are also automatic if a session disconnects unexpectedly or a system crash happens before committing.

Tips for Using COMMIT and ROLLBACK Effectively

• Always wrap related operations in a transaction when data integrity matters.
• Test logic carefully in development environments before running transactions on production data.
• Avoid holding transactions open too long — they can lock resources and impact performance.
• Be aware of your database's auto-commit behavior (you can usually toggle it off).
• Use savepoints (SAVEPOINT) if your DB supports them — they let you roll back part of a transaction.

So, to recap:
You use COMMIT when you're sure the changes should take effect, and ROLLBACK when something went wrong or you're not ready to apply the changes yet.
They’re not complicated, but understanding how and when to use them makes a big difference in building robust database applications.

The above is the detailed content of How do COMMIT and ROLLBACK work?. For more information, please follow other related articles on the PHP Chinese website!