About Mysql transactional

1》What does transaction refer to?
         A transaction can be regarded as a business logic processing unit, this unit is either executed or not executed;
2》ACID principle:
 (1) Atomicity(Automicity)
 (2) Consistency (Consistency)
A account has 3,000 yuan, B account 2000 fast,
-A --500-"B
A account and B account is 5000 fast
Before the implementation and after the execution , this sum and this state should be consistent.
(3) isolation (LSOLATION)
A account from 3000-"500-& gt; B account 2500
update (1) Transfer of transfers
select Sun () a b Jianhe
(4) Durability
Guaranteed persistence:
1 & gt before handling:
Crossing the data to the disk before. What if you want to withdraw it? Data recalled from disk? Very slow? it's painful? what to do?
2 & gt; combined with the transaction aspiration to complete the
things log, there is also a disk on the disk. What is the difference between it and the data file?
            Difference: The transaction log generates sequential I/O, while the disk data file is random I/O. Each operation of the transaction log is written sequentially to a continuous storage block on the disk, and our data file it's not true.
(1) Things Log: Sequence IO
(2) Data file: Random Io
Write the transaction into the transaction log. After a while, the background or related process of the thing will be Implemented into data files, this way we ensure the persistence of the data operated by things.

3》The status of the transaction.
     (1) Either an active transaction
         The transaction is being executed
 (2) Partially committed transaction
             We know that the transaction is either executed or not executed , what is partial submission?
          That is, the transaction has been executed, part of it has been written to the disk, and the other part is being executed, and the last execution statement is in the process of being written. We call it partial submission
        (3) Failed transaction
                The transaction is normal Submitted, but did not achieve the purpose, it is called a failed transaction
       (4) Aborted transaction
               Aborted transaction is called not submitted, or it ends prematurely halfway, which is called abort.
(5) The transaction submitted
Health submitted by the health, our affairs must be a certain middle of these 5 states. Once the transaction is submitted, it cannot be revoked.

4 "Equipment and scheduling of the transaction
Affairs:
1 & gt; increase throughput and resource utilization rate
2 & gt; reduce waiting time
Affairs to reduce each other Between the effects, we need to isolate the transaction, what if it is isolated? This depends on transaction scheduling. Any database system itself has such complexity in transaction scheduling. The scheduling method integrates many strategies to complete the scheduling. Generally speaking, in order to ensure the isolation and atomicity of transactions, they need to use transaction scheduling. To complete, there are two general strategies for scheduling:
​

transaction scheduling:
1 & gt; recovery scheduling
Generally, when the two transactions are scheduled between each other, the cross execution of any two transactions will not lead to the change of another transaction. We Call it as: recovery scheduling

# 2 & gt; No -level joint adjustment
to restore scheduling and step by step means that after the end of the transaction, they have no impact on each other, especially in affairs, in affairs, in affairs It will still be affected when rolling back, and it will still be affected when the transaction is rolled back. Therefore, in order to avoid the impact during rollback, you can also implement what is called: cascade-free scheduling.

5》Isolation of Transactions
We know about transaction scheduling. A transaction may involve multiple operations. These operations can be cross-executed. The existence of transaction scheduling is to better schedule the order so that these cross-executions can be interconnected with each other. It will not have an excessive impact, so in order to further reduce the impact between transactions, we have so-called isolation levels. There are four isolation levels for transactions. Which four?
1 & gt; Read Read UNCOMMITTED
at the isolation level. All transactions can see the execution results of other unprepared transactions. How much is the level? Reading the unsurmitted data is also becoming: Dirty Read (Dirty Read)
2 & gt ), which satisfies the simple definition of isolation: a transaction can only see changes made by transactions that have been committed; When multiple instances read data concurrently, they will see the same data rows. However, in theory, this leads to another problem: phantom read (Phantom read). Simply put: phantom read refers to reading a certain range as a user. When the data row is in this range, another transaction inserts a new row in this range. When the user reads the data row in this range, he will find a new "phantom row"
. Innodb and Falcon engines use multi-version concurrency control (MVCC) mechanism solves this problem.

4 & GT; Serial serializable
This is the highest -level isolation mechanism. It can conflict with each other through mandatory transactions, so as to solve the problem of illusion reading. Simply put: it is in A shared lock is added to each read data row. At this level, it may lead to a large number of timeouts and lock competition. Concurrency control is implemented, and our real-time concurrency control

                        (1). Lock. Read lock, write lock, exclusive lock, shared lock.
                                                                                                                                               . Timestamp, the start time and execution time of each transaction must be recorded
