Explanation of the SQL Server transaction and Isolation level instances

SQL Server implements 6 isolation levels to prevent the occurrence of problems similar to attempts to concurrently access or modify the same data in a concurrency scenario. This article will take you through all 6 isolation levels. As you'll see next, you'll understand what each isolation level can achieve and when to use it.

I. Introduction of the Business

5 of the 6 isolation levels of SQL Server are used to isolate transactions, and are thus referred to as transaction isolation levels. The other one works at the statement level.

In reality, requiring multiple data modification operations must either be completely successful or nothing happens. When data is merged into a database, there may be multiple tables that need to be updated. When the customer orders, the order form, Invoice Line Item table, and product table data may need to be updated. Purchasing a ticket may require updating the passenger and reservations tables. Whenever an operation requires multiple data change operations to be handled as a single unit in its entirety, this is the time to use the transaction.

If all the data change operations in the transaction succeed, the data changes can be committed (that is, persisted to the database). Otherwise, all data changes that have occurred in the transaction to the point of failure must be rolled back (that is, the undo operation, nothing happened).
Transaction-related operations, refer to http://www.cnblogs.com/xinaixia/p/4831198.html.

Ii. Introduction to the level of isolation

Concurrency must be treated with care because they can raise known concurrency issues, including dirty reads, non-repeatable reads, and Phantom reads, which can in turn lead to undesirable consequences for the data. As we already know, in order to prevent concurrency problems, isolation levels are used to isolate transactions or statements from one another.

The following are the isolation level names defined in SQL Server 2008:

　　1, Transaction isolation level

[1] READ UNCOMMITTED (uncommitted read, read dirty), equivalent (NOLOCK)
[2] read COMMITTED (default) (Read committed, defaults level)
[3] Repeatable read (can be read repeatedly), equivalent to (HOLDLOCK)
[4] SERIALIZABLE (serializable)
[5] SNAPSHOT (snapshot)

　　2, Statement isolation level

[6] Read COMMITTED SNAPSHOT (already committed read isolation)

For the first four isolation levels: READ UNCOMMITTED < read COMMITTED < repeatable read < SERIALIZABLE
The higher the isolation level, the more stringent the request lock on the read operation, the longer the lock is held, so the higher the isolation level, the higher the consistency, the lower the concurrency, and the greater the relative impact of the performance.

Get transaction isolation levels (Isolation level)

Dbcc

Set up quarantine

  set  transaction  isolation  level  <  isolation  Name>  --  note: When setting up a reply-to-quarantine (repeatable READ) Two words need to be separated by a space, but can be glued together in table isolation (repeatableread) . Span style= "color: #000000;" > set query table isolation  select  .... from  <  table  >  with  (<  > ) 

As you will see in the following example, the higher the isolation level, the higher the level of protection provided (to prevent more concurrency issues). Also, each isolation level includes the protection provided at the previous level, so each subsequent higher isolation level provides additional protection in the form of more concurrency issues. However, there is no free lunch in the world, the higher the isolation level, the lower the availability of data. Choosing the right isolation level is a balancing act between highly secure concurrency and high availability of data.

Iii. Introduction of examples

In order to create a concurrency environment, all examples use 2 SQL Server sessions, each running a different transaction, each accessing the same resource. In SQL Server Management Studio, each query window represents a different session, so you can use a different query window for different transactions in SQL Server Management Studio.
All examples contain real-world scenarios so that you can build on reality.

1. READ UNCOMMITTED not submitted

The READ UNCOMMITTED transaction isolation level simply does not provide isolation between transactions, which allows for a dirty read that violates one of the most basic forms of the concurrency principle. Dirty reads occur when a transaction is able to read data that has been update but not yet commit in another transaction. READ UNCOMMITTED does not request locks, runs read uncommitted modifications, which allow read dirty data, and read operations do not affect write requests for exclusive locks.

READ uncommitted is often applied to: single-user systems, where the likelihood of two transactions accessing the same resource at the same time is zero or almost zero;

• When you control concurrency using the rowversion data type

• Single-User system
• Zero or almost zero likelihood that two transactions in the system access the same resource at the same time
• When you control concurrency using the rowversion data type

Example 2. READ COMMITTED Transaction ISOLATION LEVEL

The Read COMMITTED transaction isolation level prevents a "dirty read" issue by allowing only one transaction to read data that has already been committed in another transaction. This is the default transaction isolation level in SQL Server.

Example 3. Repeatable READ Transaction ISOLATION LEVEL

As you can see in step 2 of the previous transaction isolation level, the transaction in Session 2 modifies the data that has been read by the transaction in session 1. This can lead to "LOST UPDATE", as described in the real-world scenario. Repeatable the read transaction isolation level does not allow this to occur because it violates the repeatable read principle. In other words, the transaction reading the same data in Session 1 may produce different results.

Example 4. SERIALIZABLE Transaction ISOLATION LEVEL

To show you the concurrency issues that serializable transaction isolation levels Prevent, we start with the repeatable READ transaction isolation level.

Example 5. Snapshot transaction Isolation Level

Perhaps you have noticed that in the example 1 to 4 above, the prevention of concurrency issues also reduces the accessibility of data. Read is not allowed first, then update is not allowed, insert is not allowed. The snapshot transaction isolation level prevents many of the concurrency problems that were previously prevented by those isolation levels, while reducing the cost associated with them. It allows for higher data availability.

This feat was accomplished snapshot the transaction isolation level by creating a virtual snapshot of the database by using row versions in tempdb before the transaction began. It then only allows transactions to access the database virtual snapshot. This method is called "versioning-based Isolation" (versioning-based isolation, see Understanding Row versioning-based Isolation Levels for a complete description of the details behind.

With versioning-based isolation, transactions can only see the data in the virtual snapshot. As a result, other transactions still have access to the same data, as long as they do not modify the data that has been modified by the first transaction. If that is done (attempting to modify the data), then those transactions will be rolled back and terminated with an error message.

This can only be used if the switch on the snapshot transaction isolation level in the database is turned on. Turning on this switch tells the database to set up a versioned environment. It is important to understand this, because once versioned is on, the database has the overhead of maintaining versioning, regardless of whether a transaction is using the snapshot transaction isolation level.

Example 6. READ COMMITTED SNAPSHOT Isolation Level

So far, all isolation levels have separated transactions from one another. Once the initial transaction is complete, resources that become unavailable to other transactions become available again. The read COMMITTED SNAPSHOT isolation level differs from this point in that it is able to read data that has been submitted by his transaction.

The READ COMMITTED SNAPSHOT isolation level is also opened through a database switch. Then, any transaction that uses the Read COMMITTED SNAPSHOT isolation level will work through versioning.


Hopefully, the above examples will help you understand how to properly isolate transactions and statements to prevent concurrency problems.

Iv. Summary /tr> /tr>

Isolation level	& nbsp; resolving concurrency issues	existing and sexual issues
READ uncommitted	Not applicable for concurrent occasions	Dirty Reads, non-repeatable Reads, Phantom Reads
READ COMMITTED	Dirty Reads	Lost Update, non-repeatable Reads, Phantom Reads
repeatable READ	non-repeatable Reads	Phantom Reads, potentially deadlocking
SERIALIZABLE	Phantom Reads	less Data availability, potentially deadlocking
SNAPSHOT	All of these concurrency issues	transactions Access virtual snapshots, data for other transactions committed still not visible to the current transaction, and update is not allowed for data updated by other transactions 。
READ COMMITTED SNAPSHOT	All of the above concurrency issues	none

Reference

Explanation of the SQL Server transaction and Isolation level instances