Detailed explanation of database transaction

Time:2021-10-23

1、 Business

1. What is a transaction

  • A transaction consists of one or moreSQLThe logical execution unit composed of statements can be compared to a container, which contains a pile ofSQLStatements, either all of which are executed successfully or none of which 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 the previous state and close the transaction)
rollback;  #  Rollback + close

Transaction commit (commit will be modified and transaction closed)
commit;    #  Submit + close

As can be seen from the above keywords, the start 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;

image-20210214124605661

image-20210214124833697

image-20210214125609578

  • pymysqlImplement transaction instances
select * from user where id=1;

image-20210226154121496

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 rollback, and the exception is detected and the transaction will end without committing)
    Print (F "execution failed: {e}")
else:
    Conn.commit() # transaction commit

cursor.close()
conn.close()

image-20210226154506004

2、 Four characteristics of transactions (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

  • The execution of each transaction does not interfere with each other. The internal operation of any transaction is isolated from other concurrent transactions; That is, 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 the permanent memory, usually the physical database

3、 Three operation modes of transaction

Implicit can be compared to automatic

Explicit can be compared to manual

1. Automatically commit transactions (implicitly enabled, implicitly committed)

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

  • mysqlAuto commit transaction is enabled by default. You can turn on or off the auto commit transaction 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]
  • SQL serverAuto commit transaction is enabled by default, and can be closed or enabled 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
  • OracleCommit transaction is displayed by default, and the automatic commit transaction mode can be turned on or off through the following statement
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

  • mysqlwithstart transactionperhapsbeginStatement to start a transactioncommitperhapsrollbackStatement end transaction

3. Implicit transaction (open implicitly, show commit)

In implicit transactions, you do not need to usebeginperhapsstart transactionTo start a transaction. The first execution of each SQL statement will automatically start a transaction, but you need to usecommitSubmit orrollbackRollback to end the transaction

  • mysqlBy turning off auto commit transactions, you can implicitly turn on transactions and display committed transactions
set session autocommit=0;
#0 is off and 1 is on
#[session is the current session variable \ global is the global variable]

4. Summary

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

  • Directly set the submission mode of MySQL
Set session|global autocommit = 0 # disable autocommit
Set session|global autocommit = 1 # enable autocommit
  • Start transaction manually
begin;   #  Or start transaction;
    [SQL statement]
commit;  #  Or rollback;

4、 Transaction savepoint

1. What is a transaction savepoint

  • Savepoint is similar to snapshot in virtual machine and used in transaction
  • Every savepoint set is a savepoint
  • When the transaction ends, all defined savepoints are automatically deleted
  • You can go back to any savepoint before the transaction ends

2. Set and rollback savepoint syntax

Savepoint [savepoint name]# Set save point
Rollback to [a savepoint name]# Rollback to a savepoint. Operations after the savepoint are invalid, including savepoints
rollback;  #  Roll back all operations after the transaction is opened, delete all savepoints, and end the transaction

3. Transaction savepoint experiment

  • You can set many savepoints (one, two, three)
begin;
select * from user;
update user set name="shawn" where id=1;
savepoint one;
select * from user;
update user set name="xxxx" where id=3;
savepoint two;
select * from user;
delete from user where id>3;
savepoint three;
select * from user;

image-20210226185151636

image-20210226190013451

  • Fallback to a savepoint can span multiple savepoints
rollback to two;
select * from user;

image-20210226190701117

  • The savepoint three created after the savepoint two is also invalid
rollback to three;

image-20210226190850751

  • Rollback rollback all
rollback

image-20210226191136992

5、 Principles of transaction usage

  • Keep transactions short
  • Try to avoid rollback in transactions
  • Try to avoid savepoints
  • Explicitly declare open transactions
  • By default, it relies on pessimistic locks and considers optimistic locks for transactions with demanding throughput requirements
  • The fewer rows to lock, the better. The shorter the lock time, the better

—end—