Database: MySQL built-in features-transactions

a thing

Transactions are used to manipulate multiple SQL for some operations as atomic, and once an error occurs, it can be rolled back to its original state, guaranteeing database data integrity.

CREATE table user (ID int primary key auto_increment,name char (+), balance int), insert into user (name,balance) VALUES (' WSB ', +), (' Egon ', +), (' YSB ', +), #原子操作start transaction;update user set balance=900 where name= ' WSB '; #买支付100元update user set balance=1010 where name= ' Egon '; #中介拿走10元update user set balance=1090 where name= ' YSB '; #卖家拿到90元commit; #出现异常, roll back to the initial state start transaction;update user set balance=900 where name= ' WSB '; #买支付100元update user set balance=1010 where name= ' Egon '; #中介拿走10元uppdate user set balance=1090 where name= ' YSB '; #卖家拿到90元, an exception did not get rollback;commit;mysql> select * FROM user;+----+------+---------+| ID | name | Balance |+----+------+---------+|  1 | WSB  |    | |  2 | Egon |    | |  3 | YSB  |    |+----+------+---------+rows in Set (0.00 sec)

　　

#介绍delimiter//CREATE PROCEDURE P4 (out status int) BEGIN --1.                    Declares that {set status = 1 is executed if an exception occurs;               Rollback                    ---}--Start transaction--minus 100 from fought account--Fang Shaowei account plus 90--Zhanggen account plus 10               Commit            --End Set status = 2; END//delimiter; #实现delimiter//create PROCEDURE P5 (out P_return_code tinyint) BEGIN DECLARE exit Handl         Er for SqlException BEGIN--ERROR set p_return_code = 1;     Rollback     END;         DECLARE exit handler for SQLWarning BEGIN--WARNING set p_return_code = 2;     Rollback     END;         START TRANSACTION; DELETE from TB1;    #执行失败 INSERT INTO blog (name,sub_time) VALUES (' yyy ', now ());     COMMIT; --SUCCESS Set p_return_code = 0; #0代表执行成功END//delimiter; #在mysql中调用存储过程set @res =123;call P5 (@res); select @res; #在python中基于pymysql调用存储过程cursor. Callproc (' P5 ', (123,)) print (Cursor.fetchall ()) # Query results for select Cursor.execute (' select @_p5_0; ') Print (Cursor.fetchall ())

　　

Database: MySQL built-in features-transactions