Does COMMIT TRANSACTION Omission Automatically Rollback Transactions?

Automatic Rollback if COMMIT TRANSACTION is Omitted

In the given SQL statement:

START TRANSACTION;
BEGIN;
INSERT INTO prp_property1 (module_name,environment_name,NAME,VALUE) VALUES ('','production','','300000');

/** Assume there is syntax error SQL here...**/
Blah blah blah

DELETE FROM prp_property1 WHERE environment_name = 'production';
COMMIT TRANSACTION;

The question arises whether the transaction is automatically rolled back since the COMMIT TRANSACTION statement is never reached due to a syntax error.

Transaction Rollback Behavior

Contrary to the assumption, transactions are not automatically rolled back upon encountering an error. This behavior is typically implemented in client-application settings. For instance, the MySQL command-line client terminates execution and exits upon an error, which results in the rollback of any in-progress transactions.

When developing custom applications, developers have control over transaction rollback policies. However, certain exceptions exist:

• Quitting the database connection always rolls back ongoing transactions.
• A deadlock or lock-wait timeout also implicitly triggers a rollback.

Outside these specific scenarios, errors do not automatically cause rollback. The error is returned, and the developer is free to decide the next course of action, including committing the transaction despite the error.

The above is the detailed content of Does COMMIT TRANSACTION Omission Automatically Rollback Transactions?. For more information, please follow other related articles on the PHP Chinese website!