I am trying to understand isolation/locks in SQL Server.
我正在尝试理解SQL Server中的隔离/锁。
I have following scenario in READ COMMITTED isolation level(Default)
我在读取提交隔离级别(默认)中有以下场景
We have a table.
我们有一个表。
create table Transactions(Tid int,amt int)
with some records
insert into Transactions values(1, 100)
insert into Transactions values(2, -50)
insert into Transactions values(3, 100)
insert into Transactions values(4, -100)
insert into Transactions values(5, 200)
Now from msdn i understood
现在我从msdn上了解了。
When a select is fired shared lock is taken so no other transaction can modify data(avoiding dirty read).. Documentation also talks about row level, page level, table level lock. I thought of following scenarion
当一个select被触发时，共享锁被采用，因此没有其他事务可以修改数据(避免脏读)。文档还讨论了行级、页级、表级锁。我想到了下面的场景
Begin Transaction
select * from Transactions
/*
some buisness logic which takes 5 minutes
*/
Commit
What I want to understand is for what duration of time shared lock would be acquired and which (row, page, table).
我想要了解的是，将获得什么时间共享锁，以及哪些(行、页、表)。
Will lock will be acquire only when statement select * from Transactions is run or would it be acquire for whole 5+ minutes till we reach COMMIT.
只有当语句select *从事务中运行时，锁才会被获取，或者在我们提交之前，锁会被获取5分钟以上。
3 个解决方案
#1
6
lock will only acquire when select * from Transaction is run
只有当select *从事务中运行时，锁才会获得。
You can check it with below code
您可以使用下面的代码检查它
open a sql session and run this query
打开sql会话并运行此查询
Begin Transaction
select * from Transactions
WAITFOR DELAY '00:05'
/*
some buisness logic which takes 5 minutes
*/
Commit
Open another sql session and run below query
打开另一个sql会话并运行以下查询。
Begin Transaction
Update Transactions
Set = ...
where ....
commit
#2
20
You are asking the wrong question, you are concerned about the implementation details. What you should think of and be concerned with are the semantics of the isolation level. Kendra Little has a nice poster explaining them: Free Poster! Guide to SQL Server Isolation Levels.
您提出的问题是错误的，您关心的是实现细节。您应该考虑和关注的是隔离级别的语义。Kendra Little有一个很好的海报来解释:免费的海报!SQL Server隔离级别指南。
Your question should be rephrased like:
你的问题应该换个说法:
select * from Items
从项目选择*
Q: What Items will I see?
A: All committed Items
问:我将看到什么项目?所有承诺项目
Q: What happens if there are uncommitted transactions that have inserted/deleted/update Items?
A: your SELECT will block until all uncommitted Items are committed (or rolled back).
问:如果存在插入/删除/更新项的未提交事务，会发生什么情况?答:您的选择将被阻塞，直到所有未提交的项目被提交(或回滚)。
Q: What happens if new Items are inserted/deleted/update while I run the query above?
A: The results are undetermined. You may see some of the modifications, won't see some other, and possible block until some of them commit.
问:当我运行上面的查询时，如果插入/删除/更新新项会发生什么?答:结果尚未确定。您可能会看到一些修改，但不会看到其他修改，直到其中一些提交。
READ COMMITTED makes no promise once your statement finished, irrelevant of the length of the transaction. If you run the statement again you will have again exactly the same semantics as state before, and the Items you've seen before may change, disappear and new one can appear. Obviously this implies that changes can be made to Items after your select.
一旦你的陈述完成，READ COMMITTED没有承诺，与事务的长度无关。如果您再次运行该语句，您将再次获得与状态相同的语义，您以前看到的项可能会改变、消失，并可能出现新的项。显然，这意味着可以在选择之后对项目进行更改。
Higher isolation levels give stronger guarantees: REPEATABLE READ guarantees that no item you've selected the first time can be modified or deleted until you commit. SERIALIZABLE adds the guarantee that no new Item can appear in your second select before you commit.
更高的隔离级别提供更强的保证:可重复读保证在提交之前，您第一次选择的任何项都不能被修改或删除。SERIALIZABLE添加了保证，在提交之前，您的第二次选择中不会出现新项。
This is what you need to understand, no how the implementation mechanism works. After you master these concepts, you may ask the implementation details. They're all described in Transaction Processing: Concepts and Techniques.
这是您需要了解的，不知道实现机制是如何工作的。在您掌握了这些概念之后，您可以询问实现细节。它们都在事务处理中描述:概念和技术。
#3
9
Your question is a good one. Understanding what kind of locks are acquired allows a deep understanding of DBMS's. In SQL Server, under all isolation levels (Read Uncommitted, Read Committed (default), Repeatable Reads, Serializable) Exclusive Locks are acquired for Write operations.
你的问题问得好。了解什么样的锁被获得，可以深入理解DBMS的。在SQL Server中，在所有隔离级别(读未提交、读已提交(默认)、可重复读、可串行化)下，为写操作获取独占锁。
Exclusive locks are released when transaction ends, regardless of the isolation level.
事务结束时释放独占锁，而不考虑隔离级别。
The difference between the isolation levels refers to the way in which Shared (Read) Locks are acquired/released.
隔离级别之间的差异指的是获取/释放共享(读)锁的方式。
Under Read Uncommitted isolation level, no Shared locks are acquired. Under this isolation level the concurrency issue known as "Dirty Reads" (a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed, so it could be rolled back) can occur.
在Read Uncommitted隔离级别下，不获取共享锁。在此隔离级别下，称为“脏读”的并发问题(允许事务从已被另一个正在运行的事务修改的行读取数据，并且还没有提交，因此可以回滚)。
Under Read Committed isolation level, Shared Locks are acquired for the concerned records. The Shared Locks are released when the current instruction ends. This isolation level prevents "Dirty Reads" but, since the record can be updated by other concurrent transactions, "Non-Repeatable Reads" (transaction A retrieves a row, transaction B subsequently updates the row, and transaction A later retrieves the same row again. Transaction A retrieves the same row twice but sees different data) or "Phantom Reads" (in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first) can occur.
在读提交隔离级别下，为相关记录获取共享锁。当当前指令结束时释放共享锁。这个隔离级别可以防止“脏读”，但是，由于记录可以由其他并发事务更新，所以“不可重复读取”(事务A检索行，事务B随后更新行，事务A随后再次检索相同的行。事务A检索相同的行两次，但看到不同的数据)或“虚读”(在事务的过程中，执行两个相同的查询，第二个查询返回的行集合与第一个查询不同)可以发生。
Under Repeatable Reads isolation level, Shared Locks are acquired for the transaction duration. "Dirty Reads" and "Non-Repeatable Reads" are prevented but "Phantom Reads" can still occur.
在可重复读取隔离级别下，在事务期间获取共享锁。“脏读”和“不可重复读”被阻止，但是“幻读”仍然会发生。
Under Serializable isolation level, ranged Shared Locks are acquired for the transaction duration. None of the above mentioned concurrency issues occur but performance is drastically reduced and there is the risk of Deadlocks occurrence.
在Serializable隔离级别下，在事务期间获取范围共享锁。上面提到的并发问题都没有发生，但是性能显著降低，并且存在死锁发生的风险。
#1
6
lock will only acquire when select * from Transaction is run
只有当select *从事务中运行时，锁才会获得。
You can check it with below code
您可以使用下面的代码检查它
open a sql session and run this query
打开sql会话并运行此查询
Begin Transaction
select * from Transactions
WAITFOR DELAY '00:05'
/*
some buisness logic which takes 5 minutes
*/
Commit
Open another sql session and run below query
打开另一个sql会话并运行以下查询。
Begin Transaction
Update Transactions
Set = ...
where ....
commit
#2
20
You are asking the wrong question, you are concerned about the implementation details. What you should think of and be concerned with are the semantics of the isolation level. Kendra Little has a nice poster explaining them: Free Poster! Guide to SQL Server Isolation Levels.
您提出的问题是错误的，您关心的是实现细节。您应该考虑和关注的是隔离级别的语义。Kendra Little有一个很好的海报来解释:免费的海报!SQL Server隔离级别指南。
Your question should be rephrased like:
你的问题应该换个说法:
select * from Items
从项目选择*
Q: What Items will I see?
A: All committed Items
问:我将看到什么项目?所有承诺项目
Q: What happens if there are uncommitted transactions that have inserted/deleted/update Items?
A: your SELECT will block until all uncommitted Items are committed (or rolled back).
问:如果存在插入/删除/更新项的未提交事务，会发生什么情况?答:您的选择将被阻塞，直到所有未提交的项目被提交(或回滚)。
Q: What happens if new Items are inserted/deleted/update while I run the query above?
A: The results are undetermined. You may see some of the modifications, won't see some other, and possible block until some of them commit.
问:当我运行上面的查询时，如果插入/删除/更新新项会发生什么?答:结果尚未确定。您可能会看到一些修改，但不会看到其他修改，直到其中一些提交。
READ COMMITTED makes no promise once your statement finished, irrelevant of the length of the transaction. If you run the statement again you will have again exactly the same semantics as state before, and the Items you've seen before may change, disappear and new one can appear. Obviously this implies that changes can be made to Items after your select.
一旦你的陈述完成，READ COMMITTED没有承诺，与事务的长度无关。如果您再次运行该语句，您将再次获得与状态相同的语义，您以前看到的项可能会改变、消失，并可能出现新的项。显然，这意味着可以在选择之后对项目进行更改。
Higher isolation levels give stronger guarantees: REPEATABLE READ guarantees that no item you've selected the first time can be modified or deleted until you commit. SERIALIZABLE adds the guarantee that no new Item can appear in your second select before you commit.
更高的隔离级别提供更强的保证:可重复读保证在提交之前，您第一次选择的任何项都不能被修改或删除。SERIALIZABLE添加了保证，在提交之前，您的第二次选择中不会出现新项。
This is what you need to understand, no how the implementation mechanism works. After you master these concepts, you may ask the implementation details. They're all described in Transaction Processing: Concepts and Techniques.
这是您需要了解的，不知道实现机制是如何工作的。在您掌握了这些概念之后，您可以询问实现细节。它们都在事务处理中描述:概念和技术。
#3
9
Your question is a good one. Understanding what kind of locks are acquired allows a deep understanding of DBMS's. In SQL Server, under all isolation levels (Read Uncommitted, Read Committed (default), Repeatable Reads, Serializable) Exclusive Locks are acquired for Write operations.
你的问题问得好。了解什么样的锁被获得，可以深入理解DBMS的。在SQL Server中，在所有隔离级别(读未提交、读已提交(默认)、可重复读、可串行化)下，为写操作获取独占锁。
Exclusive locks are released when transaction ends, regardless of the isolation level.
事务结束时释放独占锁，而不考虑隔离级别。
The difference between the isolation levels refers to the way in which Shared (Read) Locks are acquired/released.
隔离级别之间的差异指的是获取/释放共享(读)锁的方式。
Under Read Uncommitted isolation level, no Shared locks are acquired. Under this isolation level the concurrency issue known as "Dirty Reads" (a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed, so it could be rolled back) can occur.
在Read Uncommitted隔离级别下，不获取共享锁。在此隔离级别下，称为“脏读”的并发问题(允许事务从已被另一个正在运行的事务修改的行读取数据，并且还没有提交，因此可以回滚)。
Under Read Committed isolation level, Shared Locks are acquired for the concerned records. The Shared Locks are released when the current instruction ends. This isolation level prevents "Dirty Reads" but, since the record can be updated by other concurrent transactions, "Non-Repeatable Reads" (transaction A retrieves a row, transaction B subsequently updates the row, and transaction A later retrieves the same row again. Transaction A retrieves the same row twice but sees different data) or "Phantom Reads" (in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first) can occur.
在读提交隔离级别下，为相关记录获取共享锁。当当前指令结束时释放共享锁。这个隔离级别可以防止“脏读”，但是，由于记录可以由其他并发事务更新，所以“不可重复读取”(事务A检索行，事务B随后更新行，事务A随后再次检索相同的行。事务A检索相同的行两次，但看到不同的数据)或“虚读”(在事务的过程中，执行两个相同的查询，第二个查询返回的行集合与第一个查询不同)可以发生。
Under Repeatable Reads isolation level, Shared Locks are acquired for the transaction duration. "Dirty Reads" and "Non-Repeatable Reads" are prevented but "Phantom Reads" can still occur.
在可重复读取隔离级别下，在事务期间获取共享锁。“脏读”和“不可重复读”被阻止，但是“幻读”仍然会发生。
Under Serializable isolation level, ranged Shared Locks are acquired for the transaction duration. None of the above mentioned concurrency issues occur but performance is drastically reduced and there is the risk of Deadlocks occurrence.
在Serializable隔离级别下，在事务期间获取范围共享锁。上面提到的并发问题都没有发生，但是性能显著降低，并且存在死锁发生的风险。