Isolation levels in SQL Server control the way locking works between transactions.
SQL Server 2008 supports the following isolation levels
- Read Uncommitted
- Read Committed (The default)
- Repeatable Read
- Serializable
- Snapshot
Before I run through each of these in detail you may want to create a new database to run the examples, run the following script on the new database to create the sample data.Note : You’ll also want to drop the IsolationTests table and re-run this script before each example to reset the data.
CREATE TABLE IsolationTests
( Id INT IDENTITY,
Col1 INT ,
Col2 INT ,
Col3 INT
) INSERT INTO IsolationTests(Col1,Col2,Col3)
SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
UNION ALL SELECT 1,2,3
|
Also before we go any further it is important to understand these two terms….
- Dirty Reads – This is when you read uncommitted data, when doing this there is no guarantee that data read will ever be committed meaning the data could well be bad.
- Phantom Reads – This is when data that you are working with has been changed by another transaction since you first read it in. This means subsequent reads of this data in the same transaction could well be different.
Read Uncommitted
This is the lowest isolation level there is. Read uncommitted causes no shared locks to be requested which allows you to read data that is currently being modified in other transactions. It also allows other transactions to modify data that you are reading.
As you can probably imagine this can cause some unexpected results in a variety of different ways. For example data returned by the select could be in a half way state if an update was running in another transaction causing some of your rows to come back with the updated values and some not to.
To see read uncommitted in action lets run Query1 in one tab of Management Studio and then quickly run Query2 in another tab before Query1 completes.
Query1
BEGIN TRAN
UPDATE Tests SET Col1 = 2
--Simulate having some intensive processing here with a wait WAITFOR DELAY '00:00:10'
ROLLBACK |
Query2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT * FROM IsolationTests
|
Notice that Query2 will not wait for Query1 to finish, also more importantly Query2 returns dirty data. Remember Query1 rolls back all its changes however Query2 has returned the data anyway, this is because it didn’t wait for all the other transactions with exclusive locks on this data it just returned what was there at the time.
There is a syntactic shortcut for querying data using the read uncommitted isolation level by using the NOLOCK table hint. You could change the above Query2 to look like this and it would do the exact same thing.
SELECT * FROM IsolationTests WITH (NOLOCK)
|
Read Committed
This is the default isolation level and means selects will only return committed data. Select statements will issue shared lock requests against data you’re querying this causes you to wait if another transaction already has an exclusive lock on that data. Once you have your shared lock any other transactions trying to modify that data will request an exclusive lock and be made to wait until your Read Committed transaction finishes.
You can see an example of a read transaction waiting for a modify transaction to complete before returning the data by running the following Queries in separate tabs as you did with Read Uncommitted.
Query1
BEGIN TRAN
UPDATE Tests SET Col1 = 2
--Simulate having some intensive processing here with a wait WAITFOR DELAY '00:00:10'
ROLLBACK |
Query2
SELECT * FROM IsolationTests
|
Notice how Query2 waited for the first transaction to complete before returning and also how the data returned is the data we started off with as Query1 did a rollback. The reason no isolation level was specified is because Read Committed is the default isolation level for SQL Server. If you want to check what isolation level you are running under you can run “DBCC useroptions”. Remember isolation levels are Connection/Transaction specific so different queries on the same database are often run under different isolation levels.
Repeatable Read
This is similar to Read Committed but with the additional guarantee that if you issue the same select twice in a transaction you will get the same results both times. It does this by holding on to the shared locks it obtains on the records it reads until the end of the transaction, This means any transactions that try to modify these records are force to wait for the read transaction to complete.
As before run Query1 then while its running run Query2
Query1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT * FROM IsolationTests
WAITFOR DELAY '00:00:10'
SELECT * FROM IsolationTests
ROLLBACK |
Query2
UPDATE IsolationTests SET Col1 = -1
|
Notice that Query1 returns the same data for both selects even though you ran a query to modify the data before the second select ran. This is because the Update query was forced to wait for Query1 to finish due to the exclusive locks that were opened as you specified Repeatable Read.
If you rerun the above Queries but change Query1 to Read Committed you will notice the two selects return different data and that Query2 does not wait for Query1 to finish.
One last thing to know about Repeatable Read is that the data can change between 2 queries if more records are added. Repeatable Read guarantees records queried by a previous select will not be changed or deleted, it does not stop new records being inserted so it is still very possible to get Phantom Reads at this isolation level.
Serializable
This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads. It does this by placing range locks on the queried data. This causes any other transactions trying to modify or insert data touched on by this transaction to wait until it has finished.
You know the drill by now run these queries side by side…
Query1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
SELECT * FROM IsolationTests
WAITFOR DELAY '00:00:10'
SELECT * FROM IsolationTests
ROLLBACK |
Query2
INSERT INTO IsolationTests(Col1,Col2,Col3)
VALUES (100,100,100)
|
You’ll see that the insert in Query2 waits for Query1 to complete before it runs eradicating the chance of a phantom read. If you change the isolation level in Query1 to repeatable read, you’ll see the insert no longer gets blocked and the two select statements in Query1 return a different amount of rows.
Snapshot
This provides the same guarantees as serializable. So what’s the difference? Well it’s more in the way it works, using snapshot doesn’t block other Queries from inserting or updating the data touched by the snapshot transaction. Instead it creates it’s own little snapshot of the data being read at that time, if you then read that data again in the same transaction it reads it from its snapshot, This means that even if another transaction has made changes you will always get the same results as you did the first time you read the data.
So on the plus side your not blocking anyone else from modifying the data whilst you run your transaction but…. You’re using extra resources on the SQL Server to allocate each snapshot transaction the additional resources to store the snapshot data which can be quite significant if your transaction is working with a large amount of data.
To use the snapshot isolation level you need to enable it on the database by running the following command
ALTER DATABASE IsolationTests
SET ALLOW_SNAPSHOT_ISOLATION ON
|
If you rerun the examples from serializable but change the isolation level to snapshot you will notice that you still get the same data returned but Query2 no longer waits for Query1 to complete.
Summary
You should now have a good idea how each of the different isolation levels work. You can see how the higher the level you use the less concurrency you are offering and the more blocking you bring to the table. You should always try to use the lowest isolation level you can which is usually read committed.
相关推荐
ANSI SQL通过脏读、不可重复读和幻想(如幻读)现象定义了隔离级别。这篇论文介绍了这些现象和ANSI SQL定义未能正确描述的几个比较流行的隔离...最后,定了一个重要的多版本隔离类型,快照隔离(Snapshot Isolation)。
SQL事务隔离级别经典论文
隔离级别
DB - A Critique of ANSI SQL Isolation Levels.pdf ANSI SQL-92 [MS, ANSI] defines Isolation Levels in terms of phenomena: Dirty Reads, Non-Re-peatable Reads, and Phantoms. This document shows that ...
Improve your ability to develop, manage, and troubleshoot SQL Server solutions by learning how different components work “under the hood,” and how they communicate with each other. The detailed ...
任何关系数据库必须支持事务的ACID属性,即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、永久性(Durability)。ACID属性确保数据库中的数据更改被正确地收集到一起,并且数据将保持在与所...
SQLServer中防止并发插入重复数据,大致有以下几种方法: 1.使用Primary Key,Unique Key等在数据库层面让重复数据无法插入。 2.插入时使用条件 insert into Table(****) select **** where not exists(select 1 ...
Prior to SQL Server 7.0, REPEATABLE READ and SERIALIZABLE isolation levels were synonymous. There was no way to prevent non-repeatable reads while not preventing phantoms. By default, SQL Server 2000 ...
I think you will find this “by Example” book a helpful and complete guide, no matter what side of the Web site you support, or even if you are just starting your own. Acknowledgments Many people...
Why Concurrency Control is required? Formal Concepts ...ANSI SQL isolation levels Snapshot Isolation Locking Basics Deadlocks Index locking Optimistic locking Multi-Version Concurrency Control
Simply put, Snapshot isolation works a little bit like version control system for row data. Different versions of rows are kept for each connection while transactions are executing so that locks don’...
SQL Server keeps track of which pages belong to a table or index by using IAM pages. If there is no clustered index, there is a sysindexes row for the table with an indid value of 0, and that row will...
RANSACTION ISOLATION LEVEL
《Granularity of Locks and Degrees of Consistency in a ...为之后更为出名的 《ANSI SQL-92 标准》1、《A Critique of ANSI SQL Isolation Levels》2、《Generalized Isolation Level Definitions》提供了参考。
iforest算法是用于检测异常点的。对于电商、金融领域的欺诈检测应用广泛
Web上的IBD是一种统计资源,可通过壁炉架测试,自举和简化的主轴回归分析来分析遗传距离与地理距离之间的关系。
孤立森林算法 java实现 iforest算法是用于检测异常点的。对于电商、金融领域的欺诈检测应用广泛
isolated_forest_example 在Python中实现隔离林的示例
how to isolate wafer substrate noise
基于LiCl/DMSO全溶体系的大豆秸秆木质素分离与表征,刘祝兰,曹云峰,提取大豆秸秆酶解木素(CEL)过程中,在酶解之前创新性地使用LiCl/DMSO溶剂体系预处理球磨后原料,从而提高木素提取效率且不引起木素