Database transaction isolation level, transaction isolation level

Database transaction isolation level, transaction isolation level

Transaction Features: ACID

Atomicity: Atomicity

Consistency: Consistency

Isolation: Isolation

Duration: Persistence

 

The SQL standard defines four isolation levels for transactions:

1. read uncommitted read

Modifications in the transaction are visible to other transactions even if they are not committed. That is, transactions can read uncommitted data, and dirty reads are generated.

2. read committed

Most data systems are at this level, and oracle is at this level, but MySQL is not at this level.

Transaction-Level Definition: at the beginning of a transaction, you can only "see" modifications made by committed transactions. In other words, any modifications made to a transaction from start to commit are invisible to other transactions.

3. repeatable read

The transaction that reads data will prohibit the write transaction (but allow the read transaction), and the write transaction will prohibit any other transactions. MySQL is at this level by default.

4. serializable

It is the highest isolation level. Serializable locks each row of data read, which may cause a lot of timeout and lock contention problems.

What are the transaction isolation levels?

The default isolation level of most databases is Read Commited, for example, SQL server, and a few Oracle databases. The default isolation level is Repeatable Read, for example, MySql InnoDB 1. Dirty Read: (transactions are not committed and Read in advance): when a transaction is accessing the database and modifying the data, the modification has not been committed to the database. This is because another transaction accesses the database and then uses the data. 2. Do not read the same data repeatedly (the data read twice is inconsistent) in a transaction. When the transaction has not ended, another transaction also accesses the same data. Therefore, due to the modification of the second transaction, the data read by the first transaction for two times may be different, so that the data read by the first transaction is inconsistent. 3. Phantom read: A phenomenon occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table. This modification involves all the data rows in the table, the second transaction also modifies the data in this table. When this modification is performed, a new row of data is inserted into the table. Then, the user who operates the first transaction will find that there are still unmodified data rows in the table, it's like an illusion. 4. Loss of the first type of update (rollback loss ):
When two transactions update the same data source, if the first transaction is committed and the other transaction is canceled, the new transaction will be canceled together with the first transaction. That is to say, the first transaction is lost.
5. The second type of update loss (overwrite loss ):
The second type of update loss is a concurrency problem that is often encountered in practical applications. It is essentially the same type of concurrency problem as non-repeatable reading. Generally, it is regarded as a special case of non-repeated reading: when two or more transactions query the same records and update the row based on the initial query results, the second type of update is lost. Because every transaction does not know the existence of other transactions, the modifications made by the last transaction to the record will overwrite the committed and new transactions made to the record by other transactions... the Isolation (Isolation) attribute supports five transaction settings. The details are as follows:
DEFAULT uses the isolation level set by the database, which is determined by the DBA's DEFAULT settings.
READ_UNCOMMITTED will show dirty reads, non-repeated reads, and Phantom reads (lowest isolation level, high concurrency performance)
READ_COMMITTED may cause non-repeated read and phantom read problems (locking the row being read)
REPEATABLE_READ will trigger phantom read (Lock all rows read)
SERIALIZABLE ensures that all situations will not occur (Lock table)
Repeatable reading focuses on modification:
In the same condition, the data you read is read again and the value is different.
The focus of phantom read is to add or delete the same conditions. The number of records read for 1st and 2nd is different.

Database isolation level issues

If you say this is two transactions (read/write in one transaction), this will not happen.
This problem may occur in four transactions.