<>1,INNODB Transaction principle
* affair （Transaction） It is one of the important characteristics that distinguish database from file system , A transaction transforms a database from one consistent state to another .
* On database commit , You can ensure that either all changes have been saved , Or all changes are not saved .
<>2, Characteristics of transactions
* Atomicity (Atomicity)： All operations of the whole thing are submitted successfully , Or all failed and rolled back ( There will be no partial implementation ).
* uniformity (Consistency)： Several transactions executed in parallel , The execution result must be consistent with the result of serial execution in a certain order .
* Isolation (Isolation)： The execution of a transaction is not interfered by other transactions , The intermediate results of transaction execution must be transparent to other transactions .
* persistence (Durability):
Once a transaction has been committed , Changes to the data in the database are permanent , Even in the case of database system failure, the operation of committing transaction will not be lost .
<>3, Transaction isolation level
3.1 Uncommitted read : Dirty reading （READ UNCOMMITTED）
1） affair 2 The data queried is a transaction 1 Data modified but not submitted in , But because of business 1 Rolled back the data
2） So business 2 The query data is incorrect , So there is a problem with dirty reading .
3.2 Submit for reading : Non repeatable （READ COMMITTED）
1） affair 2 implement update Statement but before committing , affair 1 The first two of select The operation returns the same result .
2） But business 2 implement commit After operation , affair 1 The third of select The operation reads the transaction 2 Changes to data .
3） Leading to the first two select The operation returns different data , So there is a problem of non repeatable reading .
3.3 Repeatable : Illusory reading （REPEATABLE READ）： This is MySQL Default transaction isolation level for
1） Each instance opened by transaction , A version number is assigned to it , If the read data row is being executed by another transaction DELETE or UPDATE operation （ That is, there is an exclusive lock on the bank ）
2） At this time, the read operation of the object will not wait for the lock on the row to be released , Instead, the snapshot data of the row is read according to the version number （ Recorded in undo log in ）
3） such , The query operation in the transaction returns data under the same version , Solve the problem of non repeatable reading .
4） Although this isolation level solves the problem of non repeatable reading , But in theory it leads to another problem ： Illusory reading （Phantom Read）.
5） A transaction is in the process of execution , Another change to an existing row of data ,MVCC The mechanism can ensure that the content of the original data row read by the object is the same
6） But it does not prevent another transaction from inserting a new row of data , This leads to extra rows of data in the object , It's like a phantom reading , This is the problem of illusory reading .
3.4 Serially readable （SERIALIZABLE）
1） This is the highest isolation level for transactions , By forcing transaction ordering , Make it impossible to conflict with each other , It is to add a shared lock to each read data row
2） Under this isolation level , Can solve the previous dirty reading , The problem of nonrepeatable reading and unreal reading , But it can also lead to a lot of overtime and locking , Generally not recommended
<>4, Terms about transactions
Open transaction ：Start Transaction
End of transaction ：End Transaction
Commit transaction ：Commit Transaction
Rollback transaction ：Rollback Transaction
<>5, Marks for the start and end of a transaction
5.1 Open sign
* Any one of them DML sentence (insert,update,delete) implement , Marks the start of a transaction
5.2 End mark
* Submit ： A successful ending , Will all DML Statement operation history and underlying hard disk data to synchronize
* RollBACK ： The end of failure , Will all DML Statement operation history all cleared
<>6, Transaction and database underlying data
In the process of things , Before the end ,DML Statement does not change the underlying data , Just record the history , Record in memory . Only when things are over , And it's the end of success , The data in the underlying hard disk file will be modified
<>7,mysql Transaction commit and rollback
stay MySQL in , By default , Transactions are committed automatically , in other words , Just execute one DML Statements open things up , And the transaction was committed
The auto commit mechanism can be turned off
Yes t_user Perform commit and rollback operations
Submit operation ( Transaction successful )
start transaction
DML sentence
commit
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
rowsin set (0.00 sec)
Rollback operation ( Transaction failed )
start transaction
DML sentence
rollback
mysql> start transaction; mysql> insert into t_user(name) values('yy'); mysql>
rollback; mysql> select * from t_user; +----+------+ | id | name | +----+------+
| 1 | jay | | 2 | man | | 3 | pp | +----+------+ 3 rows in set (0.00 sec)
Technology