Article catalogue

• 1, MySQL transaction
• • 1. Concept of affairs
  • 2. Transaction data preparation
  • 3. Unmanaged transaction presentation
  • 4. Management services presentation
  • 5. Submission method of affairs
  • 6. Four characteristics of transaction (ACID)
  • 7. Isolation level of transactions
  • 8. Presentation of transaction isolation level
  • 9. Summary of isolation level
  • 10. Summary of matters

1, MySQL transaction

1. Concept of affairs

• One or more SQL statements form an execution unit, which is characterized in that the unit either succeeds or fails at the same time. Each SQL statement in the unit depends on each other to form a whole. If the execution of a SQL statement fails or an error occurs, the whole unit will roll back and withdraw to the original state of the transaction, If all SQL statements in the cell are executed successfully, the transaction will execute smoothly.

2. Transaction data preparation

-- establish db10 database
CREATE DATABASE db10;

-- use db10 database
USE db10;

-- Create account table
CREATE TABLE account(
	id INT PRIMARY KEY AUTO_INCREMENT,	-- account id
	NAME VARCHAR(20),			-- title of account
	money DOUBLE				-- Account balance
);
-- Add data
INSERT INTO account VALUES (NULL,'Zhang San',1000),(NULL,'Li Si',1000);

3. Unmanaged transaction presentation

-- Zhang San transferred 500 yuan to Li Si
-- 1.Zhang San account-500
UPDATE account SET money=money-500 WHERE NAME='Zhang San';
-- 2.Li Si account+500
 Error ...
UPDATE account SET money=money+500 WHERE NAME='Li Si';

-- In this scenario, these two sql Statements either succeed or fail at the same time. You need to be managed by the firm!

4. Management services presentation

• Three steps of operating a transaction
  1. Start transaction: record the rollback point and notify the server that a set of operations will be performed, either successful or failed at the same time
  2. Execute sql statement: execute one or more specific sql statements
  3. End transaction (commit rollback)
     • Submit: no problem, update the data
     • Rollback: when a problem occurs, the data is restored to the state when the transaction was started
• Open transaction

-- Standard grammar
START TRANSACTION;

• Rollback transaction

-- Standard grammar
ROLLBACK;

• Commit transaction

-- Standard grammar
COMMIT;

• Management transaction presentation

-- Open transaction
START TRANSACTION;

-- Zhang San transferred 500 yuan to Li Si
-- 1.Zhang San account-500
UPDATE account SET money=money-500 WHERE NAME='Zhang San';
-- 2.Li Si account+500
-- Error ...
UPDATE account SET money=money+500 WHERE NAME='Li Si';

-- Rollback transaction(Something went wrong)
ROLLBACK;

-- Commit transaction(No problem)
COMMIT;

5. Submission method of affairs

• Submission method
  • Auto submit (MySQL defaults to auto submit)
  • Manual submission
• Modify submission method
  • View submission method

-- Standard grammar
SELECT @@AUTOCOMMIT;  -- 1 Automatic submission on behalf of    0 for manual submission

• Modify submission method

-- Standard grammar
SET @@AUTOCOMMIT=number;

-- Change to manual submission
SET @@AUTOCOMMIT=0;

-- View submission method
SELECT @@AUTOCOMMIT;

6. Four characteristics of transaction (ACID)

• Atomicity
  • Atomicity means that all operations contained in a transaction either succeed or fail and roll back. Therefore, if the operation of a transaction succeeds, it must be fully applied to the database. If the operation fails, it cannot have any impact on the database
• Consistency
  • Consistency means that a transaction must change the database from one consistency state to another, that is, a transaction must be in a consistency state before and after execution
  • For example, if Zhang San and Li Si's money add up to 2000, no matter how much money is transferred between A and B and how many times the account is transferred, the money of the two users should add up to 2000 after the transaction is completed. This is the consistency of the transaction
• Isolation
  • Isolation is that when multiple users access the database concurrently, such as operating the same table, the transactions opened by the database for each user cannot be disturbed by the operation of other transactions, and multiple concurrent transactions should be isolated from each other
• Durability
  • Persistence means that once a transaction is committed, the changes to the data in the database are permanent. Even in the case of database system failure, the operation of committing the transaction will not be lost

7. Isolation level of transactions

• Concept of isolation level
  • When multiple clients operate, the transactions of each client should be isolated, independent and unaffected.
  • If multiple transactions operate on the same batch of data, different isolation levels need to be set, otherwise problems will arise.
  • Let's first look at the names of the four isolation levels, and then look at the possible problems
• Four isolation levels

