Transaction, stored procedure

1, Business

1. What is a transaction:

A database transaction is a series of operations (SQL statements) performed as a single logical unit of work. These operations are either performed or not performed at all.

2. Why transactions

In classic bank transfer, multiple sql statements are executed together, either succeed together or fail together. If they fail, won't one party lose?

3. Transaction characteristics (4 kinds):

  1. atomicity: emphasizes the indivisibility of transactions
  2. consistency: the integrity of data before and after transaction execution is consistent
  3. isolation: during the execution of a transaction, it should not be disturbed by other transactions
  4. durability: once the transaction ends, the data is persisted to the database

4. Transaction operation mode (3 kinds)

  1. Auto commit transaction: the default transaction management mode. If a statement completes successfully, submit the statement; If an error is encountered, the statement is rolled back.
  2. Explicit transaction: start explicitly with BEGIN TRANSACTION and end explicitly with COMMIT or ROLLBACK.
  3. Implicit transaction: when the connection operates in this mode, sql will automatically start a new transaction after committing or rolling back the current transaction. Instead of describing the beginning of a transaction, just commit or roll back each transaction. It generates a continuous chain of transactions.

Example:

 1 create table user(
 2 id int primary key auto_increment,
 3 name char(32),
 4 balance int
 5 );
 6 
 7 insert into user(name,balance)
 8 values
 9 ('Lao Li',999),
10 ('Lao Zhang',999),
11 ('Zarin ',999);
12 
13 #Atomic operation
14 start transaction;
15 update user set balance=899 where name='wsb'; #Buy pay 100 yuan
16 update user set balance=1009 where name='egon'; #The intermediary took 10 yuan
17 update user set balance=1089 where name='ysb'; #The seller gets 90 yuan
18 commit;
19 
20 #If an exception occurs, roll back to the initial state
21 start transaction;
22 update user set balance=899 where name='wsb'; #Buy pay 100 yuan
23 update user set balance=1009 where name='egon'; #The intermediary took 10 yuan
24 uppdate user set balance=1089 where name='ysb'; #The seller gets 90 yuan,There was an exception and I didn't get it
25 rollback;
26 commit;
27 mysql> select * from user;
28 +----+------+---------+
29 | id | name | balance |
30 +----+------+---------+
31 |  1 | wsb  |    999 |
32 |  2 | egon |    999 |
33 |  3 | ysb  |    999|
34 +----+------+---------+
35 3 rows in set (0.00 sec)

In pymysql:

 1 try:
 2     cursor.execute(sql_1)  
 3     cursor.execute(sql_2)  
 4     cursor.execute(sql_3)  
 5 except Exception as e:
 6     connect.rollback()  # Transaction rollback
 7     print('Transaction failed', e)
 8 else:
 9     connect.commit()  # Transaction commit
10     print('Transaction successful', cursor.rowcount)# Close connection
11 cursor.close()
12 connect.close()

Summary: transaction is used to treat multiple SQL of some operations as atomic operations. Once an error occurs in one of them, it can be rolled back to the original state, so as to ensure the integrity of database data.

2, Stored procedure

 

1. Introduction

 

The stored procedure contains a series of executable sql statements. The stored procedure is stored in MySQL, and a pile of internal sql can be executed by calling its name

Advantages of using stored procedures:

#1. It is used to replace the SQL statement written by the program to realize the decoupling between the program and SQL

#2. Based on network transmission, the amount of data transmitted by alias is small, while the amount of data transmitted directly by sql is large

Disadvantages of using stored procedures:
#1. It is inconvenient for programmers to expand functions

Supplement: three ways of combining program and database
 1 #Mode 1:
 2     MySQL: stored procedure
 3     Programs: calling stored procedures
 4 
 5 #Mode 2:
 6     MySQL: 
 7     Program: pure SQL sentence
 8 
 9 #Mode 3:
10     MySQL:
11     Programs: classes and objects, i.e ORM(Essence or purity SQL Statement)

Create a simple stored procedure (no parameters)

 1 delimiter //
 2 create procedure p1()
 3 BEGIN
 4     select * from blog;
 5     INSERT into blog(name,sub_time) values("xxx",now());
 6 END //
 7 delimiter ;
 8 
 9 #stay mysql Call in
10 call p1() 
11 
12 #stay python Medium based pymysql call
13 cursor.callproc('p1') 
14 print(cursor.fetchall())

Create stored procedure (with reference)

For stored procedures, parameters can be received. There are three types of parameters:

#In is only used to pass in parameters
 #out is only used for return values
 #inout can be passed in or returned
 1 delimiter //
 2 create procedure p2(
 3     in n1 int,
 4     in n2 int
 5 )
 6 BEGIN
 7     
 8     select * from blog where id > n1;
 9 END //
10 delimiter ;
11 
12 #stay mysql Call in
13 call p2(3,2)
14 
15 #stay python Medium based pymysql call
16 cursor.callproc('p2',(3,2))
17 print(cursor.fetchall())

out

 1 delimiter //
 2 create procedure p3(
 3     in n1 int,
 4     out res int
 5 )
 6 BEGIN
 7     select * from blog where id > n1;
 8     set res = 1;
 9 END //
10 delimiter ;
11 
12 #stay mysql Call in
13 set @res=0; #0 For false (execution failed), 1 for true (execution succeeded)
14 call p3(3,@res);
15 select @res;
16 
17 #stay python Medium based pymysql call
18 cursor.callproc('p3',(3,0)) #0 amount to set @res=0
19 print(cursor.fetchall()) #query select Query results for
20 
21 cursor.execute('select @_p3_0,@_p3_1;') #@p3_0 Represents the first parameter,@p3_1 Represents the second parameter, the return value
22 print(cursor.fetchall())

inout

 1 delimiter //
 2 create procedure p4(
 3     inout n1 int
 4 )
 5 BEGIN
 6     select * from blog where id > n1;
 7     set n1 = 1;
 8 END //
 9 delimiter ;
10 
11 #stay mysql Call in
12 set @x=3;
13 call p4(@x);
14 select @x;
15 
16 
17 #stay python Medium based pymysql call
18 cursor.callproc('p4',(3,))
19 print(cursor.fetchall()) #query select Query results for
20 
21 cursor.execute('select @_p4_0;') 
22 print(cursor.fetchall())

Execute stored procedure

 1 -- No parameters
 2 call proc_name()
 3 
 4 -- With parameters, all in
 5 call proc_name(1,2)
 6 
 7 -- With parameters, with in,out,inout
 8 set @t1=0;
 9 set @t2=3;
10 call proc_name(1,2,@t1,@t2)
11 
12 Execute stored procedure

Delete stored procedure

drop procedure proc_name;






























Posted by LightningSt on Wed, 18 May 2022 16:48:42 +0300