MySQL repeatable read mining pit record-when transaction B is updated, the update submitted by transaction A will not affect transaction B

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.