• Possible problems

• Query database isolation level

-- Standard grammar
SELECT @@TX_ISOLATION;

• Modify database isolation level

-- Standard grammar
SET GLOBAL TRANSACTION ISOLATION LEVEL Level string;

-- Modify the database isolation level to read uncommitted
SET GLOBAL TRANSACTION ISOLATION LEVEL read uncommitted;

-- View isolation level
SELECT @@TX_ISOLATION;   -- After modification, you need to disconnect and reconnect

8. Presentation of transaction isolation level

Dirty reading problem

• Window 1

-- Query account table
select * from account;

-- Set the isolation level to read uncommitted
set global transaction isolation level read uncommitted;

-- Open transaction
start transaction;

-- transfer accounts
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- Window 2 query transfer results ,Dirty reading(Uncommitted data of other transactions is queried)

-- After viewing the transfer result in window 2, execute rollback
rollback;

• Window 2

-- Query isolation level
select @@tx_isolation;

-- Open transaction
start transaction;

-- Query account table
select * from account;

Solve the problem of dirty reading and the problem that the presentation cannot be read repeatedly

• Window 1

-- Set the isolation level to read committed
set global transaction isolation level read committed;

-- Open transaction
start transaction;

-- transfer accounts
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- Check the transfer result in window 2, and there is no change(The dirty reading problem was solved)

-- Execute commit transaction.
commit;

-- View the transfer result in window 2, and the data has changed(There is a problem that cannot be read repeatedly. The committed data of other transactions is read)

• Window 2

-- Query isolation level
select @@tx_isolation;

-- Open transaction
start transaction;

-- Query account table
select * from account;

Solve the problem of non repeatable reading

• Window 1

-- Set the isolation level to repeatable read
set global transaction isolation level repeatable read;

-- Open transaction
start transaction;

-- transfer accounts
update account set money = money - 500 where id = 1;
update account set money = money + 500 where id = 2;

-- Check the transfer result in window 2, and there is no change

-- Execute commit transaction
commit;

-- At this time, as long as window 2 is still in the last transaction, the results are the same. Changes can only be seen when window 2 ends the transaction(The problem of non repeatable reading is solved)

• Window 2

-- Query isolation level
select @@tx_isolation;

-- Open transaction
start transaction;

-- Query account table
select * from account;

-- Commit transaction
commit;

-- Query account table
select * from account;

Problems and solutions of illusory reading

• Window 1

-- Set the isolation level to repeatable read
set global transaction isolation level repeatable read;

-- Open transaction
start transaction;

-- Add a record
INSERT INTO account VALUES (3,'Wang Wu',1500);

-- Query the account table, and you can view it in this window id Results for 3
SELECT * FROM account;

-- Commit transaction
COMMIT;

• Window 2

-- Query isolation level
select @@tx_isolation;

-- Open transaction
start transaction;

-- Query the account table. The newly added account cannot be found id Record for 3
select * from account;

-- add to id Failed to add a piece of data for 3. There is unreal reading
INSERT INTO account VALUES (3,'test',200);

-- Commit transaction
COMMIT;

-- Query the account table and find the newly added id Record for 3
select * from account;

• Solve the problem of unreal reading

/*
	Window 1
*/
-- Set the isolation level to serializable
set global transaction isolation level serializable;

-- Open transaction
start transaction;

-- Add a record
INSERT INTO account VALUES (4,'Zhao Liu',1600);

-- Query the account table, and you can view it in this window id Results for 4
SELECT * FROM account;

-- Commit transaction
COMMIT;



/*
	Window 2
*/
-- Query isolation level
select @@tx_isolation;

-- Open transaction
start transaction;

-- Query the account table and find that the query statement cannot be executed, and the data table is locked! The operation cannot continue until window 1 commits the transaction
select * from account;

-- add to id If a piece of data for 4 is found to exist, it will not be added again! The problem of unreal reading is solved
INSERT INTO account VALUES (4,'test',200);

-- Commit transaction
COMMIT;

9. Summary of isolation level

Note: the isolation level grows from small to large, with higher security but lower efficiency. Therefore, READ UNCOMMITTED and serial isolation levels are not recommended

10. Summary of matters

• One or more SQL statements form an execution unit, which is characterized by either success or failure at the same time. For example, transfer operation
• Start transaction: start transaction;
• Rollback transaction: rollback;
• Commit transaction: commit;
• Four characteristics of transaction
  • Atomicity
  • persistence
  • Isolation
  • uniformity
• Isolation level of transaction
  • Read uncommitted
  • read committed
  • repeatable read
  • serializable