The problem of repeated data insertions before the line, by troubleshooting the problem and the default isolation level of MySQL-repeatable read (can be reread) , repeatable read ensures that multiple instances of the same transaction will see the same data row when concurrently reading the data. Now through the experiment, the problem is analyzed.
1. Turn on transaction A at Terminal A, and check.
TRANSACTION; Select spt.id,spt.audit_status,spt.is_deleted from 5;
The results are as follows:
2. Turn on transaction b at Terminal B and make the same query, and the result is the same as in transaction A.
TRANSACTION; Select from 5;
3. In transaction A, update, update the id=3 audit_status to 3, query, find the update succeeds, and then commit transaction A;
Update set audit_status=3, is_deleted=0where id= 3;
Select spt.id,spt.audit_status,spt.is_deleted from stat_point_task SPT limit 5;
Commit
4. At this point, transaction a updates the data, commits it, returns to transaction B, queries in transaction B, and finds the data before the transaction a update.
/* query again, because repeatable reads, the discovery of the query to the data before the transaction a update */ Select from 5;
The above-mentioned default isolation level for MySQL is explained by experiments, but at this point I have a question as to whether transaction B updates on the basis of the update of transaction A, or on the basis of the pre-update (and the data obtained by the query B). The following experiment is done to analyze this problem.
5. Update the data in transaction B, assuming that the update of a affects transaction B, the data in the query results of transaction a corresponds to Audit_status of 3, and that statement in transaction B should have a id=3 of 0 and will not set Audit_status to 4.
Update set audit_status=4where id=3 and Audit_status= 1; Select from 5;
After the update, the query again, found that the id=3 data corresponding to the audit_status is still 1, indicating that the update of transaction a affects transaction B.
We re-verify with the following statement that the update to transaction B has affected transaction B when it is updated, and that the update submitted in transaction A has updated Id=3 's audit_status to 3, although the audit_ of the id=3 that is queried in transaction B because it is repeatable read The status is 1, but the update was successful when the following update was made in transaction B, and the Audit_status was successfully updated to 5.
Update set audit_status=5where id=3 and Audit_status= 3; Select from 5;
In conclusion,
Because of the repeatable read of MySQL, when a query is made to transaction B, the update submitted by transaction A does not affect transaction B.
When an update is made to Transaction B, however, the update submitted by transaction a affects transaction B.
-------------------------------------------
If there is any mistake, please correct me and appreciate it.
-------------------------------------------
MySQL repeatable read mining pit record-when transaction B is updated, the update submitted by transaction A will not affect transaction B