MySQL：未提交的事务回滚（mysql未提交的事物）
Every software engineer or database engineer has heard of ACID safety, which is an important concept in database design. In ACID, T stands for transaction. This article is to explain how to roll back an uncommitted transaction in MySQL, and how to ensure that a transaction is successful and reliable in real-world systems.
MySQL provides a basic mechanism for transaction management with the “begin”, “commit” and “rollback” operations. A transaction can be viewed as a unit of work that is either fully successful or fails completely, either writing all its changes to the database, or rolling them back.
The basic flow of transaction goes like this:
* Begin
* Execute a series of database operations with valid SQL statements
* Commit the transaction if it executes successfully
* Rollback the transaction if any part of the transaction fails
Rollback is the process of undoing the changes made to a database as part of a transaction, if it fails or is cancelled. In MySQL, the rollback operation can be done by using the “ROLLBACK” statement. It is supported by both InnoDB and MyISAM storage engines.
For example, the following operation will start a transaction, insert a record in the “items” table, make a mistake in a subsequent query, and rollback the transaction.
“`sql
BEGIN;
INSERT INTO items (name, price) VALUES ( ‘Monitor’, 599);
UPDATE items SET price = 200 WHERE price > 100; # make a mistake
ROLLBACK;
“`
The above query will insert a record into the table, but then the “UPDATE” statement will fail (because there is only one record inserted and its price is lower than the specified price), the transaction will be rolled back and the record will not be stored in the database.
In real-world applications, developers should always use ACID transaction pattern when creating an application that requires transaction. It is also possible to use a “SAVEPOINT” to set a point of no return in a transaction, so if anything fails after this point, only changes occur after the savepoint can be rolled back. This provides a great way to ensure the reliability of a transaction without having to roll back the entire transaction.
Even with ACID patterns, it is important to perform some tests to make sure that transactions are committed and rollbacked depending on the circumstances. Various types of errors can arise due to system or hardware failures, or unhandled exceptions, and tests should be performed to make sure these scenarios don’t occur in production.
In conclusion, transactions are an important part of database development and operations. MySQL provides a powerful mechanism to handle transactions and roll back any failed transactions. It is important to use the ACID pattern for transactions, and also to perform tests to make sure that it is working properly in production.
