View and change SQL Server transaction isolation level

You can customize the transaction isolation level based on your SQL Server, which will meet your requirements or improve performance. For example, for SQL Server with simple logic, you can use the read uncommitted mode to reduce deadlocks, reduce congestion, and improve performance and response. There should be a lot of such application scenarios, but there is no global configuration, you sister!

This feature is really powerful, but I don't know why Microsoft defines its maximum scope as the current link. It's really tough and cannot be set globally, the following explains in detail how to set less and use more comfortably.

View the current SQL Server transaction isolation level settings:

The value of DBCC useroptions-> isolation level is the current setting value.

But I have to say that this command is almost useless. Why? Because the scope of the transaction isolation level is the current link, that is, you are viewing the current link level, however, it is normal for SQL Server to have more than 150 connections at the same time. What about other connections?

Set the SQL Server transaction isolation level

The settings at the SQL Server transaction isolation level are also very painful, tangled, and disgusting. But what's better is that its settings can be set in multiple occasions and in multiple ways, making up for a little bit.

1. settings in the transact-SQL statement

In the current SQL statement, the transaction isolation level is set to only affect the current SQL statement. There are two ways:

 --  The first method  
  
  Select     *    From  Table1  With  (Nolock)

  --  The second method 
  
  Set     Transaction     Isolation     Level     Read     Uncommitted  ;

  Select     *   From  Table1 

This method is flexible and can be focused on statements. The disadvantage is that there are too many settings, because the scope of this method is too small.

2. settings in ADO. net

Use System. Data. sqlclient to host the ADO. NET application in the namespaceProgramYou can call the sqlconnection. begintransaction method and set the isolationlevel option to unspecified, chaos, readuncommitted, readcommitted, repeatableread, serializable, or snapshot.

Is set in sqlconnection,CodeAs follows:

System. Data. sqlclient. sqlconnection con= NewSqlconnection ();

Con. begintransaction (isolationlevel. readuncommitted );
 

In this way, the scope is increased. The disadvantage is to set it in C #. the most terrible thing is that if ORM is used, how can I set it !!!!!!!!!!!!!

Of course, there are other settings. For details, see: Adjust the transaction isolation level.

This is the egg function, such a good function, but such an egg setting, there is no global setting, it is strongly recommended that Microsoft put the transaction isolation level settings in sp_configure

SQL Server transaction isolation level explanation:

A transaction specifies an isolation level that defines the degree to which a transaction must be isolated from other transactions for resource or data changes. The isolation level is described from the perspective of allowed concurrent side effects (such as dirty read or phantom read.

Transaction isolation level control:

Whether the lock is used and the requested lock type when reading data.

The time when the lock is read.

Whether to reference the read operations of rows modified by other transactions:

Other transactions are blocked before the exclusive lock on the row is released.

Retrieves the committed version of the row that exists during the start statement or transaction.

Read uncommitted data changes.

Selecting the transaction isolation level does not affect the lock obtained to protect data modification. A transaction always acquires an exclusive lock on any data it modifies and holds the lock before the transaction is completed, no matter what isolation level is set for the transaction. For read operations, the transaction isolation level mainly defines the protection level to prevent changes made by other firms.

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 overhead is minimal.

The ISO Standard defines the following isolation levels, which are supported by the SQL Server database engine:

Uncommitted read (the lowest level of isolation transaction, only ensure that the physical damage data is not read)

Committed read (default database engine level)

Repeatable read

Serializable (the highest level of isolation transactions, full isolation between transactions)

Isolation level	Dirty read	Non-repeated read	phantom read
uncommitted read	Yes	Yes	Yes
committed read	NO	Yes	Yes
repeatable	NO	NO	Yes
snapshot	NO	NO	NO
serializable	NO	NO	NO