MySQL lock problem

1. Dirty Reading

Dirty pages are only pages that have been modified in the buffer pool but are not flushed to disk, that is, the pages in the database instance memory are inconsistent with the pages in the disk, of course, the log has been written to the redo log file before it is flushed to disk, and the so-called dirty data refers to the transaction's modification of the row record in the buffer pool

It is very normal for the dirty page to read. Dirty pages are caused by the database instance memory and disk asynchrony, which does not affect the consistency of the data (or the two end up being consistent when dirty pages are flushed back to disk). And because the dirty page refreshes asynchronously, does not affect the database usability, has brought the performance enhancement

Dirty data is very different, dirty data is uncommitted data, if you read dirty data, that is, one transaction can read the uncommitted data in another transaction, it is clearly against the isolation of the database

Dirty reads are values under different transactions, the current transaction can read to another transaction uncommitted data, which is simply to read dirty data.

The transaction isolation level in table T has a default RR changed to read uncommited, so in session a, the two select operation in session B yielded different results when the transaction was not committed, and 2 this record was not committed in session a, which resulted in dirty reads. Transaction Isolation violation

Dirty read isolation may seem useless, but in some more special cases you can set the transaction isolation level to read UNCOMMITTED. For example, the slave node in the replication environment, and the slave query does not require a particularly accurate return value

2. Non-repeatable reading

Non-repeatable reading refers to reading the same data collection multiple times within a transaction, and when the transaction is not finished, another transaction accesses the same data set and does some DML operations, so that between two data in the first transaction, due to the modification of the second transaction, The data that the first transaction reads two times may not be the same. This occurs when the data that is read two times within a transaction is different, called non-repeatable read

The difference between non-repeatable reads and dirty reads: Dirty reads are READ UNCOMMITTED data, and non-repeatable reads are data that has already been committed, but it violates database conformance requirements

Start a transaction in session A, the first read to 1, another session B start another transaction, insert a 2 record, before the commit, the transaction in session a read again is, read the record is 1, no dirty read phenomenon, but in session B after the transaction commits, When the transaction in session a reads, this is read 1 and 2 of these two records, the premise of this example is that the transaction isolation level of session A and conversation B is RC

In general, non-repeatable reads are acceptable because they read data that has already been committed and do not pose a major problem, so many database manufacturers set their transaction isolation level to RC by default, allowing non-repeatable reads at this isolation level

In the InnoDB storage engine, through the Next-key lock algorithm to avoid the problem of non-repeatable reading, in the official MySQL document, the non-repeatable read problem is defined as Phantom problem, the Phantom problem. Under the Next-key lock algorithm, the scan of an index is not only locked in the scanned index, but also locks the range of gaps covered by these indexes, so the insertion within this range is not allowed, thus avoiding the problem of non-repeatable reads caused by another transaction inserting data within this range. Therefore, the default transaction isolation level for the InnoDB storage engine is RR, using the Next-key lock algorithm to avoid the non-repeatable read phenomenon

3. Missing updates

A missing update is a problem that is caused by another lock, which simply means that the update operation of one transaction is overwritten by an update operation lock on another transaction, which results in inconsistent data, such as

Transaction T1 updates the row record R to V1, but the transaction T1 is not committed at the same time, transaction T2 updates row record R to v2, transaction T2 UNCOMMITTED transaction T1 COMMIT TRANSACTION T2 Commit

However, under any isolation level of the current database, there is no theoretical loss of database update issues. This is because, even though the transaction isolation level of READ uncommitted, for a row DML operation, it is necessary to lock the row or other coarse-grained objects, so in step b above, transaction T2 does not update the row record R, the rest is blocked until the transaction T1 commits

Although the database can prevent the loss of update problems, but in the production application there is another logical meaning of the lost update problem, and the problem is not caused by the database itself. In fact, this problem can occur in all multi-user computer system environments. Simply put, the following situation occurs and a missing update will occur

A.    The transaction T1 queries a row of data, puts it into local memory, and displays it to an end user, user1b.    The transaction T2 also queries the row data and displays the obtained data to the end user user2c.    User1 Modify the record of this row, update the database and submit D.    User2 Modify the record of this line, update the database and submit

Obviously, the user User1 update operation in this process will be lost, and this may lead to a horrible result, the assumption that the bank has lost the update phenomenon. For example, a user account has 10 000 RMB, and he uses two internet banking clients to transfer funds separately. The first transfer 9000, because the network and data relationship, this need to wait, but this is the user operation of another online banking client, transfer 1 yuan, if both operations are successful, the user's balance should be 9999, the first turn of 9000 has not been updated, But the other account in the transfer was 9000, which resulted in more money and uneven accounting. Perhaps the reader will say, no, my net silver is USB key, this will not happen, yes, login via USB key may solve the problem, but the most important thing is to solve this problem at the database layer, to avoid any possible update situation

To avoid the loss of an update, you need to serialize the transaction in this case, rather than parallel operations, in 1 of the steps above, add an exclusive x lock to the record read by the user, and similarly, in step 2, the user also needs to add an exclusive x lock, in this way, Step 2 must wait for 1 and step 3 to complete, and finally complete step 4

MySQL lock problem