#              When a transaction applies for a type of lock, in order to achieve concurrency control, it needs this lock, but due to the results of transaction scheduling, it has never been able to obtain the lock. The result is:
                                                                                         . In addition, there is another kind of lock called: deadlock

6》Start transaction and rollback

Generally speaking, the operation of starting a transaction:　　　　　　　　　
                                                                                                                                                                                                                      Start transaction 2>Delete information in one of the student tables
　　　　delete from student where num='4'
​ ​ 3>How to perform data recovery? Transaction rollback. Guessed it.
                                                                                                                                                                                                                                       
#                                                                                                                              select * from student;
                                                                                                           3>Transaction submission
                        commit ;
          4>Try to rollback again
                                                                                                                  rollback
                                                                select * from student; At that time, I found that the 75th one was wrong and the execution was wrong. Do I need to roll back the first 80? What should we do at this time? The role of save points comes out. For example: I save every 10 operations: ​​​Affairs
Start Transaction;
2 & GT; Delete the information of "Cuihua" in one Student table
Delete from Student Where Name = 'Cuihua'
Save a transaction point
Savepoint yya
3 & gt; Delete the information of "Ximen Blog" in one of the Student tables

Savepoint yyb
4 & GT; "Two" Information
                    delete from student where name='niuer'
                               savepoint                   ’’ ’     through ’s ’’s ’ through ’’s' through ’ through through through through through‐‐‐‐‐‐‐‐ ‐ to 5>                             　　　　rollback to yya Think about it. What is the save point yya? ​ ​
​ ​ ​ 6>Check whether the recovery is successful
​ ​ ​ ​ ​select * from student;

6.3 Automatic submission of transactions
1 & gt; Query mysql transaction automatically submit
SELECT @@ AutoCommit;
MySQL & GT; Select @@ AutoCommit;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----+
                                                                                                                                          ------+
                                         1 row in set (0.00 sec)

                                                                                                               >Test Verification
Delete from student where name = 'ximenchuniu'
Rollback;
Does it return? And we didn't explicitly start a transaction? Transactions support DML statement rollback. DDL is a hidden test commit, so unfortunately it is not supported.

Verify that read is uncommitted:
                                                                                                                                                                          together  together  together  out    Query the isolation level select @@tx_isolation; Affairs
Window One Window Two Common Operation:
1.Select @@ tx_isolation;
2.Set tx_isolation = 'Read_uncommittd';

1.start transaction; Start a cross transaction
        2.update student set age='70' where d_id='1010';

Window 2 operation
        1.start transaction;  Start a cross transaction
2.Select * from Student; You can see that the 1 window is changed to 70, and the window transaction is not submitted. It is seen by 2, which is called

verification read and submitted
, open In the two windows, the two parties crossed a transaction
window 1 and window 2 common operation:
1.Select @@ tx_isolation;
2.Set tx_isolation = 'read_committd'
## window window 1 Operation:
1.start Transaction; Start a cross -crossing transaction
2.Update Student Set Age = '70 'where d_id =' 1010 ';

2 Operation
1. Start Transaction; Start a cross transaction
2.Select * from Student; Updated data
3.Select * from Student; When a window A executes 3 steps, you can see the update data and avoid dirty reading

Verification can be re -read
A MySQL, open two windows, and the two parties crossed a transaction
Phantom Reading:
definition: the same transaction T1, in two different time periods, such as 1 2 execution, execute On the same day, the query statements get the records of the records are differently called Phantom Reading

Window 1 and Window 2 Common operation:
1.Select @同 同 同 同 同 同';

window 1 Operation
1.start transaction; Start a cross -crossing transaction
2.Update Student Set Age = '60' where num = '10 ';
3.Commit

2 Operation
1. Start Transaction; Start a cross transaction
2.Select * from Student; ; 1commit;Still not see the updated data in window 1
In the two windows, the two parties cross the business of a transaction
两 1 and window 2 common operation:
1.Select @@ tx_isolation;

## 两1 operation
                1.start transaction; start a cross transaction
              2.update student set age='60' where num='10'; cannot be executed. It can only be executed after Window 2 submits the operation!

2 Operation
1.start Transaction; Start a cross -crossing transaction
2.Commit; Submit here, the Update of the 1 window can be successfully executed. This is serializable!
Reason:
Two transactions to cross the same data at the same time, one party must submit first. Otherwise, reading and writing will block each other.

The above is the detailed content of About Mysql transactional. For more information, please follow other related articles on the PHP Chinese website!