What is business
Transaction is to operate multiple sql statements. These sql statements must be executed successfully at the same time. If one fails, it will return to its original state
Role of transaction
Ensure the security of data, if transfer between banks
Four characteristics of transaction ACID
atomicity:
All operations contained in the transaction are either all successful or all failed to be rolled back; success must be fully applied to the database, and failure cannot affect the database
consistency:
Before and after the execution of affairs, they must be in a consistent state. For example, Zhang San and Wang Ba have a total of 500 oceans. No matter how they transfer money to each other, after they transfer accounts, that is, after the end of affairs, their money adds up to 500 oceans, which is the consistency of affairs
isolation:
When multiple users access the database concurrently, the transactions opened by the database for each user will not be interfered by the operations of other transactions, and multiple concurrent transactions should be isolated from each other
durability:
Once a transaction is committed, the change to the data in the database is permanent. Even in the case of database system failure, the operation of things will not be lost
How to use transactions
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);
# Start transaction operation before modifying data
start transaction;
# Modify operation
update user set balance=900 where name='wsb'; #100 yuan for purchase
update user set balance=1010 where name='egon'; #Agency takes 10 yuan
update user set balance=1090 where name='ysb'; #The seller gets 90 yuan
# Roll back to previous state
rollback;
# Look at the data in the query table
select * from user;
mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 1000 | | 2 | egon | 1000 | | 3 | ysb | 1000 | +----+------+---------+ 3 rows in set (0.00 sec)
It can be seen from this that the previously updated data has not been modified at all. Why?
As long as the transaction is not committed, the data is still in memory and will not be saved to the hard disk until it is committed
# Start transaction operation before modifying data start transaction; # Modify operation update user set balance=900 where name='wsb'; #100 yuan for purchase update user set balance=1010 where name='egon'; #Agency takes 10 yuan update user set balance=1090 where name='ysb'; #The seller gets 90 yuan # Submission of affairs commit; # Roll back to previous state rollback; # Look at the data in the query table select * from user;
mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 900 | | 2 | egon | 1010 | | 3 | ysb | 1090 | +----+------+---------+ 3 rows in set (0.00 sec)
so change it
From a code point of view
try: update user set balance=900 where name='wsb'; #100 yuan for purchase update user set balance=1010 where name='egon'; #Agency takes 10 yuan update user set balance=1090 where name='ysb'; #The seller gets 90 yuan except Exception as e: rollback; else: commit;
ok ,