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
Isolation level |
& nbsp; resolving concurrency issues |
existing and sexual issues |
READ uncommitted |
Not applicable for concurrent occasions |
Dirty Reads, non-repeatable Reads, Phantom Reads | /tr>
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 | /tr>
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