What are transactions in Mysql and how are they used

What is a transaction?

A transaction is a logical set of 1 operations, and the units that make up the set of operations either all succeed or all fail, a feature known as a transaction

Note: mysql data supports transactions, but the requirement must be an innoDB storage engine

Solve the problem:

mysql's transactions solve this problem because the transactional nature of mysql requires that this set of operations either all succeed or all fail, thus avoiding the need for one operation to succeed and the other to fail. Conducive to the security of data

How to use:

(1) Before executing the sql statement, we need to start the transaction start transaction;

(2) Execute our sql statement normally

(3) When the sql statement is executed, there are two situations:

1, all successful, we will commit the impact of sql statement to the database, committ

2. Some sql statements fail and we execute rollback (roll back) to undo the database operation


(Note: mysql data supports transactions, but the requirement 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 rollback 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 'moey' in 'field list'
mysql > rollback; // As long as 1 is unsuccessful, perform rollback
Query OK, 0 rows affected (0.01 sec)

mysql > select*from bank;
+---------+--------+
| name | money |
+---------+--------+
| shaotuo | 1000.0 |
| laohu | 5000.0 |
+---------+--------+
-- After success, commit operation
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 (as long as you do not execute commit, the sql statement will not affect the actual database)
Query OK, 0 rows affected (0.05 sec)

mysql > select*from bank;
+---------+--------+
| name | money |
+---------+--------+
| shaotuo | 1500.0 |
| laohu | 4500.0 |
+---------+--------+