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
2020 Nobel Prize in physiology or medicine announced Implementation and challenge of metadata service in data Lake Enterprises face SEM Bidding and SEO How to choose ? Or both ?spark.sql.shuffle.partitions and spark.default.parallelism The difference between JavaScript Do a simple guess number games What are the types of variables ? Trump's "VIP therapy ": Is receiving a drug treatment that has not yet been approved ( Essence )2020 year 6 month 26 day C# Class library DataTable( Extension method ) program ( process ) How is it stored in the operating system , Space allocation Understanding neural network machine translation in three minutes