2  

This post by Gavin Draper explains it quite well and contains some example query's.

Gavin Draper的这篇文章很好地解释了这一点，并包含了一些查询示例。

SQL Server Isolation Levels By Example

Isolation levels in SQL Server control the way locking works between transactions.

SQL Server中的隔离级别控制事务之间的锁定工作。

SQL Server 2008 supports the following isolation levels

SQL Server 2008支持以下隔离级别。

• 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.

在我详细地分析每一个示例之前，您可能想要创建一个新的数据库来运行示例，请在新数据库上运行以下脚本以创建示例数据。注意:您还需要删除IsolationTests表，并在每个示例重置数据之前重新运行这个脚本。

CREATE TABLE IsolationTests  
(
    Id INT IDENTITY,
    Col1 INT,
    Col2 INT,
    Col3 INTupdate te
)

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….

还在继续讲下去之前一定要了解这两项....

1. 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.
2. 脏读——这是当您读取未提交的数据时，当这样做时，不能保证数据读取将被提交，这意味着数据很可能是坏的。
3. 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.
4. 幻影读取——这是当您正在处理的数据自您第一次读取以来被另一个事务更改时。这意味着在同一事务中对该数据的后续读取很可能是不同的。

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.

这是最低的隔离级别。读uncommitted不会导致请求共享锁，从而允许您读取当前正在其他事务中修改的数据。它还允许其他事务修改正在读取的数据。

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.

你可以想象，这可能会以各种不同的方式导致一些意想不到的结果。例如，如果一个更新在另一个事务中运行，导致您的一些行返回更新的值，而有些则不返回，那么select的返回的数据可能处于半路状态。

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.

要查看read uncommitted in action，可以在Management Studio的一个选项卡中运行Query1，然后在Query1完成之前在另一个选项卡中快速运行Query2。

Query1

Query1

BEGIN TRAN  
UPDATE IsolationTests SET Col1 = 2  
--Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:10'  
ROLLBACK

Query2

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.

注意，Query2不会等待Query1完成，更重要的是Query2返回脏数据。记住，Query1回滚了所有的更改，但是Query2还是返回了数据，这是因为它没有等待所有其他的事务，对这些数据进行独占锁定，它只是返回了当时的数据。

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.

通过使用NOLOCK表提示，使用read uncommitted隔离级别查询数据有一个语法快捷方式。您可以将上面的Query2更改为如下所示，它将执行完全相同的操作。

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.

这是默认的隔离级别，意味着选择将只返回提交的数据。Select语句将针对正在查询的数据发出共享锁请求，如果另一个事务已经对该数据拥有独占锁，那么这会导致您等待。一旦您拥有了共享锁，任何试图修改该数据的其他事务都将请求一个独占锁，并等待读取提交的事务完成。

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.

您可以看到一个读取事务的示例，在返回数据之前等待修改事务完成，方法是在单独的选项卡中运行以下查询，就像您在read Uncommitted中所做的那样。

Query1

Query1

BEGIN TRAN  
UPDATE Tests SET Col1 = 2  
--Simulate having some intensive processing here with a wait
WAITFOR DELAY '00:00:10'  
ROLLBACK

Query2

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.

请注意Query2在返回之前是如何等待第一个事务完成的，以及当Query1回滚时返回的数据是如何返回的。没有指定隔离级别的原因是读取提交是SQL Server的默认隔离级别。如果您想检查正在运行的隔离级别，可以运行DBCC用户选项。记住隔离级别是特定于连接/事务的，所以同一数据库上的不同查询通常在不同的隔离级别下运行。

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 forced to wait for the read transaction to complete.

这类似于Read Committed，但附加的保证是，如果您在事务中两次发出相同的select，那么您将两次得到相同的结果。它通过保留在读取的记录上获得的共享锁来实现这一点，直到事务结束，这意味着任何试图修改这些记录的事务都必须等待读取事务完成。

As before run Query1 then while its running run Query2

和之前一样，运行Query2时运行Query1

Query1

Query1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ  
BEGIN TRAN  
SELECT * FROM IsolationTests  
WAITFOR DELAY '00:00:10'  
SELECT * FROM IsolationTests  
ROLLBACK

