What is a transaction?
A transaction is a logical group of operations that constitute each unit of this group of operations. If it fails or fails, it is a transaction.
Note: mysql DATA supports transactions, but must be an innoDB Storage engine.
Solve this problem:
Mysql transactions solve this problem. Because of the transaction characteristics of mysql, this set of operations is required, either completely successful or all failed, which avoids the success of an operation. Conducive to data security
How to use:
(1) Before executing an SQL statement, start transaction is enabled for the transaction;
(2) normal execution of our SQL statements
(3) When the SQL statement is executed, there are two situations:
1. All are successful. We need to submit the impact of SQL statements on the database to the database, committ
2. If some SQL statements fail, we execute rollback to quickly cancel database operations.
(Note: mysql DATA supports transactions, but must be an innoDB Storage engine)
Mysql> create table bank (name varchar (20), money decimal (5, 1) engine = innodb defau
Lt charset = utf8;
Mysql> inset into bank values ('shaotuo ', 1000), ('laohu', 5000 );
Mysql> select * from bank;
+ --------- + -------- +
| Name | money |
+ --------- + -------- +
| Shaotuo | 1000.0 |
| Laohu | 5000.0 |
+ --------- + -------- +
------ Failed to roll back and execute rollback
Mysql> start transaction; // start the transaction
Query OK, 0 rows affected (0.00 sec)
Mysql> update bank set money = money + 500 where name = 'shaotuo ';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Mysql> update bank set moey = money-500 where name = 'laohu ';
ERROR 1054 (42S22): Unknown column 'moine' in 'field list'
Mysql> rollback; // If one fails, perform the rollback operation.
Query OK, 0 rows affected (0.01 sec)
Mysql> select * from bank;
+ --------- + -------- +
| Name | money |
+ --------- + -------- +
| Shaotuo | 1000.0 |
| Laohu | 5000.0 |
+ --------- + -------- +
------ Perform the commit operation after the operation is successful
Mysql> start transaction; // start the transaction
Query OK, 0 rows affected (0.00 sec)
Mysql> update bank set money = money + 500 where name = 'shaotuo ';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Mysql> update bank set money = money-500 where name = 'laohu ';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Mysql> commit; // execute commit after both of them are successful (as long as no commit is executed, the SQL statement will not affect the real database)
Query OK, 0 rows affected (0.05 sec)
Mysql> select * from bank;
+ --------- + -------- +
| Name | money |
+ --------- + -------- +
| Shaotuo | 1500.0 |
| Laohu | 4500.0 |
+ --------- + -------- +