何时/以什么方式在READ COMMITTED隔离级别中保留/释放锁 [英] when/what locks are hold/released in READ COMMITTED isolation level

问题描述

我试图了解SQL Server中的隔离/锁.

I am trying to understand isolation/locks in SQL Server.

我在READ COMMITTED隔离级别(默认)中有以下情况

I have following scenario in READ COMMITTED isolation level(Default)

我们有一张桌子.

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)

现在我知道了来自msdn的

Now from msdn i understood

当触发选择共享锁时，其他任何事务都无法修改数据(避免脏读).文档还讨论了行级，页面级，表级锁定.我想到了以下方案

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

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

只有在运行语句select * from Transactions时才会获取锁，否则将在整个5分钟以上的时间内获取锁，直到到达COMMIT为止.

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 * from Transaction

您可以使用以下代码进行检查

You can check it with below code

打开一个SQL会话并运行此查询

open a sql session and run this query

Begin Transaction

select * from Transactions

 WAITFOR DELAY '00:05'
/*
some buisness logic which takes 5 minutes

*/

Commit

打开另一个sql会话并在查询下运行

Open another sql session and run below query

Begin Transaction
Update Transactions
Set = ...
where ....
commit

这篇关于何时/以什么方式在READ COMMITTED隔离级别中保留/释放锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！