Database dirty read, non-repeatable read, Phantom read

Issues arising from transaction concurrency:

Dirty reads: One transaction reads data that is not committed by another transaction

Transaction 1: Updating a single piece of data
Transaction 2: Read record of transaction 1 update
Transaction 1: Commit commits are called
At this point, the data read by transaction 2 is data stored in database memory, called dirty reads.
The data read is dirty data
Detailed Explanation:
Dirty reading means: When a transaction is accessing the data, and the data has been modified, and this modification has not yet been committed to the database,
Another transaction accesses the data and then uses that data. Since this data is data that has not yet been submitted, the other
The data that the transaction reads is dirty, and the actions that are made based on dirty data may not be correct.

Non-repeatable READ: In the same transaction, read the same data two times, get different content

Transaction 1: Querying a record
; Transaction 2: Update records for transaction 1 queries
Transaction 2: Commit commits are called
Transaction 1: Querying the last record again
At this point, transaction 1 queries the same data two times, can get different content, called non-repeatable read

Phantom read: The same transaction, read two times with the same operation, get a different number of records

Transaction 1: Querying all records in a table
Transaction 2: Inserting a record
Transaction 2: Commit commits are called
Transaction 1: Querying all records in the table again
At this point the transaction 12 queries to the record is not the same, called the Phantom Read
Detailed Explanation:
Phantom reading is a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies the data in a table.
This modification involves all rows of data in the table. At the same time, the second transaction also modifies the data in this table, which is a change to a table
Inserts a new row of data into the Then, the user who operates the first transaction in the future will see that there are no modified rows of data in the table.
It was as if there was a hallucination.

The problem with the above isolation level is as follows:

There are five levels of transaction isolation:
Transaction_none does not use transactions.
transaction_read_uncommitted allow dirty reads.
Transaction_read_committed prevents dirty reads, the most common isolation level, and is the default isolation level for most databases
Transaction_repeatable_read can prevent dirty reads and non-repeatable reads,
Transaction_serializable can prevent dirty reads, non-repeatable reads, and Phantom reads (transactional serialization) to reduce database efficiency

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 levels.
such as: Con.settransactionisolation (Connection.repeatable_read);

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

1 Dirty read: Modified with an exclusive lock, until the transaction is committed to release, read with a shared lock, read release transaction 1 read the data with a shared lock (so that in the process of reading data in transaction 1, other transactions will not modify the data), do not allow anything to manipulate the data, only read, and then 1 if there is an update operation, It is then converted to an exclusive lock, and other transactions are not entitled to participate in read-write, which prevents dirty reads.

However, when the transaction 1 reads the data, it is possible that other transactions also read the data, after reading the shared lock release, the transaction 1 modifies the data, the completion of the commit transaction, the other transaction read the data again when the data is inconsistent, there will be non-repeatable read problems, so this can not avoid the non-repeatable reading problem.

2 non-repeatable READ: Read the data with a shared lock, write the data with an exclusive lock, all transaction commits to release the lock. Read time does not allow other things to modify the data, no matter how many times the data is read in the transaction process, the data is consistent and avoids the non-repeatable reading problem
3 Phantom reading problem: the use of the range lock RangeS ranges_s mode, the lock retrieval range is read-only, so as to avoid the Phantom reading problem, here is a description of the scope of the lock article

Database dirty read, non-repeatable read, Phantom read