sql server 更新表数据时，会给表加排他锁，如何解除排他锁
当sql server ，更新一个表时，此时 sql server会给 表加一个排他锁，但是其他用户要更新时，却不得不等第一个用户更新完，才能更新，如何解除 排他锁，禁止sql server自动给这个表加排他锁 问题点数：100、回复次数：27Top
1 楼fengfangfang（）回复于 2006-06-28 17:04:04 得分 0
不可以Top
2 楼LouisXIV（夜游神）回复于 2006-06-28 17:15:48 得分 0
lz的想法会造成问题的^^Top
3 楼zjcxc（邹建）回复于 2006-06-28 20:28:21 得分 60
不可能实现.Top
4 楼zjcxc（邹建）回复于 2006-06-28 20:30:14 得分 0
不加锁的话, 两个用户同时更新一条记录, 那数据库中保存的应该是那个用户的记录呢?
如果只能保存一个用户的数据, 那么返回信息的时候, 得给一个用户失败的错误信息, 那这样还不如加锁让一个用户等待?
如果两个用户都返回成功的信息, 那更不对了, 明明只保存了一个用户的数据嘛.Top
5 楼suntt（两条腿的狗）回复于 2006-06-28 20:59:40 得分 0
你只能想办法降低锁的粒度，尽量将在表上排他锁降低到页或行级别Top
6 楼LouisXIV（夜游神）回复于 2006-06-28 21:09:52 得分 0
^^
老大说的浅显易懂Top
7 楼li_zero（相遇太早，反而淡了缘分）回复于 2006-06-28 22:01:12 得分 0
可以更改，参见SET TRANSACTION ISOLATION LEVEL，不过从数据角度来考虑，这个很不建议使用。
事务必须运行于可重复读或更高的隔离级别以防止丢失更新。当两个事务检索相同的行，然后基于原检索的值对行进行更新时，会发生丢失更新。如果两个事务使用一个 UPDATE 语句更新行，并且不基于以前检索的值进行更新，则在默认的提交读隔离级别不会发生丢失更新。
Top
8 楼lzhs（快乐至上）回复于 2006-06-29 10:56:22 得分 40
可以更改，参见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之后，才会执行完成。
Top
9 楼li_zero（相遇太早，反而淡了缘分）回复于 2006-06-29 11:34:26 得分 0
楼上的你写错了吧，Set Transaction Isolation Level READ UNCOMMITTED 要对每个连接执行，并且要再begin tran之前执行，我试过的。Top
10 楼lzhs（快乐至上）回复于 2006-06-29 16:25:53 得分 0
。。。
Set 就算是写在之前也不行的。
Top
11 楼zjcxc（邹建）回复于 2006-06-29 19:39:53 得分 0
TO: li_zero(相遇太早，缘分太浅)
你说的和楼主要的不是同一个东西
READ UNCOMMITTED
指定语句可以读取已由其他事务修改但尚未提交的行。
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^== 这个是最低的事务隔离级别
^^^^ 看见了吗, 这里是读取, 而楼主要的是更新不加锁
在 READ UNCOMMITTED 级别运行的事务，不会发出共享锁来防止其他事务修改当前事务读取的数据。READ UNCOMMITTED 事务也不会被排他锁阻塞，排他锁会禁止当前事务读取其他事务已修改但尚未提交的行。设置此选项之后，可以读取未提交的修改，这种读取称为脏读。在事务结束之前，可以更改数据中的值，行也可以出现在数据集中或从数据集中消失。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 NOLOCK 相同。这是隔离级别中限制最少的级别。
Top
12 楼zjcxc（邹建）回复于 2006-06-29 19:43:49 得分 0
简单地说, READ UNCOMMITTED 更改的是 SELECT 加锁的行为, 即不让 SELECT 加锁
SQL通过锁之间的相容或者相斥来实现那些操作与那些操作能或者不能同时进行.
而 READ UNCOMMITTED 让 SELECT 不加锁, 既然没有锁, 当然不会产生与其他锁是否相容或者相斥的问题, 从而跳过了 update 下的排它锁
但它并不会让 update 不下排它锁Top
13 楼zjcxc（邹建）回复于 2006-06-29 19:51:54 得分 0
-- 可以用下面的代码来测试, 说明 UPDATE 加的排它锁
-- 窗口1执行下面的代码
CREATE TABLE dbo.tb(id int)
INSERT dbo.tb SELECT id FROM sysobjects
GO
SET 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 锁.Top
14 楼lzhs（快乐至上）回复于 2006-06-30 09:44:04 得分 0
还可以这样验证，执行下面的语句：
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锁没有了。
Top
15 楼jupiterhunter（木猎）回复于 2006-06-30 15:30:21 得分 0
感谢各位的积极留言，我的问题难道走到死胡同了吗？
我遇到的问题，我写了一个存储过程，６个用户同时更新 一个表的内容，并且是各自更新各自的数据，互不干扰，比如
第1个用户 update 表１ set 字段１='1' where flag=1
第2个用户 update 表１ set 字段１='1' where flag=2
第３个用户 update 表１ set 字段１='1' where flag=３
当第一个用户执行时，第二个用户和第三个用户都要等待，怎么才能 让三个用户同时执行，提高速度和效率，难道真的要把 表１，分成多个表？
Top
16 楼lzhs（快乐至上）回复于 2006-06-30 17:36:52 得分 0
第1个用户 update 表１ set 字段１='1' where flag=1
第2个用户 update 表１ set 字段１='1' where flag=2
第３个用户 update 表１ set 字段１='1' where flag=３
======================================================
这些语句不会造成等待吧？
Top
17 楼zjcxc（邹建）回复于 2006-06-30 20:55:38 得分 0
这个难说, 如果 update 的条件不必扫描被锁定的资源, 就可以定位到所有满足条件的记录, 那自然就不会被锁住.
否则当然会被锁住.Top
18 楼zjcxc（邹建）回复于 2006-06-30 20:58:26 得分 0
还是有示例比较能说明问题:
-- 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
GO
ALTER TABLE dbo.tb ADD PRIMARY KEY(id)
CREATE INDEX IX_flag ON dbo.tb(flag)
GO
--DROP TABLE dbo.tbTop
19 楼zjcxc（邹建）回复于 2006-06-30 20:59:15 得分 0
-- 查询窗口1 中执行下面的查询
BEGIN TRAN
UPDATE dbo.tb SET col = col + 1
WHERE flag = 1
--ROLLBACK TRAN -- 让事务一直保持, 以保持对数据下的锁Top
20 楼zjcxc（邹建）回复于 2006-06-30 20:59:41 得分 0
-- 查询窗口2 中执行下面的查询
BEGIN TRAN
UPDATE A SET col = col + 1
FROM dbo.tb A
-- WITH(INDEX = IX_flag)
WHERE flag = 2
ROLLBACK TRAN
Top
21 楼zjcxc（邹建）回复于 2006-06-30 21:03:33 得分 0
结果:
你会发现查询窗口2的处理被BLOCK了, 原因是默认情况下, sql直接走聚集索引(这里是主键)来扫描数据, 而要通过 flag 来定位数据的话, 要扫描全部的聚集索引, 也就是要扫描到查询窗口1中加锁的资源了
终止查询窗口2中的查询, 去掉我加的注释, 让 WITH(INDEX = IX_flag) 生效, 再执行查询, 你会发现处理很快执行完成.
原因:
我们指定了 WITH 选项后, ``强制sql走 IX_flag 查询, 由于 IX_flag 索引并没有持有任何锁资源, 因此在这个索引上能定位到满足条件的记录, 而这个满足条件的记录对应的原始资源并未被锁定, 所以更新得以顺利进行.Top
22 楼lzhs（快乐至上）回复于 2006-07-01 10:31:16 得分 0
我昨天在我的机器上也做过这样的试验：
查询一：
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
那就不行了，就进入了等待状态。
Top
23 楼lzhs（快乐至上）回复于 2006-07-01 10:34:44 得分 0
你会发现查询窗口2的处理被BLOCK了, 原因是默认情况下, sql直接走聚集索引(这里是主键)来扫描数据, 而要通过 flag 来定位数据的话, 要扫描全部的聚集索引, 也就是要扫描到查询窗口1中加锁的资源了
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
我觉得这儿有不当的地方，如果用聚集索引的话，应该能够直接定位到flag = 1 或2的情况，不会扫描（flag = 其他）的部分吧？
我现在没有实验环境，等周一再试验一下吧。
Top
24 楼zjcxc（邹建）回复于 2006-07-01 19:56:25 得分 0
主键并没有flag列, 所以不扫描表那能够判断flag值?Top
25 楼zjcxc（邹建）回复于 2006-07-01 20:01:33 得分 0
至于查询走不走主键, 这个是通过查询计划来看的
不同的表结构和不同的数据, 会让同一个处理语句的执行计划不同, 所以换语句, 或者换数据, 都可能不走主键.
而我要说明的, 只是说更新的扫描, 如果不扫描到被锁定的资源, 就不会被堵塞.
(我的例子只是来说明这一点)Top
26 楼lzhs（快乐至上）回复于 2006-07-01 20:46:00 得分 0
。。。
不好意思，早上那会刚起来，没看清楚。你的主键是建在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
Top
27 楼seayar（习习）回复于 2006-07-26 10:00:28 得分 0
学习Top
