I affair

1. What is a transaction

• A transaction is a logical execution unit composed of one or more SQL statements. It can be compared to a container containing a pile of SQL statements. Either all of these statements are executed successfully or none of them can be executed successfully (atomicity)

2. Why use transactions

• When performing a series of operations on data, in order to prevent some of these operations from succeeding and others from failing, resulting in incorrect data, we need to use transactions to rollback it to the original state

3. How to use transactions

• keyword

🍎Open transaction
begin;  # Or the following statement  
start transaction;

🍎Transaction rollback(Rollback to previous state,And close the transaction)
rollback;  # Rollback + close

🍎Transaction commit(Submit modification,And close the transaction)
commit;    # Submit + close

As can be seen from the above keywords, the beginning of a transaction corresponds to a rollback or commit, and then the transaction needs to be restarted

• Example of bank balance

🍎First create a user balance table and insert records
create table user(
    id int primary key auto_increment,
    name varchar(16) not null,
    balance int not null
);

insert user(name,balance) value
    ("shawn",150000),
    ("song",20000),
    ("xing",520022),
    ("hai",10000);

select * from user;  # Check all the records

🍎Open transaction
begin;  # Or start transaction;

🍎Update record
update user set balance=100 where name="shawn";
update user set balance=100 where name="song";
update user set balance=100 where name="xing";
update user set balance=200 where name="hai";

select * from user;  # Check whether the modification is successful

🍎Transaction rollback
rollback;  # After rollback, the transaction is closed
select * from user;  # Check whether to roll back to the original data

🍎Start another transaction
begin;

🍎Update the data again
update user set balance=200 where name="shawn";
update user set balance=200 where name="song";
commit;  # The transaction committed and closed
select * from user;  # View changes in data

rollback;  # Using transaction rollback again will no longer work because the transaction has been closed 
select * from user;

• Transaction processing instance implemented by pymysql

select * from user where id=1;

import pymysql

conn = pymysql.connect(
    host="127.0.0.1",
    port=3306,
    user="root",
    password="123456",
    database="test02",
    charset="utf8"
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql1 = "select * from user;"
sql2 = "update user set name=%s where id=1;"
sql3 = "select * from user;"

try:
    cursor.execute(sql1)  # Execute sql1
    print(cursor.fetchall())
    rows = cursor.execute(sql2,("song",))  # Execute sql2
    print("row : %s"%rows)
    cursor.execute(sql3)  # Execute sql3
    print(cursor.fetchall())
except Exception as E:
    conn.rollback()  # Transaction rollback (in pymysql, there is no need to roll back. If an exception is detected, it will end and will not be committed)
    print(f"Execution failed : {E}")
else:
    conn.commit()    # Transaction commit

cursor.close()
conn.close()

II Four characteristics of transaction (ACID)

1. Atomicity

• Transaction is the smallest execution unit of a program and cannot be subdivided (just as atoms in nature cannot be subdivided), so the operations in the transaction either succeed or fail

2. Consistency

• The execution of transactions must change the database from one consistency state to another. Consistency is guaranteed through atomicity

3. Isolation (Lsolation)

• The execution of each transaction does not interfere with each other. The internal operation of any transaction is isolated from other concurrent transactions; In other words, concurrent transactions cannot see each other's intermediate state, and concurrent transactions cannot affect each other

4. Sustainability

• Also known as "persistence", it means that once the transaction is submitted, any changes to the data are recorded in permanent memory, usually a physical database

III Three operation modes of transaction

Implicit can be compared to automatic

Explicit can be compared to manual

1. Automatically commit transactions (implicitly enable, implicitly commit)

Each individual SQL statement is a transaction. After the statement is executed, the commit operation will be executed automatically. If an error occurs, the transaction will be rolled back to the previous state

• mysql enables automatic transaction submission by default. You can turn on or off the automatic transaction submission mode through the following statement

set session autocommit=0;  
# 0 is off and 1 is on
# [session is the current session variable \ global is the global variable]

• By default, SQL server enables automatic transaction submission, which can be turned off or on through the following statement

set implicit_transactions ON;  
# ON is to turn ON implicit transaction mode or turn off automatic transaction mode
# OFF is to turn OFF implicit transaction mode or turn on auto commit transaction mode

• Oracle displays the commit transaction by default. Use the following statement to turn on or off the auto commit transaction mode

set autocommit on;
# on is to enable automatic transaction mode
# Off is to turn off auto commit transaction mode

2. Explicit transaction (explicit open, explicit commit)

The open transaction is displayed by specifying the transaction start statement, and the transaction is ended by the commit or rollback command

• mysql starts a transaction with a start transaction or begin statement and ends a transaction with a commit or rollback statement

3. Implicit transaction (open implicitly, show commit)

In implicit transactions, you do not need to use begin or start transaction to start the transaction. The first execution of each SQL statement will automatically start the transaction, but you need to use commit or rollback to end the transaction

• In mysql, the automatic transaction submission is turned off to achieve the purpose of implicitly turning on the transaction and displaying the submitted transaction

set session autocommit=0;
# 0 is off and 1 is on
# [session is the current session variable \ global is the global variable]

IV summary

MySQL starts the transaction for each SQL statement by default, and will automatically execute the commit operation for submission after the execution of this SQL statement. Set two methods of manual submission:

1. Directly set the submission mode of Mysql

set session|global autocommit=0  # Prohibit automatic submission
set session|global autocommit=1  # Turn on auto submit

2. Start transaction manually

begin;  # Or start transaction;
[SQL sentence]
commit;  # Or rollback;

---end---