YouTipMysql Transaction
MySQL transactions are mainly used to handle large volumes of operations and complex data. For example, in a personnel management system, when you delete a person, you need to delete both the person's basic information and all related information, such as mailbox, articles, and so on. These database operations together constitute a transaction!
In MySQL, a transaction is a group of SQL statements that are executed as a single work unit.
* In MySQL, only databases or tables that use the InnoDB storage engine support transactions.
* Transaction processing can be used to maintain database integrity, ensuring that a batch of SQL statements either all execute successfully or all fail.
* Transactions are used to manage **INSERT, UPDATE, DELETE** statements.
Generally, a transaction must satisfy four conditions (ACID): Atomicity (**A**tomicity), Consistency (**C**onsistency), Isolation (**I**solation), and Durability (**D**urability).
* **Atomicity:** All operations within a transaction are either completed entirely or not completed at all. If an error occurs during execution, the transaction is rolled back to its state before it began, as if it never executed.
* **Consistency:** Before and after a transaction, the integrity of the database is not compromised. This means the data written must fully comply with all predefined rules, including accuracy, referential integrity, and the database's ability to perform subsequent operations.
* **Isolation:** The database allows multiple concurrent transactions to read and modify data simultaneously. Isolation prevents inconsistencies that can arise from concurrent execution. Transaction isolation levels include Read Uncommitted, Read Committed, Repeatable Read, and Serializable.
* **Durability:** Once a transaction is committed, the changes are permanent and will not be lost even in the event of a system failure.
> By default, in the MySQL command line, transactions are auto-committed, meaning a COMMIT operation is executed immediately after an SQL statement. To explicitly start a transaction, you must use the `BEGIN` or `START TRANSACTION` command, or execute `SET AUTOCOMMIT=0` to disable auto-commit for the current session.
### Transaction Control Statements:
* `BEGIN` or `START TRANSACTION` explicitly starts a transaction.
* `COMMIT` (or `COMMIT WORK`, which is equivalent) commits the transaction, making all changes to the database permanent.
* `ROLLBACK` (or `ROLLBACK WORK`, which is equivalent) ends the user's transaction and undoes all uncommitted changes.
* `SAVEPOINT identifier` creates a savepoint within a transaction. A transaction can have multiple savepoints.
* `RELEASE SAVEPOINT identifier` deletes a savepoint. If the specified savepoint does not exist, an exception is thrown.
* `ROLLBACK TO identifier` rolls the transaction back to a specific savepoint.
* `SET TRANSACTION` is used to set the transaction isolation level. The InnoDB storage engine provides isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.
!(#)
### There are two main methods for MySQL transaction processing:
1. Using `BEGIN`, `ROLLBACK`, and `COMMIT`:
* **`BEGIN` or `START TRANSACTION`**: Starts a transaction.
* **`ROLLBACK`**: Rolls back the transaction, canceling previous changes.
* **`COMMIT`**: Confirms the transaction, committing changes permanently.
2. Directly using `SET` to change MySQL's auto-commit mode:
* **`SET AUTOCOMMIT=0`**: Disables auto-commit.
* **`SET AUTOCOMMIT=1`**: Enables auto-commit.
`BEGIN` or `START TRANSACTION` -- Used to start a transaction:
```sql
BEGIN; -- Or use START TRANSACTION;
`COMMIT` -- Used to commit the transaction, saving all changes permanently to the database:
```sql
COMMIT;
`ROLLBACK` -- Used to roll back the transaction, undoing all changes made since the last commit:
```sql
ROLLBACK;
`SAVEPOINT` -- Used to set a savepoint within a transaction, allowing a rollback to that point later:
```sql
SAVEPOINT savepoint_name;
`ROLLBACK TO SAVEPOINT` -- Used to roll back to a previously set savepoint:
```sql
ROLLBACK TO SAVEPOINT savepoint_name;
Here is a simple example of a MySQL transaction:
## Example
```sql
-- Start the transaction
START TRANSACTION;
-- Execute some SQL statements
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;
-- Decide whether to commit or rollback
IF (condition) THEN
COMMIT; -- Commit the transaction
ELSE
ROLLBACK; -- Rollback the transaction
END IF;
### Example
A simple transaction example:
## Transaction Test
```sql
mysql> use ;
Database changed
mysql> CREATE TABLE tutorial_transaction_test(id int(5))engine=innodb;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from tutorial_transaction_test;
Empty set (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tutorial_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
mysql> insert into tutorial_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from tutorial_transaction_test;
+------+
| id |
+------+
| 5 |
| 6 |
+------+
2 rows in set (0.01 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tutorial_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from tutorial_transaction_test;
+------+
| id |
+------+
| 5 |
| 6 |
+------+
2 rows in set (0.01 sec)
mysql>
### Using Transactions in PHP
## MySQL ORDER BY Test:
```php