Review of MySQL 10: TCL and transaction presentation

Time:2020-9-26

The mountain sits with the banquet and draws pictures, the water makes the night window, and the wind and rain come. It’s wonderful to see the water and the mountains. What’s more, it will pollute the platform. After the baptism of wind and rain, the trees on the opposite side of the mountain, the flowers and plants on both sides of the road are more colorful and moving. The trees on the roadside pull out new branches and buds, and there are several fungi under the trees. The rain and dew on them are shining and moving, that is, the weather is a little cold.Please pay attention to adding clothes.

Today, I will continue to lead readers to continue to learn the Tcl language of MySQL, not to say much nonsense, but directly to the concept! Go ahead

Transaction control language
Things: an execution unit is composed of one or a group of SQL statements. The execution unit either executes all or does not execute all of them. Each SQL statement is interdependent. As an indivisible whole, the whole unit will be rolled back if a SQL statement fails or produces an error. All affected data will return to the state before the transaction started; if all SQL statements in the cell are executed successfully, the transaction is executed successfully.
Storage engine (table type) Introduction
1. Concept: the data in MySQL is stored in files (or memory) with various technologies.
2. View the storage engines supported by MySQL through show engines; um, joss.
3. The most used storage engines in MySQL are InnoDB, MyISAM, memory, etc., in which InnoDB supports things, while MyISAM and memory do not support things.

Acid attribute of things
1. Atomicity refers to the fact that things are an inseparable unit of work, and the operations in things either happen or do not happen.
2. Consistency things must make the database change from one consistency state to another.
3. Isolation refers to that the execution of a thing cannot be interfered by other things, that is, a thing
The internal operation and the data used are isolated from other concurrent things, and the concurrent execution can not interfere with each other.
4. Durability refers to that once a thing is submitted, it will change the data in the database permanently, and other operations and database failures should not have any impact on it.

Case: transfer, pseudocode. Pleasant goat is ready to transfer 500 to grey wolf and ask grey wolf to help repair the fence of Yangcun village.
Xiyangyang 1000
Grey wolf 1000
Update table set balance of pleasant goat = 500 where name ='pleasant goat '
Accidentally disconnected, pleasant goat less than 500, gray wolf should normally go the following code.
Update table set balance of grey wolf = 1500 where name ='grey wolf '

 

View engine
SHOW ENGINES;

Creation of transactions

Implicit transaction: the transaction has no obvious open and end marks
For example, insert, update, Dalete statements
Delete from table where id = 1;

Show transactions: transactions have obvious open and end marks
Premise: the auto submit function must be set to disable
SET autocommit=0;

Step 1: open things
SET autocommit=0;
start transcommit=0;
Start transaction; optional
Step 2: write the SQL statement in the transaction (select Insert update delete)
Statement 1;
Sentence 2;
...

Step 3: close the thing
Commit; commit the transaction
Rollback; roll back a transaction

Delete table
DROP TABLE IF EXISTS accoun_t;
Create table
CREATE TABLE accoun_t(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);
Insert test data
INSERT INTO accoun_t(username,balance)
Values ('pleasant goat', 1000), ('grey wolf ', 1000);
#Demonstrate how to use transaction: commit
#Open transaction
SET autocommit=0;
START TRANSACTION;
#Simulate writing a set of transactions
UPDATE accoun_ T set balance = 500 where username = pleasant goat;
UPDATE accoun_ T set balance = 1500 where username = grey wolf;
#End transaction: commit
COMMIT;
#View table data
SELECT * FROM accoun_t;

#Demonstrate how to use a transaction: rollback
#Open transaction
SET autocommit=0;
START TRANSACTION;
#Simulate writing a set of transactions
UPDATE accoun_ T set balance = 1000 where username = pleasant goat;
UPDATE accoun_ T set balance = 1000 where username = grey wolf;

#End transaction rollback
ROLLBACK;
#View table data
SELECT * FROM accoun_t;

#No change back1000

The principle of transaction

#Introduction to concurrency of transactions
#For multiple transactions running at the same time, when these transactions access the same data in the database, if the necessary isolation mechanism is not adopted, it will lead to various concurrency problems
Dirty read: one thing reads uncommitted data from another. (not submitted)
If the T2 transaction has not been read, it will be invalid if the T2 has not been read.

Non repeatable read: the content of data read twice in a transaction is inconsistent, which requires that the data read multiple times in a transaction is consistent, which is a problem caused by the update of the transaction. (Updated)
For two things, T1 and T2. T1 reads a field, and after T2 updates the field, T1 reads the same field again, and the value is different.

Phantom read: the number of data read twice in a transaction is inconsistent. It is required that the number of data read multiple times in a transaction is consistent. This is a problem caused by insert or delete. (insert)
For two things, T1 and T2. T1 reads a field from a table, and T2 inserts new rows into the table. After that, if T1 reads the same table again, there will be a few more rows.

Isolation level of the transaction

Read uncommitted: allows things to read changes that have not been committed by other transactions, dirty reads, nonrepeatable reads, and phantom reads.
Read committed: transactions are only allowed to read changes that have been committed by other things. Dirty reads can be avoided, but non repeatable and phantom reads may still occur.
Repeatable read: ensures that a transaction can read the same value from a field multiple times, and prevents other transactions from updating the field during the duration of the transaction.
Serialization: ensure that transactions can read the same rows from a table. During the duration of the transaction, other transactions are forbidden to perform insert, update and delete operations on the table. All concurrency problems can be avoided, but the performance is very low.
Each time you start a MySQL program, you get a separate database connection, and each database connection has one.
Global variable @ TX_ Isolation, which represents the current transaction isolation level.
View the current isolation level: select @ TX_ isolation;
Set the isolation level of the current MySQL connection
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Set the global isolation level of the database system
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

