Database transaction Isolation Level (non-original)

Read behavior is a condition that can be encountered when multiple transactions are executing concurrently, while reading data. Understanding them first helps you understand the meaning of each isolation level. These include dirty reads, non-repeatable reads, and Phantom reads.

Dirty Read

Dirty read also known as invalid data readout, refers to in the database access, transaction T1 a value modification, and then the transaction T2 read the value, and then T1 for some reason to revoke the modification of the value, which causes the T2 read the data is invalid.

Dirty reading means that when a transaction is accessing the data and the data has been modified and the modification has not been committed to the database, another transaction accesses the data and then uses that data. Because this data is data that has not yet been submitted, the data that is read by another transaction is dirty, and the operation based on dirty data may not be correct.

To illustrate:

In the following example, transaction 2 modifies a row, but no commit, and transaction 1 reads the uncommitted data. Now if transaction 2 rolls back the changes you just made or makes another change, the data found in transaction 1 is incorrect.

Transaction One	Transaction Two
/* Query 1 */ SELECT age FROM users WHERE id = 1; /* will read 20 */
	/* Query 2 */ UPDATE users SET age = 21 WHERE id = 1; /* No commit here */
/* Query 1 */ SELECT age FROM users WHERE id = 1; /* will read 21 */
	ROLLBACK; /* lock-based DIRTY READ */

In this example, the transaction 2 rollback does not have an ID of 1,age is 21 of the data. So, the transaction reads a dirty piece of data.

Non-REPEATABLE READ

Non-repeatable reading refers to the fact that in database access, two identical queries within a transaction range return different data. This is caused by the commit of the other transaction modifications in the system during the query. For example, transaction T1 reads a certain data, transaction T2 reads and modifies the data, and T1 reads the data again in order to verify the read value, it gets different results.

A more understandable argument is that the same data is read multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data of the first transaction. Because of the modification of the second transaction, the data that the first transaction reads may be different, so that the data that is read two times within a transaction is not the same, so called non-repeatable read, that is, the original read is not repeatable.

To illustrate:

In lock-based concurrency control, the "non-repeatable read (non-repeatable read)" phenomenon occurs when a read lock is not obtained when a select operation is performed (read locks) or a read lock is released immediately after the select operation is executed; A "non-repeatable read (non-repeatable Read)" behavior occurs when a transaction that does not require a commit conflict is rolled back in multiple versions of concurrency control.

Transaction One	Transaction Two
/* Query 1 */ SELECT * FROM users WHERE id = 1;
	/* Query 2 */ UPDATE users SET age = 21 WHERE id = 1; COMMIT; /* in multiversion concurrency control, or lock-based READ COMMITTED */
/* Query 1 */ SELECT * FROM users WHERE id = 1; COMMIT;  /*lock-based REPEATABLE READ */

In this example, transaction 2 commits successfully, so his modifications to the row with ID 1 are visible to other transactions. But transaction 1 has previously read the value of another "age" from this line.

Phantom reading

Phantom reading is a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table, such as "All rows of data" in the table. At the same time, the second transaction modifies the data in the table by inserting a "new row of data" into the table. Then, the user who operates the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred. The general solution to Phantom reading is to increase the range lock ranges, locking the lock range is read-only, which avoids phantom reading.

Phantom Read (Phantom Read) is a special scenario for non-repeatable reads (non-repeatable reads): Perform a Select when the transaction does not have a range lock ... The where operation may occur "Phantom Read (Phantom Read)".

To illustrate:

When the transaction executes 12 times Select ... Where you retrieve a range of data within the middle of the operation, transaction 2 creates a new row of data in the table (such as insert), which satisfies the "WHERE" clause of transaction 1 exactly.

Transaction One	Transaction Two
/* Query 1 */ SELECT * FROM users WHERE age BETWEEN 10 AND 30;
	/* Query 2 */ INSERT INTO users VALUES ( 3, ‘Bob‘, 27 ); COMMIT;
/* Query 1 */ SELECT * FROM users WHERE age BETWEEN 10 AND 30;

In this example, the transaction has performed the same query operation two times. However, two operations intermediate transaction two adds a data to the database that conforms to the query condition of transaction one, resulting in a phantom read.

Solution Solutions

In order to solve the reading phenomena such as dirty reading, non-repetition reading and phantom reading, we need to improve the transaction isolation boundary. At the same time, however, the higher the isolation level of the transaction, the lower the concurrency capability. Therefore, the reader is also required to weigh the business needs.

Database transaction Isolation Level (non-original)