Dirty read, phantom read, non-repeatable read and lost update instances

On June 5, 2017, the weather was rainy.

When I was sorting out my previous study notes two days ago, I found that the concepts of dirty reads, phantom reads, non-repeatable reads and lost updates were a bit vague about the problems caused by transaction concurrency, so I reviewed them again, and now I have summarized some of my understandings as follows for everyone’s convenience.

Locks are a means to prevent other transactions from accessing specified resources. Locks are the main method to achieve concurrency control and are an important guarantee for multiple users to be able to manipulate data in the same database at the same time without data inconsistency. Generally speaking, locks prevent dirty reads, non-repeatable reads, and phantom reads.

1.Dirty Read——A transaction reads data that another transaction has not committed.

Detailed explanation: When a transaction is accessing data and modifying the data, but the modification has not yet been submitted to the database, another transaction also accesses the data and then uses the data. Because this data has not yet been committed, the data read by another transaction is dirty data, and the operations based on the dirty data may be incorrect.

Transaction T1: Update a piece of data
-->Transaction T2: Read the record updated by transaction T1
Transaction T1: Call commit to submit
At this time, transaction T2 reads Data is data stored in database memory, which is called dirty data. This process is called dirty reading.

Dirty reading occurs when one transaction A reads data that has been modified by another transaction B but has not yet been committed. If B rolls back, transaction A reads invalid data. This is similar to a non-repeatable read, but the second transaction does not need to commit.

Solve the dirty read problem: Add an exclusive lock when modifying, and release it after the transaction is committed. Add a shared lock when reading. After reading, release transaction 1 and add a shared lock when reading data (in this way, after the transaction 1 During the process of reading data, other transactions will not modify the data), no transaction is allowed to operate on the data, it can only be read. After 1, if there is an update operation, it will be converted into an exclusive lock, and other transactions will have no rights. Participate in reading and writing, thus preventing dirty read problems. However, when transaction 1 is reading the data, it is possible that other transactions have also read the data. After the reading is completed, the shared lock is released. At this time, transaction 1 modifies the data. After the modification is completed, the transaction is submitted. When other transactions read the data again, the data is found. If they are inconsistent, non-repeatable read problems will occur, so this cannot avoid non-repeatable read problems.

2.Phantom reading (Phantom)——In the same transaction, the same operation is used to read twice, and the number of records obtained is different.

Detailed explanation: Phantom reading refers to a phenomenon that occurs when transactions are not executed independently. For example, the first transaction modifies the data in a table, and this modification involves all the data in the table. OK. At the same time, the second transaction also modifies the data in this table. This modification inserts a row of new data into the table. Then, in the future, the user who operates the first transaction will find that there are still unmodified data rows in the table, as if a hallucination has occurred.

Transaction T1: Query all records in the table
-->Transaction T2: Insert a record
-->Transaction T2: Call commit to submit
Transaction T1: Query the table again All records in
             
At this time, the records queried twice by transaction T1 are different, which is called phantom reading.

Note: Phantom reading focuses on adding or deleting.

Phantom reading occurs when two identical queries are executed, and the result set returned by the second query is different from the first query.

What happens: No scope lock.

How to avoid: Implementing serialization isolation mode may occur in any low-level isolation.

Solving the phantom read problem: The range lock RangeS RangeS_S mode is used to lock the retrieval range as read-only, thus avoiding the phantom read problem.

3.Nonrepeatable Read——In the same transaction, the same data is read twice and the content is different.

Transaction T1: Query a record
-->Transaction T2: Update the record queried by transaction T1
-->Transaction T2: Call commit to submit
Transaction T1: Query again The last record
           
At this time, transaction T1 queried the same data twice, and the obtained content was different, which is called non-repeatable read.

Note: The focus of non-repeatable reading is modification.

In the lock-based parallel control method, if a read lock is not added when executing select, a non-repeatable read problem will occur.

In the multi-version parallel control mechanism, when a transaction that encounters a commit conflict needs to be rolled back but is released, a non-repeatable read problem will occur.

There are two strategies to prevent this problem from happening:

(1) Postpone the execution of transaction 2 until transaction 1 is committed or rolled back. This strategy applies when using locks.

(2) In multi-version parallel control, transaction 2 can be submitted first, while transaction 1 continues to execute on the old version of data. When transaction 1 finally attempts to commit, the database will check whether its result is the same as when transaction 1 and transaction 2 were executed sequentially. If yes, transaction 1 is submitted successfully; if not, transaction 1 will be rolled back.

Solve the non-repeatable read problem: add shared locks when reading data, add exclusive locks when writing data, and release the locks only after transaction submission. No other thing is allowed to modify the data when reading. No matter how many times the data is read during the transaction, the data is consistent, avoiding the problem of non-repeatable reading.
4.Lost Update (Lost Update)

Transaction T1 reads the data, performs some operations, and then updates the data. Transaction T2 also does the same thing, so when T1 and T2 update data, they may overwrite each other's updates, causing errors.

5. To deal with the above isolation level issues, use the following method:

Five levels of transaction isolation:
(1) TRANSACTION_NONE does not use transactions.
(2) TRANSACTION_READ_UNCOMMITTED allows dirty reads.
(3) TRANSACTION_READ_COMMITTED prevents dirty reads, the most commonly used isolation level, and is the default isolation level for most databases.
(4) TRANSACTION_REPEATABLE_READ can prevent dirty reads and non-repeatable reads.
(5) TRANSACTION_SERIALIZABLE can prevent dirty reads, non-repeatable reads and phantom reads, which (transaction serialization) will reduce the efficiency of the database.

The above five transaction isolation levels are static constants defined in the Connection interface. Use the setTransactionIsolation(int level) method to set the transaction isolation level.

For example: con.setTransactionIsolation(Connection.REPEATABLE_READ).

Note: The isolation level of a transaction is limited by the database. The isolation levels supported by different databases are not necessarily the same.

The above is the detailed content of Dirty read, phantom read, non-repeatable read and lost update instances. For more information, please follow other related articles on the PHP Chinese website!