preface

What problems can occur when multiple transactions are executed concurrently?

As we know, SQL is executed to query or modify data in the buffer pool. What happens if multiple transactions update a row at the same time?

1. Write dirty

When transaction A and transaction B update the same row at the same time, transaction A updates first, and transaction B updates later.

At this point, undo log will record the old value of the data changed by transaction A, assuming that the old value is NULL. Transaction B then updates the row, overwriting the value of A’s update. When transaction A suddenly rolls back, it will roll back according to its Undo log.

Transaction A is rolled back, and the value of this data becomes the null value before the update.

However, transaction B is unaware of this and finds that its updated value is missing. This is dirty writing.

Essentially, one transaction modifies the value of another uncommitted transaction (which can be rolled back), resulting in potentially inconsistent data.

2. Dirty reads

There is also transaction A and transaction B. Transaction A updates A row of data, and transaction B happens to query the row, so transaction B gets the value that A updates.

After transaction B gets the value, it goes to the business system for various business logic processing, etc. At this time, transaction A suddenly rolls back, and then rolls back the undo log value to the data line. Transaction B then queries the row again and finds that the values are different. This is dirty reading.

In essence, one transaction queries the value of another uncommitted transaction, resulting in potentially inconsistent data.

3. Do not repeat the read

While avoiding dirty reads, there is also the possibility of non-repeatable reads.

Under what circumstances does this happen?

Assume that transaction B is updating A row, but has not committed it yet. Transaction A cannot read the row during the uncommitted time. Transaction A cannot read the value it changed until transaction B commits. This way you can avoid dirty reading.

In this case, it is assumed that transaction A first queries the value of A.

Transaction B changes the value of the row to B and commits the transaction immediately. Transaction A has not committed the transaction and conducts A second query during the execution of the transaction, so the value of the second query from transaction A is the value of B.

Transaction C then updates the data to the C value again and commits the transaction. In this case, transaction A conducts A third query without committing the transaction and finds the value C.

Non-repeatable reads are the case where transaction A has not committed A transaction and may read different data each time.

From the above analysis, it is repeatable and well understood. Transaction A is expected to read the value of A every time.

4. The magic to read

Suppose transaction A needs to query the data in batches for several times, and ten data are queried for the first time

Transaction B inserts a few rows into the table, and transaction B commits the transaction, so there will be a few more rows

Then when transaction A conducts the query again, transaction B commits, resulting in several more pieces of data in transaction A query

So there is and query the first time did not see the data, is illusory.

In essence, a transaction executes multiple queries using the same SQL, each time finding new data.