sql server 更新表数据时，会给表加排他锁，如何解除排他锁

Question

可以更改，参见SET TRANSACTION ISOLATION LEVEL，不过从数据角度来考虑，这个很不建议使用。事务必须运行于可重复读或更高的隔离级别以防止丢失更新。当两个事务检索相同的行，然后基于原检索的值对行进行更新时，会发生丢失更新。如果两个事务使用一个 UPDATE 语句更新行，并且不基于以前检索的值进行更新，则在默认的提交读隔离级别不会发生丢失更新。

Answer 1

可以更改，参见SET TRANSACTION ISOLATION LEVEL，不过从数据角度来考虑，这个很不建议使用。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
不可以的。Begin Tran 
Set Transaction Isolation Level READ UNCOMMITTED
Update BuildInfo Set Floors = 6 Where Num = 'A'
－－执行到这儿停，不提交事务
Commit Tran
在另外一个Conn中执行
Update BuildInfo
Set Floors = 5
Where Num = 'A'
会进入等待的，直到上面Commit Tran之后，才会执行完成。

Answer 2

楼上的你写错了吧，Set Transaction Isolation Level READ UNCOMMITTED 要对每个连接执行，并且要再begin tran之前执行，我试过的。

Answer 3

Set 就算是写在之前也不行的。

Answer 4

TO: li_zero(相遇太早，缘分太浅) 你说的和楼主要的不是同一个东西
READ UNCOMMITTED
指定语句可以读取已由其他事务修改但尚未提交的行。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^== 这个是最低的事务隔离级别
            ^^^^ 看见了吗, 这里是读取, 而楼主要的是更新不加锁
在 READ UNCOMMITTED 级别运行的事务，不会发出共享锁来防止其他事务修改当前事务读取的数据。READ UNCOMMITTED 事务也不会被排他锁阻塞，排他锁会禁止当前事务读取其他事务已修改但尚未提交的行。设置此选项之后，可以读取未提交的修改，这种读取称为脏读。在事务结束之前，可以更改数据中的值，行也可以出现在数据集中或从数据集中消失。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 NOLOCK 相同。这是隔离级别中限制最少的级别。

Answer 5

简单地说, READ UNCOMMITTED 更改的是 SELECT 加锁的行为, 即不让 SELECT 加锁SQL通过锁之间的相容或者相斥来实现那些操作与那些操作能或者不能同时进行.而 READ UNCOMMITTED 让 SELECT 不加锁, 既然没有锁, 当然不会产生与其他锁是否相容或者相斥的问题, 从而跳过了 update 下的排它锁但它并不会让 update 不下排它锁

Answer 6

-- 可以用下面的代码来测试, 说明 UPDATE 加的排它锁-- 窗口1执行下面的代码
CREATE TABLE dbo.tb(id int)
INSERT dbo.tb SELECT id FROM sysobjects
GOSET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
UPDATE dbo.tb SET id = id + 2
WHERE id <100
-- ROLLBACK TRAN  -- 让事务保持
GO
-- 查询窗口2执行下面的语句
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRAN
UPDATE dbo.tb SET id = id + 2
WHERE id <100
ROLLBACK TRAN  -- 让事务保持
-- 测试说明: 如果查询窗口2的语句能立即执行完成, 说明 READ UNCOMMITTED 确实去掉了UPDATE的 X 锁.

Answer 7

还可以这样验证，执行下面的语句：
Set Transaction Isolation Level READ UNCOMMITTED
Begin Tran 
Update BuildInfo Set Floors = 6 Where Num = 'A'然后
执行sp_lock，结果如下：
51 8 0 0 DB                  S GRANT
51 8 453576654 1 PAG 1:41             IX GRANT
51 1 85575343 0 TAB                  Sch-S GRANT
51 8 453576654 0 TAB                  IX GRANT
51 8 453576654 1 KEY (41007461262c)   X GRANT
52 8 0 0 DB                  S GRANT执行select object_name(453576654)，结果如下：
BuildInfo可见，就算你指定了Transaction Isolation Level为Read Uncommitted，Update语句还是会给要Update的对象加上X锁（排它锁）的。最后commit tran 或rollback tran，就会发现这个X锁没有了。

Answer 8

感谢各位的积极留言，我的问题难道走到死胡同了吗？我遇到的问题，我写了一个存储过程，６个用户同时更新　一个表的内容，并且是各自更新各自的数据，互不干扰，比如第1个用户　update 表１　set 字段１='1' where flag=1
第2个用户　update 表１　set 字段１='1' where flag=2
第３个用户　update 表１　set 字段１='1' where flag=３当第一个用户执行时，第二个用户和第三个用户都要等待，怎么才能　让三个用户同时执行，提高速度和效率，难道真的要把　表１，分成多个表？

