When is a Transaction Not Automatically Rolled Back in SQL?

Automatic Transaction Rollback

The question revolves around the behavior of transactions when an error occurs within a block of SQL statements enclosed by START TRANSACTION and COMMIT TRANSACTION. The OP notices that when a syntax error is encountered before the COMMIT TRANSACTION statement, the transaction is automatically rolled back.

Transaction Rollback Mechanism

No, transactions are not rolled back immediately upon encountering an error. However, certain client applications may adopt specific policies for error handling.

For example, in the mysql command-line client:

• An error during execution typically halts the execution process and results in quitting the client.
• Quitting while a transaction is ongoing automatically causes the rollback of the transaction.

Customizing Rollback Policy

When building your own applications, you have control over transaction rollback policies. However, note that there are exceptions where a rollback is enforced:

1. Client Disconnect: Quitting or disconnecting from the database always initiates a transaction rollback.
2. Deadlock/Lock-Wait Timeout: When a deadlock or lock-wait timeout occurs, an implicit rollback takes place.

For all other scenarios, if an error is encountered during a transaction, the error is returned, and the developer has the choice to commit or discard the transaction.

The above is the detailed content of When is a Transaction Not Automatically Rolled Back in SQL?. For more information, please follow other related articles on the PHP Chinese website!