Dirty reads, Phantom reads, non-repeated reads, and loss of updates, lost

Dirty reads, Phantom reads, non-repeated reads, and loss of updates, lost

The weather is rain on January 1, June 5, 2017.

When I sorted out the previous learning notes two days ago, I found that the problems caused by transaction concurrency-dirty reads, Phantom reads, non-repeated reads, and lost updates were a bit vague, so I re-reviewed them again, now I will summarize some of my understandings as follows to facilitate your learning.

A lock is used to prevent other transactions from accessing specified resources. Locks are the main method for implementing concurrency control. They are an important guarantee that multiple users can simultaneously manipulate data in the same database without data inconsistency. In general, locks can prevent dirty reads, non-repeated reads, and Phantom reads.

1. Dirty Read: one transaction reads data not committed by another transaction.

Explanation: when a transaction is accessing the data and modifying the data, and the modification has not been committed to the database, another transaction also accesses the data, then the data is used. Because the data has not been committed, the data read by another transaction is dirty data, and the operations performed based on the dirty data may be incorrect.

Transaction T1: update a piece of data
--> Transaction T2: reads the updated records of transaction T1.
Transaction T1: Call commit for commit
At this time, the data read by transaction T2 is stored in the database memory, which is called dirty data. This process is called dirty read.

Dirty read occurs when 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 non-repeated reads, but the second transaction does not need to be committed.

Solve the dirty read problem: Apply an exclusive lock during modification, and release the lock only after the transaction is committed, after reading the data, release transaction 1 is added with a shared lock (in this case, other transactions will not modify the data when transaction 1 reads the data), and no transaction is allowed to operate on the data, it can only be read. If there is an update operation in the last 1, it will be converted to an exclusive lock, and other transactions are not authorized to participate in read/write operations. This prevents dirty read problems. However, when transaction 1 reads data, other transactions may also read the data. After reading the data, the shared lock is released. At this time, transaction 1 modifies the data and submits the transaction, when another transaction reads data again, it finds that the data is inconsistent, and the non-repeated read problem occurs. Therefore, the non-repeated read problem cannot be avoided.

2. Phantom)-- In the same transaction, read twice with the same operation, and the number of records is different.

Explanation: phantom read refers to a phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which involves all the data rows in the table. At the same time, the second transaction also modifies the data in this table. This modification inserts a new row of data into the table. In the future, the user who operates the first transaction will find that there are still data rows in the table that have not been modified, just like an illusion.

Transaction T1: Query all records in the table
--> Transaction T2: Insert a record
--> Transaction T2: Call commit for commit
Transaction T1: Query all records in the table again

At this time, the records queried by transaction T1 are different, which is called phantom read.

Note: The Phantom read focus is added or deleted.

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

Occurrence: no range lock.

How to Avoid: the serialization isolation mode may occur in any low-level isolation.

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

3. Nonrepeatable Read: The same data is Read twice in the same transaction and the obtained content is different.

Transaction T1: query a record
--> Transaction T2: update the records queried by transaction T1
--> Transaction T2: Call commit for commit
Transaction T1: query the last record again

At this time, transaction T1 queries the same data twice, and the obtained content is different, which is called non-repeated read.

Note: The Repeatable read focuses on modification.

In the lock-based parallel control method, if no read lock is added when select is executed, the non-repeated read will occur.

In the multi-version parallel control mechanism, when a transaction with a commit conflict needs to be rolled back but released, the non-repeated read issue will occur.

There are two policies to prevent this problem:

(1) postpone the execution of transaction 2 until transaction 1 is committed or rolled back. This policy is applied when the lock is used.

(2) In multi-version parallel control, transaction 2 can be committed first, while transaction 1 continues to run on the old version of data. When transaction 1 finally tries to commit, the database will check whether its results are the same as the sequential execution of transaction 1 and transaction 2. If yes, transaction 1 is committed successfully; if not, transaction 1 is rolled back.

Solve the problem of non-repeated read: Share lock is applied when reading data, and exclusive lock is applied when writing data. The lock is released only when the transaction is committed. When reading data, other things are not allowed to modify the data. No matter how many times the data is read during the transaction, the data is consistent, avoiding the issue of non-repeated reading.
4.Update loss(Lost Update)

Transaction T1 reads data, performs some operations, and updates the data. When T2. transaction T2.

5. Handle the isolation level issues as follows:

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

The preceding five transaction isolation levels are static constants defined in the Connection interface. You can use the setTransactionIsolation (int level) method to set the transaction isolation level.

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

Note: The transaction isolation level is limited by the database. Different databases support different isolation levels.