Transaction definition

Transactions are a set of atomicity SQL query , Or a separate unit of work . If the database engine can successfully apply all the statements of this group of queries to the database , Then the group query is executed . If any of the jump statements cannot be executed due to crash or other reasons , Then all statements are not executed . in other words , Statements within a transaction , Or all of them are successful , Or all of them fail .

The concept of a transaction is not limited to Mysql.

Examples of transactions

About bank account transfer operation , Account transfer is a complete business , Smallest unit , Can't be subdivided ——— In other words, bank account transfer is a transaction

<> The following is a list of bank accounts t_act( account number , balance ), Transfer
update t_act set balance=400 where actno=1; update t_act set balance=200 where
actno=2;

Above two DML Statements must succeed or fail at the same time . The smallest element is nonseparable , Be the first DML After the statement is executed successfully , The data of the first account in the underlying database cannot be modified , I just recorded the operation ; This record is done in memory ; When the second DML After the statement is executed successfully , Synchronize with the data in the underlying database file . If article 2 DML Statement execution failed , Clear all historical operation records , In order to accomplish the above functions, we must rely on the transaction

Transactional ACID nature  

1, Atomicity (Atomicity):
All operations after transaction starts , Or it's all done , Or not at all , It is impossible to stop in the middle link . An error occurred during transaction execution , It rolls back to the state before the transaction started , All the operations are as if they didn't happen . In other words, the transaction is an indivisible whole , It's like the atom you learned in chemistry , It is the basic unit of material composition .

2, uniformity (Consistency): Databases always transition from one consistent state to another . There is no intermediate state in the transaction . In general, consistency is a by-product of atomicity .

3, Isolation (Isolation):
Generally speaking , Changes made by a firm before final submission , It is invisible to other transactions . Why is it said here “ Generally speaking ” To modify it , We will describe the isolation level later , You'll know one or two .

4, persistence (Durability):
After the transaction is completed , Then its changes are permanently saved to the database . Even if the system crashes , The modified data will not be lost . Persistence is a bit of a fuzzy concept , Because in fact, there are many different levels of persistence . Some persistence policies can provide very strong security , Some may not . And it can't be done 100% The strategy of persistence guarantee based on ( If the database itself can achieve real persistence , How can backup increase persistence ? Right !).

Transactional ACID Features ensure that banks don't lose your money . It is very complicated to implement similar functions in application logic . Therefore, a lot of work is done in the database that users don't notice , That's the guarantee ACID Implementation of .

But at the same time , Like data lock granularity and other security functions , Transactions also increase system overhead , One has come true ACID Database for , The comparison is not realized ACID Database for , Usually a stronger one is needed CPU processing capacity , More memory and more disk space .

Isolation level of the transaction

* READ UNCOMMITTED( Read uncommitted ): Modifications in transactions , Even if not submitted , It is also visible to other transactions . Transactions can read uncommitted data , This is also known as dirty reading (Dirty
Read). This level can cause a lot of problems , In terms of performance Read
UNCOMMITED It's not much better than the other levels , But it lacks many of the benefits of other levels , Unless there's really a very necessary reason , It is seldom used in practical application
* READ COMMITTED( Read submitted ): The default isolation level for most database systems is READ COMMITTED( but Mysql no ).READ
COMMITTED Meet the simple definition of isolation mentioned earlier : When a transaction begins , can only “ seeing ” Changes made by submitted firms . let me put it another way , A transaction knows from the beginning before committing , Any changes you make are not visible to other transactions . This level is sometimes called non repeatability (nonrepeatable
read), Because the same query is executed twice , There may be different results .
* REPEATABLE READ( Repeatable ):REPEATABLE
READ Solved the problem of dirty reading . This level ensures that the results of reading the same record multiple times in the same transaction are consistent . But in theory , The repeatable read level still can't solve another phantom read (Phantom
Read) The problem of . The so-called illusory reading , It refers to when a transaction reads a range of records again , There will be illusions (Phantom Row).InnoDB
The storage engine is controlled by multi version concurrency (MVCC.Multiversion Concurrency Control) Solve the problem of unreal reading . Repeatable is Mysql Default transaction isolation level for
* SERIALIZABLE( Serializable ):
SERIALIZABLE Is the highest level of isolation . It forces the transaction to execute serially , It avoids the problem of "face to face" and "virtue unreal reading" . In short ,SERIALIZABLE Each row of data read will be locked , So it may lead to a lot of supermarket and lock competition problems . This isolation level is also used in practical applications , Only when it is necessary to ensure the consistency of the data and the data can be received without concurrency , This level should be considered .

Some terms for transactions

* Open transaction :Start Transaction
* End of transaction :End Transaction
* Commit transaction :Commit Transaction
* Rollback transaction :Rollback Transaction mysql> start transaction;# Start transaction manually mysql> insert
into t_user(name) values('pp'); mysql> commit;#commit You can then change the underlying database data mysql>
select * from t_user; +----+------+ | id | name | +----+------+ | 1 | jay | | 2
| man | | 3 | pp | +----+------+ 3 rows in set (0.00 sec)
Set transaction isolation level  

Mode 1
Can be in my.ini File transaction-isolation Option to set the default transaction isolation level for the server .

The value of this option can be :
– READ-UNCOMMITTED – READ-COMMITTED – REPEATABLE-READ – SERIALIZABLE • for example :
[mysqld] transaction-isolation = READ-COMMITTED
Mode 2
Set isolation level dynamically by command
• Isolation levels can also be set dynamically in the running server , Should be used SET TRANSACTION ISOLATION LEVEL sentence .
• Its grammatical pattern is as follows :
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
Among them <isolation-level> It can be : – READ UNCOMMITTED – READ COMMITTED – REPEATABLE READ
– SERIALIZABLE • for example : SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Scope of isolation level
• The scope of the transaction isolation level is divided into two types : – Global level : Valid for all sessions – Conversation level : Only valid for the current session • for example , Set session level isolation level to READ
COMMITTED : mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED; or : mysql>
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; • Set the global isolation level to READ
COMMITTED : mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
View isolation level
• The scope of the transaction isolation level is divided into two types : – Global level : Valid for all sessions – Conversation level : Only valid for the current session • for example , Set session level isolation level to READ
COMMITTED : mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED; or : mysql>
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; • Set the global isolation level to READ
COMMITTED : mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
 

Technology
©2020 ioDraw All rights reserved
Vue slot slot: Default slot , Named slot , Scope slot algorithm : Longest common subsequence ( Output all longest common subsequences ) Simple student achievement management system 《 How does the program run 》 Chapter 11 Hardware control method FDTD solution—— Material fitting , Light source settings MMKV The principle and implementation of virtual reality ( one )vue-element-admin Step record of adding menu python Simple understanding of decorator Java The realization of snake eating (1 Hour Society ) New photo album app launched by Microsoft : Using cross platform framework React Native to write