Answer 9

第1个用户　update 表１　set 字段１='1' where flag=1
第2个用户　update 表１　set 字段１='1' where flag=2
第３个用户　update 表１　set 字段１='1' where flag=３
======================================================
这些语句不会造成等待吧？

Answer 10

这个难说, 如果 update 的条件不必扫描被锁定的资源, 就可以定位到所有满足条件的记录, 那自然就不会被锁住.否则当然会被锁住.

Answer 11

还是有示例比较能说明问题:-- 1. 创建测试环境
CREATE TABLE dbo.tb(
id int IDENTITY(1, 1), 
flag int,
col int)
INSERT dbo.tb(flag, col)
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 3, 1
GOALTER TABLE dbo.tb ADD PRIMARY KEY(id)
CREATE INDEX IX_flag ON dbo.tb(flag)
GO--DROP TABLE dbo.tb

Answer 12

-- 查询窗口1  中执行下面的查询
BEGIN TRAN
UPDATE dbo.tb SET col = col + 1
WHERE flag  = 1
--ROLLBACK TRAN -- 让事务一直保持, 以保持对数据下的锁

Answer 13

-- 查询窗口2  中执行下面的查询BEGIN TRAN
UPDATE A SET col = col + 1
FROM dbo.tb A 
-- WITH(INDEX = IX_flag)
WHERE flag = 2
ROLLBACK TRAN

Answer 14

结果:
你会发现查询窗口2的处理被BLOCK了, 原因是默认情况下, sql直接走聚集索引(这里是主键)来扫描数据, 而要通过 flag 来定位数据的话, 要扫描全部的聚集索引, 也就是要扫描到查询窗口1中加锁的资源了
终止查询窗口2中的查询, 去掉我加的注释, 让 WITH(INDEX = IX_flag) 生效, 再执行查询, 你会发现处理很快执行完成.原因:
我们指定了 WITH 选项后, ``强制sql走 IX_flag 查询, 由于 IX_flag 索引并没有持有任何锁资源, 因此在这个索引上能定位到满足条件的记录, 而这个满足条件的记录对应的原始资源并未被锁定, 所以更新得以顺利进行.

Answer 15

我昨天在我的机器上也做过这样的试验：
查询一：
BEGIN TRAN
UPDATE dbo.tb SET col = col + 1
WHERE flag  = 1查询二：
UPDATE dbo.tb SET col = col + 1
WHERE flag  = 2即使flag为主键的话，查询二也能执行的。
但如果换成：
UPDATE dbo.tb SET col = col + 1
WHERE flag  <> 1
那就不行了，就进入了等待状态。

Answer 16

你会发现查询窗口2的处理被BLOCK了, 原因是默认情况下, sql直接走聚集索引(这里是主键)来扫描数据, 而要通过 flag 来定位数据的话, 要扫描全部的聚集索引, 也就是要扫描到查询窗口1中加锁的资源了
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
我觉得这儿有不当的地方，如果用聚集索引的话，应该能够直接定位到flag = 1 或2的情况，不会扫描（flag = 其他）的部分吧？我现在没有实验环境，等周一再试验一下吧。

Answer 17

主键并没有flag列, 所以不扫描表那能够判断flag值?

Answer 18

不好意思，早上那会刚起来，没看清楚。你的主键是建在id上，不是flag上的。
你的说法是正确的。
:)我觉得可以有一个折衷的方法，就是建一个可更新的视图：
Create View vTest
As
Select * From tb With(ReadPast) 
--视图定义查询使用ReadPast，不读取被锁定的列，这样的话，直接更新视图就可以了。更新的时候，更新这个视图：
查询一：
BEGIN TRAN
UPDATE vTest Set col = col + 1
WHERE flag  = 1查询二：
BEGIN TRAN
UPDATE vTest SET col = col + 1
WHERE flag  = 2

Answer 19

经过我多次调试，baidu上搜索，sqlserver不可以这样做，这也是他的并发性不能和oracle相比重要的原因之一。我也想sqlserver什么时候能做到这一点。Oracle就可以，默认隔离模式下，只对更新的当前记录加锁，其他用户看到是原数据，可以更新其他记录(当然有限制条件)。本人觉得Oracle这一点比sqlserver好的多，并发性大大提高。