Atitit. Summary of database transaction isolation level attilax, atititattilax

Atitit. Summary of database transaction isolation level attilax, atititattilax

Atitit. database transaction isolation level

 

1. Role of transaction isolation level 1

2. Isolation Level 2

3. Default management level of common databases 3

 

1. Role of transaction isolation level

 

 

A lower isolation level can enhance the ability of many users to access data at the same time, but it also increases the number of concurrent side effects (such as dirty read or loss of updates) that users may encounter. On the contrary, a higher isolation level reduces the types of concurrent side effects that users may encounter, but requires more system resources and increases the possibility of one transaction blocking other transactions. Balance the data integrity requirements of applications with the overhead of each isolation level, and select the corresponding isolation level. The highest isolation level (serializable) ensures that the transaction can accurately retrieve the same data for each repeated read operation. However, you need to execute a certain level of lock to complete this operation, locking may affect other users in multiple user systems. At the lowest isolation level (uncommitted read), you can retrieve data that has been modified but not committed by other transactions. In uncommitted reads, all concurrent side effects may occur, but because there is no read lock or version control, the minimum overhead is

 

Author: old wow's paw Attilax iron, EMAIL: 1466519819@qq.com

Reprinted please indicate Source: http://blog.csdn.net/attilax

 

 

 

When multiple transactions are performed at the same time, you can set the isolation level to handle dirty read, non-repeated read, and phantom read events.

Read uncommitted | 0 uncommitted read

Set the query isolation level to 0.

Dirty data can be read

Read dirty data: A transaction adds, deletes, and modifies the data, but is not committed. It may be rolled back, but another transaction reads uncommitted data.

Read committed | 1 committed read

Specify the isolation level of the query as 1.

Avoid dirty reads, but there may be non-repeated and Phantom reads.

Non-repeated read: A transaction updates or deletes the data, and the data queried by the other transaction is inconsistent.

Phantom read: A transaction adds data, and the data queried by the other transaction is inconsistent.

Repeatable read | 2 repeatable read

Specify the transaction isolation level as 2.

Prevents dirty reads and does not allow repeated reads.

Serializable | 3 serializable

Set the query isolation level to 3.

Serialized read, transactions can only be executed one by one, avoiding dirty reads, non-repeated reads, Phantom reads

Execution efficiency is slow (I have encountered a situation where the isolation level is 30 times the isolation level 1 ).

SNAPSHOT

When reading data, you can ensure that the row read by the read operation is the final commit version available at the beginning of the transaction.

This means that the isolation level ensures that the committed data is read and that the data can be reread,

It can also ensure that there is no phantom read. However, this isolation level uses version control instead of shared locks.

SQL Server 2005 and later versions are supported.

The following table shows the concurrent side effects allowed at different isolation levels.

Isolation level	Dirty read	Non-repeated read	Virtual read
Uncommitted read	Yes	Yes	Yes
Committed read	No	Yes	Yes
Repeatable read	No	No	Yes
Snapshots	No	No	No
Serializable	No	No	No

 

 

2. isolation level

The ead uncommitted specified statement can read rows modified but not committed by other transactions. Transactions running at the read uncommitted level do not issue a shared lock to prevent other transactions from modifying the data READ by the current transaction. The read uncommitted transaction will not be blocked by the exclusive lock. The exclusive lock will prevent the current transaction from reading rows that have been modified but not committed by other transactions. After this option is set, uncommitted changes can be read, which is called dirty read. Before the transaction ends, you can change the value in the data, or the row can appear in the data set or disappear from the data set. This option is used to set NOLOCK for all tables in all SELECT statements in the transaction. This is the minimum isolation level.

The specified read committed statement cannot READ data modified by other transactions but not COMMITTED. This avoids dirty reads. Other transactions can change data between the statements of the current transaction, resulting in non-repeated read and phantom data. This option is the default setting of SQL Server.

Repeatable read the specified statement cannot READ the rows modified but not committed by other transactions, and it is specified that no other transaction can modify the data READ by the current transaction before the current transaction is completed. A shared lock is set for all data read by each statement in the transaction, and the shared lock is kept until the transaction is completed. This prevents other transactions from modifying any row read by the current transaction. Other transactions can insert new rows that match the search conditions of the statements issued by the current firm. If the current transaction then re-runs the statement, it will retrieve the new row, resulting in phantom read. Because the shared lock is kept until the transaction ends, rather than released at the end of each statement, the concurrency level is lower than the default read committed isolation level. This option is used only when necessary.

3. Default management level of common databases

Mssql default read committed isolation level to access data

 

 

First, you must specify the isolation level supported by Oracle: read committed/serializable. The default value is read committed, while MySQl supports the isolation level: read uncommitted/read committed/repeatable read/serializable, the default value is repeatable read;

Conclusion: Oracle's read committed lock strength and isolation level are equivalent to MySQL's read committed. Therefore, Oracle's default consistent read

 

Oralce_MySQL default isolation level comparison-Oracle-times Yuan Li Fang network-computer knowledge and technology exchange platform .htm

Isolation level _ Baidu Baike .htm

 

This involves the database isolation level. By default, oracle, mssql, and mysql access data at the read committed isolation level.

Your principle is as follows ::

Transaction A, open, and read the data. At this time, transaction A has not been committed.
Update data immediately after transaction B and submit the transaction

Transaction A reads the data again and finds that the data has changed.

 

Because, by default, the read committed level of the database, as the name suggests, ensures that the COMMITTED data can be READ to prevent dirty reads ..

If the default isolation level of the database configuration is read uncommitted for performance consideration, this is not the case and the data cannot be changed ..