MySQL COMMIT Statement

The `COMMIT` statement in MySQL is used to permanently save all changes made during the current transaction. It finalizes the transaction, making all modifications available to other users and sessions.

Usage

The `COMMIT` statement is typically used after a series of `INSERT`, `UPDATE`, or `DELETE` operations to ensure changes are saved to the database. It is essential in transaction management to ensure data integrity.

COMMIT;

This syntax confirms all changes in the current transaction are saved and cannot be rolled back. Note that `COMMIT` is applicable only in storage engines that support transactions, such as InnoDB. It is not applicable in non-transactional storage engines like MyISAM.

Examples

1. Basic Transaction Commit

START TRANSACTION;
INSERT INTO accounts (name, balance) VALUES ('John Doe', 1000);
COMMIT;

In this example, a new account is added, and the change is saved by using `COMMIT` to finalize the transaction.

2. Committing Multiple Changes

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE name = 'John Doe';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Jane Smith';
COMMIT;

This example transfers funds between two accounts. Both updates are finalized together with a single `COMMIT`, ensuring atomicity.

3. Conditional Commit with Error Handling

To handle conditional commits and error detection, use application logic or stored procedures. Direct `IF` statements in SQL scripts are not supported. Here is an example using a stored procedure:

DELIMITER //
CREATE PROCEDURE ConditionalCommit()
BEGIN
    DECLARE order_exists INT;
    START TRANSACTION;
    DELETE FROM orders WHERE order_id = 101;
    SELECT COUNT(*) INTO order_exists FROM orders WHERE order_id = 101;
    IF order_exists = 0 THEN
        COMMIT;
    ELSE
        ROLLBACK;
    END IF;
END //
DELIMITER ;

In this example, a procedure checks if the order deletion is successful before committing.

Tips and Best Practices

• Pair with `START TRANSACTION`. Use `COMMIT` with `START TRANSACTION` to ensure changes are part of a transaction.
• Handle errors with `ROLLBACK`. Revert changes before committing in case of errors.
• Ensure transaction consistency and complete all necessary operations within a transaction before committing.
• Minimize transaction duration to reduce locking and improve performance.
• Test before committing to verify that all changes meet your requirements.

Additional Note

Issuing a `COMMIT` without a preceding `START TRANSACTION` has no effect, as there are no changes to finalize. Always ensure that `START TRANSACTION` is used to initiate a transaction.