#Isolation of database transactions: the database system must have the ability of isolation and concurrent running of various transactions, so that they will not affect each other and avoid various concurrent problems.
#The degree to which one transaction is isolated from other transactions is called the isolation level.
#Oracle: two transaction isolation levels supported: read committed, serializable. The default isolation level of Oracle is read committed.
#MySQL supports four transaction isolation levels, and the default transaction isolation level of MySQL is repeatable read

Demonstration of dirty reading

The following operations are transferred to the operation under CMD or Linux
View the default isolation level
SELECT @@tx_isolation;

T1 switch Library
Set isolation level
Open transaction
View table data
USE girls;
set global transaction isolation level read uncommitted;
start transaction;

T2 switch Library
Set isolation level
Open transaction
View table data
USE girls;
set global transaction isolation level read uncommitted;
start transaction;
SELECT * FROM accoun_t;

T1 modification data
RollBACK 
UPDATE accoun_t SET username='xiyangyang' WHERE id=1;
rollback;

Solution to dirty reading problem:
Recover the data first
UPDATE accoun_t SET balance=500 WHERE id=1;
UPDATE accoun_t SET balance=1500 WHERE id=2;

 

T1 set isolation level
set global transaction isolation level read committed;
start transaction;

T2 exit reenter
Open transaction
Switch Library
mysql -u RemoteLogin -p
start transaction;
USE girls;

T1 modify data, do not submit
UPDATE accoun_t SET balance=500 WHERE id=1;
UPDATE accoun_t SET balance=1500 WHERE id=2;

T2 query, no modified data was found

T1

T2 got the data

Non repeatable demo

T1 recover data first
Set isolation level
UPDATE accoun_t SET balance=1000 WHERE id=1;
SET global transaction isolation level read committed;

T2 login database
Switch Library
Opening things up
View data
mysql -u RemoteLogin -p
USE girls;
start transaction;
SELECT * FROM accoun_t;

T1 opens things up
Modify data
Submit data
start transaction;
UPDATE accoun_t SET balance=1500 WHERE id=1;
commit;

T2 query found inconsistent data with the first query

resolvent
T1 improves isolation level
set global transaction isolation level repeatable READ;

T2 re login
Open transaction
View data
mysql -u RemoteLogin -p
start transaction;
SELECT * FROM girls.accoun_t;

T1 open transaction
Modify data
Submit data
start transaction;
UPDATE accoun_t SET balance=1300 WHERE id=1;
commit;

T2 view or original data

If you want to see the latest data, submit it first and then view it.

Phantom reading demonstration
T1 close transaction
Switch Library
View data
SET autocommit=0;
USE girls;
SELECT * FROM accoun_t;

T2 shut things down
Switch Library
View data
Insert a new line
Submit
SET autocommit=0;
USE girls;
SELECT * FROM accoun_t;
INSERT INTO accoun_t values(NULL,'shiyuan',2000);
commit;

T1 modified the data, and the results showed that four lines were changed.

Solution serialization
T1 improves transaction level serialization
Shut things down
View data
Update data but do not execute
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET autocommit=0;
SELECT * FROM accoun_t;
UPDATE accoun_ T set user name: 'dayitong';, do not execute first

Problem solving
Turn on T2 serialization
Shut things down
Insert a row of data
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET autocommit=0;
INSERT INTO accoun_t VALUES(NULL,'yazhou',5000);

T1 execution modification data
Submit
UPDATE accoun_t SET username='dayitong';
commit;

T2 wait timeout is kicked out

Rollback point
Close transaction
Switch Library
View data
SET autocommit=0;
USE girls;
SELECT * FROM accoun_t;

Change all usernames to pleasant goat
Change the user name to grey wolf
Set rollback point
Then change all the usernames to be bald
Data file back to archive
View data
UPDATE accoun_ T set username ='pleasant goat ';
UPDATE accoun_ T set username ='grey wolf ';
SAVEPOINT cundang;
UPDATE accoun_ T set username ='bareheaded ';
ROLLBACK TO cundang;
SELECT * FROM accoun_t;

DELETEandTRUNCATEThe difference in things

#Demo delete
Turn off transaction auto commit
Open transaction
Delete table
RollBACK 
see
SET autocommit=0;
START TRANSACTION;
DELETE FROM major2;
ROLLBACK;
SELECT * FROM major2;

#Demonstrate truncate
Turn off auto commit for transactions
Opening things up
Delete table
RollBACK 
see
SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE major2;
ROLLBACK;
SELECT * FROM major2;

This is the end of transaction control, and I hope to see the readers here have a harvest. Let’s meet and see you next time! o(^▽^)o

 

Recommended Today

What black technology does the real-time big data platform use behind the glory of the king?

Hello everyone, I’m Xu Zhenwen. Today’s topic is “Tencent game big data service application practice based on Flink + servicemesh”, which is mainly divided into the following four parts: Introduction to background and Solution Framework Real time big data computing onedata Data interface service onefun Microservice & servicemesh 1、 Introduction to the solution framework and […]