Query2

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.

注意，Query1为两个select返回相同的数据，即使您在第二个select运行之前运行查询来修改数据。这是因为Update查询被迫等待Query1完成，因为在指定可重复读取时打开了独占锁。

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.

如果您重新运行上述查询，但将Query1更改为读取提交，您将注意到这两个选择返回不同的数据，并且Query2不等待Query1完成。

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.

这个隔离级别接受可重复读取，并增加了不添加新数据的保证，消除了获取幻像读取的机会。它通过在查询的数据上放置range锁来实现这一点。这会导致任何其他事务试图修改或插入该事务所涉及的数据，直到它完成为止。

You know the drill by now run these queries side by side…

现在您已经知道了如何并行运行这些查询……

Query1

Query1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE  
BEGIN TRAN  
SELECT * FROM IsolationTests  
WAITFOR DELAY '00:00:10'  
SELECT * FROM IsolationTests  
ROLLBACK

Query2

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.

您将看到在Query2中插入的insert等待Query1完成，在它运行之前，它将消除幻读的机会。如果您将Query1中的隔离级别更改为可重复读取，您将看到插入不再被阻塞，并且Query1中的两个select语句返回不同数量的行。

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 row versioning is used so when data is changed the old version is kept in tempdb so existing transactions will see the version without the change. When all transactions that started before the changes are complete the previous row version is removed from tempdb. This means that even if another transaction has made changes you will always get the same results as you did the first time in that transaction.

这提供了与serializable相同的保证。所以有什么区别呢?更重要的是，使用快照不会阻止其他查询插入或更新快照事务触及的数据。相反，使用行版本控制，所以当数据被更改时，旧版本将保存在tempdb中，以便现有的事务可以看到没有更改的版本。当在更改完成之前启动的所有事务完成时，将从tempdb中删除先前的行版本。这意味着，即使另一个事务进行了更改，您也将始终得到与您在该事务中第一次所做的相同的结果。

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 hold multiple versions of your changes.

一面是你不阻止任何人修改数据当您运行您的事务但....您正在使用SQL服务器上的额外资源来保存更改的多个版本。

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.

如果您从serializable中重新运行示例，但是将隔离级别更改为快照，您将注意到仍然得到相同的数据，但是Query2不再等待Query1完成。

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.

现在，您应该对每个不同的隔离级别如何工作有了很好的了解。您可以看到，使用的级别越高，提供的并发性越少，对表的阻塞就越多。您应该始终尝试使用您所能使用的最低隔离级别，通常读取commit。

3  

• READ UNCOMMITTED: The SELECT can read all kinds of nasty inconsistencies. Old rows, new rows, duplicate rows, missing rows. It can also totally error out with the famous "data movement" error.
• 读取未提交:选择可以读取各种严重的不一致性。旧行，新行，重复行，缺少行。它也可以完全错误地使用著名的“数据移动”错误。
• READ COMMITTED: Will block without snapshot isolation. Will return the old state with snapshot isolation in perfect consistency.
• 读取提交:将在没有快照隔离的情况下阻塞。将返回快照隔离的旧状态，并保持完美的一致性。
• REPEATABLE READ/SERIALIZABLE: Will block.
• 可重复读/序列化:将阻止。
• SNAPSHOT: Will return the old state with snapshot isolation in perfect consistency.
• SNAPSHOT:返回快照隔离的旧状态，具有完美的一致性。

It sounds like you should read a few concurrency tutorials. I have written these brief facts to get you started. To really understand what's going on to the point that you can make predictions (that come true) you need to go deeper than an answer on Stack Overflow can provide.

听起来你应该读一些并发教程。我写这些简短的事实是为了让你们开始。要真正理解发生了什么，从而能够做出预测(实现预测)，您需要深入到堆栈溢出所能提供的答案之外。

Most of the time, you want to use SNAPSHOT for read-only transactions. It takes away all concurrency concerns. Be aware that it has a few drawbacks.

大多数情况下，您希望将快照用于只读事务。它消除了所有并发问题。请注意，它有一些缺点。