The classic example of a transaction is a transfer . If you want to give your friend Lao Wang 100 yuan , Cary was just a hundred .
Operations involved to query balance , Addition and subtraction , Update balance must be integrated .
Otherwise, after the procedure is checked , Before the reduction , Yours 100 Yuan , You can check it again by this time difference , Then transfer the money to another friend .
So there's a need for business . Ensure a series of database operations , live and die together !
And in the MySQL in , Transactions are implemented in the engine layer . We'll take 5.5 Post default InnoDB Engine as an example to explain this article .
<>1 Isolation and isolation level
Transactional ACID Of I - “ Isolation ”.
When multiple transactions are executed simultaneously on the database , May appear
* Dirty reading （dirty read）
* Non repeatable （non-repeatable read）
* Illusory reading （phantom read）
In order to solve these problems , That's it “ Isolation level ”. The more isolated , The lower the efficiency . Therefore, we should find a balance between the two according to the business needs .
SQL The standard transaction isolation levels are as follows ：
* Read uncommitted （read uncommitted）
A transaction has not been committed , Its changes can be seen by other transactions
* Read submitted （read committed）
After a transaction is committed , The changes will only be seen by other transactions
* Repeatable （repeatable read）
Data seen during the execution of a transaction , Always consistent with the data that the transaction sees at startup .
Yes, of course , Uncommitted changes are not visible to other transactions . When a transaction starts , Be able to see the results of all committed transactions . But after that , During the execution of this transaction , Updates to other transactions are not visible to it
* Serialization （serializable ）
For peer records ,“ write ” plus “ Write lock ”,“ read ” plus “ Read lock ”.
When a read-write lock conflict occurs , A post accessed transaction must wait for the previous transaction to complete
What if tables T There is only one column in , One row value 1, Here is the behavior of executing two transactions in chronological order .
mysql> create table T(c int) engine=InnoDB; insert into T(c) values(1);
* The act of executing two transactions in chronological order
Under different isolation levels , affair A What are the different return results , It's in the picture V1,V2,V3 What are the return values of
* “ Read uncommitted ”
V1,V2,V3 The values of are all 2.
* “ Read submitted ”
V1 yes 1,V2,V3 The value of is 2.
* “ Repeatable ”
V1,V2（ Transaction during execution , Before submission , The data you see must be consistent .） yes 1,V3 yes 2.
* “ Serialization ”
affair B implement “ take 1 Change to 2” Time , It's going to be locked up . Until business A After submission , affair B（ Post access transactions ） To continue .
So from A From the perspective of , V1,V2 The value is 1,V3 The value of is 2.
In terms of implementation , A view will be created in the database , When accessing, the logical result of the view shall prevail .
* “ Repeatable ”
The view is created when the transaction starts , This view is used throughout the lifetime of the transaction .
* “ Read submit ”
View in each SQL Created when the statement begins execution .
* “ Read uncommitted ”
Directly returns the latest value on the record , There is no view concept
* “ Serialization ”
Lock directly to avoid parallelism （ meanwhile , Pay attention to the non concurrent same time period ） visit .
Oracle The default isolation level for the database is “ Read submit ”, So for some from Oracle Migrate to MySQL Application of , To ensure the consistency of database isolation level , Remember to MySQL The isolation level of is set to “ Read submit ”.
<>2 collocation method
Will start the parameter transaction-isolation The value of is set to READ-COMMITTED. You can use it show variables To see the current value .
* show variables like 'transaction_isolation'
Each isolation level has its own usage scenarios , It has to depend on the business .
<>3 When to use “ Repeatable ”
Suppose you are managing a personal bank account table . A table keeps the balance at the end of each month , A table holds the bill details .
At this time, you have to do data proofreading , That is to judge the difference between the balance of last month and the current balance , Is it consistent with the bill details of this month .
You must hope that , Even if a new transaction occurs to a user , It doesn't affect your proofreading results .
Use it at this time “ Repeatable ” Isolation level is very convenient .
The view at the start of a transaction can be considered static , Not affected by other transaction updates .
<>4 Implementation of transaction isolation
stay MySQL in , In fact, each record will record a rollback operation when it is updated .
The latest value on the record , By rolling back the operation , You can get the value of the previous state .
<>4.1 Examples
Let's assume that a value changes from 1 It was changed in order to 2,3,4
* Records in the rollback log .
Current value 4, But when querying this record , Starting a transaction at different times is different read-view.
In view A,B,C, The values of this record are 1,2,4, Multiple versions of the same record can exist in the system , It is multi version concurrency control of database （MVCC）.
Yes read-view A, To get 1, You must perform all the rollback operations in the graph to get the current value .
Even if there is another transaction now 4 Change to 5, This business follows read-view A,B,C The corresponding transaction will not conflict .
<> When to delete a rollback log
Delete when not needed . That is, the system will judge itself , These rollback logs are no longer needed when there are no transactions , The rollback log is deleted .
<> When not needed ?
When there is no earlier rollback log in the system read-view Time .
<>5 Avoid long business
Long transactions mean that there will be very old transaction views in the system . Because these transactions can access any data in the database at any time , So before the transaction is committed , In the database, all possible rollback records must be kept , Resulting in a large amount of storage .
stay MySQL 5.5 And before , The rollback log is placed with the data dictionary ibdata file , Even if the long transaction is finally committed , The rollback segment is cleaned up , Files don't get smaller .
In addition to the impact on the rollback segment , Long transactions also occupy lock resources , May slow down the whole database .
<>6 Transaction start mode
The development of students is not intended to be long affairs , Often misused .
MySQL There are several ways to start a transaction ：
<>6.1 Explicitly start a transaction
begin or start transaction. Matching
* Commit statement commit
* rollback statement rollback
<>6.2 set autocommit=1
set autocommit=0
Turn off auto commit for this thread . If you only execute one select, The transaction starts , It will not be submitted automatically .
The transaction will continue , Until the initiative commit or rollback, Or disconnect .
Some client connection frameworks will execute the connection first after the connection is successful
set autocommit=0
Causes the next query to be in the transaction , If it's a long connection , This leads to unexpected long transactions .
Therefore, it is recommended to use
set autocommit=1
Explicitly start a transaction .
Frequent transaction business , In the second way, each transaction does not need to be actively executed once at the beginning “begin”, Reduce the number of statement interaction . If you have this concern , Recommended use commit work
and chain.
autocommit by 1 Time , use begin Explicitly initiated transactions , If implemented commit, Then commit the transaction .
If implemented commit work and
chain, Commit the transaction and start the next transaction automatically , Omit execution begin Statement overhead . From the perspective of program development, we can also know whether each statement is in a transaction .
<>7 Query long transaction
information_schema Library's innodb_trx Query long transaction in table
* Search duration exceeds 60s Business of . select * from information_schema.innodb_trx where
TIME_TO_SEC(timediff(now(),trx_started))>60
<>8 Daily preparation V.S thoughtful
The advantage is that “ Maximum recovery time ” Shorter .
* One preparation a day
In the worst case, it needs to be applied for a day binlog. such as , Every day 0 Click to do a full backup , And to recover one until last night 23 Point backup
* One week for standby
In the worst case, it's going to take a week binlog La !
The corresponding index of the system is RTO（ Recovery target time ）.
Of course, there is a cost , Because more frequent full backups consume more storage space , So this one RTO It's cost , Needs to be assessed against the business .
<>9 Avoid the impact of long transactions on business
<>9.1 Application development end
Confirm whether to use
set autocommit=0
Confirm that it can be in the test environment , hold MySQL Of general_log open , Run a business logic , adopt general_log confirm .
If you set this value , Parameters are also provided to control , The goal is to change it to 1.
Confirm if there are unnecessary read-only transactions . Some frameworks don't care what statement comes first begin/commit frame . Some businesses don't need it , But there are several select Statement put into transaction . This kind of read-only transaction can be removed .
When the business connects to the database , Based on business estimates , adopt SET MAX_EXECUTION_TIME command , Controls the maximum execution time of each statement , Avoid unexpected execution of monolingual sentences for too long .
<>9.2 Database side
monitor information_schema.Innodb_trx surface , Set long transaction threshold , Call the police if it exceeds the limit / perhaps kill.
Percona Of pt-kill This tool is good , recommend .
In the business function test phase, it is required to output all the general_log, Analyze log behavior to discover problems in advance .
Used MySQL
5.6 Or a later version , hold innodb_undo_tablespaces Set to 2 Or greater . If a large transaction occurs, the rollback segment is too large , It is more convenient to clean up after setting .
Technology
Daily Recommendation