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