请教如何在select时锁定记录
表名：Table_A
包含两列：X，Y
我每次从Table_A中取出一条有效记录（判断有效与否的标准是Y值是否为零）：
select top 1 X from Table_A where Y=0
将获取的X赋值给变量x
然后将已取出的记录设置为失效：
update Table_A set Y=1 where X=x
现在我发现当以上操作放到多线程中并发处理时会出现问题：
某线程select了一条记录但还没来得及update时
另一个线程仍然可能会进来select到同一条记录
我想知道，我能否通过修改select语句，例如加上with rowlock、readpast
使得我能够保证每次select得到的记录都不同？
谢谢
问题点数：100、回复次数：73Top
1 楼sdhylj（青锋-SS)(献血有益健康(别人的)）回复于 2006-11-27 12:10:32 得分 0
select * from table with(加锁方式)Top
2 楼sdhylj（青锋-SS)(献血有益健康(别人的)）回复于 2006-11-27 12:17:56 得分 0
锁定提示 描述
HOLDLOCK 将共享锁保留到事务完成，而不是在相应的表、行或数据页不再需要时就立即释放锁。HOLDLOCK 等同于 SERIALIZABLE。
NOLOCK 不要发出共享锁，并且不要提供排它锁。当此选项生效时，可能会读取未提交的事务或一组在读取中间回滚的页面。有可能发生脏读。仅应用于 SELECT 语句。
PAGLOCK 在通常使用单个表锁的地方采用页锁。
READCOMMITTED 用与运行在提交读隔离级别的事务相同的锁语义执行扫描。默认情况下，SQL Server 2000 在此隔离级别上操作。
READPAST 跳过锁定行。此选项导致事务跳过由其它事务锁定的行（这些行平常会显示在结果集内），而不是阻塞该事务，使其等待其它事务释放在这些行上的锁。READPAST 锁提示仅适用于运行在提交读隔离级别的事务，并且只在行级锁之后读取。仅适用于 SELECT 语句。
READUNCOMMITTED 等同于 NOLOCK。
REPEATABLEREAD 用与运行在可重复读隔离级别的事务相同的锁语义执行扫描。
ROWLOCK 使用行级锁，而不使用粒度更粗的页级锁和表级锁。
SERIALIZABLE 用与运行在可串行读隔离级别的事务相同的锁语义执行扫描。等同于 HOLDLOCK。
TABLOCK 使用表锁代替粒度更细的行级锁或页级锁。在语句结束前，SQL Server 一直持有该锁。但是，如果同时指定 HOLDLOCK，那么在事务结束之前，锁将被一直持有。
TABLOCKX 使用表的排它锁。该锁可以防止其它事务读取或更新表，并在语句或事务结束前一直持有。
UPDLOCK 读取表时使用更新锁，而不使用共享锁，并将锁一直保留到语句或事务的结束。UPDLOCK 的优点是允许您读取数据（不阻塞其它事务）并在以后更新数据，同时确保自从上次读取数据后数据没有被更改。
XLOCK 使用排它锁并一直保持到由语句处理的所有数据上的事务结束时。可以使用 PAGLOCK 或 TABLOCK 指定该锁，这种情况下排它锁适用于适当级别的粒度。
Top
3 楼zjcxc（邹建）回复于 2006-11-27 12:22:04 得分 0
1. 把select放在事务中, 否则select完成, 锁就释放了
2. 要阻止另一个select , 则要手工加锁, select 默认是共享锁, select之间的共享锁是不冲突的, 所以, 如果只是共享锁, 即使锁没有释放, 另一个select一样可以下共享锁, 从而select出数据
BEGIN TRAN
SELECT * FROM tb WITH(TABLOCKX)
UPDATE ....
COMMIT TRANTop
4 楼sxdoujg（无情过客）回复于 2006-11-27 12:32:35 得分 0
up
Top
5 楼lvlili（我爱风儿）回复于 2006-11-27 12:35:46 得分 0
参与，关注。。。。。。Top
6 楼real_name（*真名）回复于 2006-11-27 12:37:25 得分 0
BEGIN TRAN
-- 事务不提交或者回滚, 以保持锁不释放
SELECT * FROM tb WITH(UPDLOCK, READPAST)
-- UPDLOCK 让锁保留到事务结束, READPAST 跳过已经锁定的数据Top
7 楼fangle6688（）回复于 2006-11-27 13:55:25 得分 0
感谢各位的回复，不过我还是不太明确：
我希望我每次select产生的锁定不会导致下一次select失败或等待，也就是仅锁定一条记录，下一次select仍然能够立刻顺利地获得另一条记录
使用事务好像不能解决问题，它只能保证当两次调用事务select到同一条记录时，其中一次事务会因为update失败而失败，所以我需要使用一个循环来执行事务直到成功。这与我的需求不符，如果仅仅为了保证每一次select得到不同的记录而不用考虑效率，那么我完全可以使用队列排队，而不是多线程并发Top
8 楼fcuandy（了此残生.）回复于 2006-11-27 13:58:29 得分 0
我觉得实际应用中还是加状态字段来控制比较好.Top
9 楼zjcxc（邹建）回复于 2006-11-27 14:04:04 得分 0
我希望我每次select产生的锁定不会导致下一次select失败或等待，也就是仅锁定一条记录，下一次select仍然能够立刻顺利地获得另一条记录
=====================================================
select 产生的锁导致下一次select失败或者等待? 你是指并发中的另一个线程吧?
如:
real_name(*真名) ( ) 信誉：100 Blog
已经写了, 再加 READPAST 选项, 可以跳过已经锁的行, 这样, 无论你用几个线程, 那么SELECT都不会被阻塞, 它始终去读取未被锁定的行.
Top
10 楼zjcxc（邹建）回复于 2006-11-27 14:05:02 得分 0
至于锁定多少条记录的问题, sql默认的锁定行为本来就是行级锁定的, 所以你用TOP 1指定只锁定一条记录就好了/
SELECT TOP 1 * FROM tb WITH(UPDLOCK, READPAST)Top
11 楼zjcxc（邹建）回复于 2006-11-27 14:06:49 得分 0
下一次select仍然能够立刻顺利地获得另一条记录
这个问题跟你的处理逻辑有关, 在处理的记录, 已经用锁定来标识了, 你必须能够标识在你的查询方法中, 能够通过条件过滤掉已经处理完成的记录, 这个是你自己处理的时候应该考虑的问题, 与锁无关.Top
12 楼fangle6688（）回复于 2006-11-27 14:41:07 得分 0
SELECT * FROM tb WITH(TABLOCKX)
//这样做会产生应用瓶颈，好比让一条16车道的马路连接一座单车道的桥，马路再宽也要堵车的。。。
SELECT * FROM tb WITH(UPDLOCK, READPAST)
//这样的锁能对select产生影响吗？
Top
13 楼fangle6688（）回复于 2006-11-27 14:51:40 得分 0
不好意思，编辑回帖时没看到新回复
to zjcxc:
其实关键找不到合适了型的锁啊
TABLOCKX锁掉了整张表，肯定不能用；UPDLOCK是更新锁，对select没有约束力。。。Top
14 楼fangle6688（）回复于 2006-11-27 14:53:15 得分 0
“我觉得实际应用中还是加状态字段来控制比较好.”
我就是加状态字段控制的啊，但遇到并发就傻了~
Top
15 楼zjcxc（邹建）回复于 2006-11-27 19:14:37 得分 0
UPDLOCK是更新锁，对select没有约束力
---------------------------
楼主想约束的SELECT, 应该只限于你的SELECT后有更新的那此SELECT吧?
如果是那样的话, 那就不存在问题了, 因为有UPDATE的SELECT, 你都使用UPDLOCK, 这样下的都是更新锁, 而更新锁与更新锁是有冲突的, 所以是有约束力的.Top
16 楼zjcxc（邹建）回复于 2006-11-27 19:15:34 得分 0
如果要阻塞所有的SELECT, 则可以用 XLOCK,PAGLOCK 来下页级的排它锁Top
17 楼fangle6688（）回复于 2006-11-27 22:32:19 得分 0
to zjcxc:
假如我并发调用以下过程多次：
select top 1 X from Table_A with(updlock, readpast) where Y=0
update Table_A set Y=1 where X=x
请你明确告诉我：有没有可能有两次select到同一条记录？
我认为这样加锁，实际上只是防止了两次update同一条记录
而并不会防止两次select同一条记录
在select上加锁导致update失败从而间接导致整个过程失败，这并不是我需要的结果
因为这样仅仅保证了功能需求，而对性能需求是起反作用的
我必须增加额外的循环开销来处理如果失败
这样的话我还不如放弃并发，老老实实地等待update成功了再select
Top
18 楼sunheartlee（心晴）回复于 2006-11-28 10:36:05 得分 0
select * from tb with(tablockx)Top
19 楼biao1（蓝色代码）回复于 2006-11-28 10:42:07 得分 0
顶 接分Top
20 楼OnlyAlone（）回复于 2006-11-28 11:21:46 得分 0
关注Top
21 楼antony1029（鹏月）回复于 2006-11-28 11:42:50 得分 0
学习了！Top
22 楼xjchen（星际浪子）回复于 2006-11-28 11:46:47 得分 0
set rowcount 1 -- 设置取最新一条记录
begin tran
update table set @X=x,flag=1 where flag=0 -- 取出同时设置已读
commit tran
select @XTop
23 楼tianyan316（与狼共舞,舞者岂是羊）回复于 2006-11-28 11:55:50 得分 0
学习了`Top
24 楼zjcxc（邹建）回复于 2006-11-28 12:15:10 得分 0
select top 1 X from Table_A with(updlock, readpast) where Y=0
update Table_A set Y=1 where X=x
看你的两次怎么算, 如果第1次的已经提交事务了, 第2次的select才发出, 则第2次很有可能取得第1次的记录, 否则是不可能的.Top
25 楼zjcxc（邹建）回复于 2006-11-28 12:15:41 得分 0
这也是我提到的另一个问题:
下一次select仍然能够立刻顺利地获得另一条记录
这个问题跟你的处理逻辑有关, 在处理的记录, 已经用锁定来标识了, 你必须能够标识在你的查询方法中, 能够通过条件过滤掉已经处理完成的记录, 这个是你自己处理的时候应该考虑的问题, 与锁无关.
Top
26 楼chuifengde（树上的鸟儿）回复于 2006-11-28 12:25:12 得分 0
markTop
27 楼real_name（*真名）回复于 2006-11-28 12:39:39 得分 0
:)Top
28 楼wewaa（C/C++）回复于 2006-11-28 13:07:30 得分 0
路过Top
29 楼J2MELOVER（）回复于 2006-11-28 14:02:30 得分 0
高手是不是都有钻石呀Top
30 楼shawnwan（）回复于 2006-11-28 14:35:24 得分 0
当作事务处理啊Top
31 楼goodbee（采蜂）回复于 2006-11-28 16:00:40 得分 0
好多钻石Top
32 楼ken2002（尖刀）回复于 2006-11-28 16:34:47 得分 0
好多钻呀
关注Top
33 楼babybattle（好好学习）回复于 2006-11-28 17:10:58 得分 0
学习。
顺便看下我有没有钻石。Top
34 楼CrazyGou（）回复于 2006-11-28 17:29:08 得分 0
markTop
35 楼iewin（风铃声）回复于 2006-11-28 17:30:30 得分 0
学习了
Top
36 楼zhuaiman（米米涨了(︶︿︶)╭∩╮）回复于 2006-11-28 17:36:20 得分 0
学习Top
37 楼mb459（）回复于 2006-11-28 17:55:11 得分 0
xuexiTop
38 楼liuxingjia000（）回复于 2006-11-28 19:43:51 得分 0
顶Top
39 楼cadenza7（）回复于 2006-11-28 19:55:25 得分 0
select top 1 X from Table_A where Y=0 FOR UPDATE
取值的时候就锁表,然后接着更新.
update Table_A set Y=1 where X=x
Top
40 楼yangxjn（一条）回复于 2006-11-28 20:14:20 得分 0
收藏Top
41 楼jia6867489（）回复于 2006-11-28 21:14:12 得分 0
你好深奥啊Top
42 楼cyyno1（GunRose）回复于 2006-11-28 22:27:02 得分 0
吼吼
我爱钻石
吼吼
select X from Table_A where Y=0 for update//行锁,选中多少行纪录锁多少行纪录
在遇到commit 或 rollback后解锁Top
43 楼zxq810213（乖猫猫）回复于 2006-11-29 09:26:07 得分 0
markTop
44 楼handsomebird123（handsomebird123）回复于 2006-11-29 10:00:37 得分 0
markTop
45 楼king2003（鱼）回复于 2006-11-29 11:28:23 得分 0
呵好问题呀!我前段时间也遇到了这种问题.进来学习一下MARKTop
46 楼king2003（鱼）回复于 2006-11-29 11:50:09 得分 0
select top 1 X from Table_A with(updlock, readpast) where Y=0
update Table_A set Y=1 where X=x
看你的两次怎么算, 如果第1次的已经提交事务了, 第2次的select才发出, 则第2次很有可能取得第1次的记录, 否则是不可能的.
zjcxc(邹建)：不明白你说的这句话呀！
如果第1次的已经提交事务了, 第2次的select才发出, 则第2次很有可能取得第1次的记录
他那个ＳＥＬＥＣＴ是有条件限制的就是说第一次事务结束之后Y就等于１了．按道理来说第二次是不应该取到重复的呀！
Top
47 楼junmail（浪子）回复于 2006-11-29 13:19:20 得分 0
mark，学习！Top
48 楼net205（人不可以无耻到这种地步）回复于 2006-11-29 13:21:31 得分 0
学习..Top
49 楼bugchen888（臭虫）回复于 2006-11-29 14:19:46 得分 0
real_name(*真名)的是正解,楼主可以执行一下试试.
BEGIN TRAN
-- 事务不提交或者回滚, 以保持锁不释放
SELECT * FROM tb WITH(UPDLOCK, READPAST)
-- UPDLOCK 让锁保留到事务结束, READPAST 跳过已经锁定的数据
sp_locks @@spid
-- 查看被锁定的资源
Top
50 楼zhaixing0101（风轻云淡）回复于 2006-11-29 17:04:41 得分 0
Mark!Top
51 楼lysh137856（黎宏宇）回复于 2006-11-29 17:43:24 得分 0
關注中Top
52 楼zjcxc（邹建）回复于 2006-11-29 20:10:45 得分 0
如果第1次的已经提交事务了, 第2次的select才发出, 则第2次很有可能取得第1次的记录
他那个ＳＥＬＥＣＴ是有条件限制的就是说第一次事务结束之后Y就等于１了．按道理来说第二次是不应该取到重复的呀！
-----------------------------------
既然你有条件保证, 那当然不会取到了, 我说的是没有条件可保证的情况下.Top
53 楼jackeyabc（过些天跟布什二女儿订婚)）回复于 2006-11-29 20:23:03 得分 0
收藏先~Top
54 楼voice007（）回复于 2006-11-29 20:36:28 得分 0
mark!Top
55 楼net205（人不可以无耻到这种地步）回复于 2006-11-29 21:09:23 得分 0
with(updlock, readpast)好像不行，READPAST设置并没有跳过已经锁定的数据，
当进程A在执行一个带with(updlock, readpast)的事务时，如果没有执行完成，另进程B会一直等待进程A执行完后才能执行，所以应该不满足楼主的要求吧
我是这样操作的，不知道有没有问题
create table TmpTable (id int primary key,x nvarchar(20),y int)
insert into TmpTable
select 1,N'用户A',0 union all
select 2,N'用户B',1 union all
select 3,N'用户C',0
select * from TmpTable
在分析器中开2个查询窗口
declare @ix nvarchar(20),@i int
set @i=10000000
begin tran
select top 1 @ix=x FROM TmpTable WITH(UPDLOCK, READPAST) where y=0
print @ix
--注释
while @i>0
begin
set @i=@i-1
end
update TmpTable Set y=1 Where x=@ix
commit tran
执行以上语句时，同时执行以下语句
declare @ix nvarchar(20),@i int
set @i=10000000
begin tran
select top 1 @ix=x FROM TmpTable WITH(UPDLOCK, READPAST) where y=0
print @ix
update TmpTable Set y=1 Where x=@ix
commit tran
--drop table TmpTable
结果第2个查询会出现：
服务器: 消息 1205，级别 13，状态 50，行 8
事务（进程 ID 60）与另一个进程已被死锁在 lock 资源上，且该事务已被选作死锁牺牲品。请重新运行该事务。
要是执行第一段语句"--注释"前部分，然后执行第二段整个语句，此时第二段语句也会一直在等待状态(我最长等了5分钟)，然后执行第一段的commit tran，第二段也就马上执行完成，结果为：用户C(这儿应该就是READPAST的作用)
Top
56 楼china2wto（china2wto）回复于 2006-11-29 21:14:29 得分 0
select * from table1 for updateTop
57 楼net205（人不可以无耻到这种地步）回复于 2006-11-29 21:17:21 得分 0
顶上，继续学习Top
58 楼xiaoku（野蛮人(^v^)）回复于 2006-11-30 09:36:42 得分 0
收藏Top
59 楼alongsun（）回复于 2006-11-30 09:42:59 得分 0
我以前做项目时也遇到过这种类似的问题,我们是这样解决的.用的数据库是oracle.
就想上面说的,用for update nowait 锁住选出来的记录,一定要加nowait,不加的话,SQL文就等着上一个提交后才能执行.这时如果再有别的事务里的SQL再选这些记录的话,就会发生OracleException的,oracle的错误code是54,你可以判断这个值,然后再循环几次再试着取几次,因为被锁的记录事务提交后就释放了,别的SQL就可以再取得.你可以试多少次以后还能取得的话,就可以转到EEROR处理中去.
不知道说明白了没有,如果不清楚,大家再讨论.Top
60 楼alongsun（）回复于 2006-11-30 09:44:33 得分 0
不好意思,少了一个字.
上面应该是<你可以试多少次以后还不能取得的话,就可以转到EEROR处理中去.>Top
61 楼zjcxc（邹建）回复于 2006-11-30 20:31:13 得分 0
net205(培养一批有理想有道德有文化有纪律的新时代四有小姐)
你的测试写得有些问题的
你的UPDATE和SELECT 是以 x 列做条件的, 要扫描x列满足条件, 当然要扫描表中的所有数据
而第1个查询窗口中的 UPDATE 已经锁定了记录, 因为你没有提交数据
所以第2个查询窗口的处理中, SELECT是可以过去的, 因为有READPAST, 这点你可以把UPDATE去掉来证明
而对于UPDATE, 因为UPDATE前是要先定位记录才来进行更新的, 实际上也是做了一个SELECT, 这个SELECT并没有READPAST, 当然扫描到查询1中被锁定的记录时, 就会停下来等待
因此才会有你的测试结果.Top
62 楼zjcxc（邹建）回复于 2006-11-30 20:32:05 得分 0
如果你在x列上建立一个索引, 则不会有这种情况了.Top
63 楼erqie（）回复于 2006-12-01 09:02:41 得分 0
又学习了一把。还没有涉及过锁的问题。看来多线程操作确实复杂Top
64 楼happyamei（啊梅）回复于 2006-12-01 11:53:47 得分 0
收藏一下Top
65 楼net205（人不可以无耻到这种地步）回复于 2006-12-01 13:51:20 得分 0
呵呵，体会到了，多谢邹老大指点....Top
66 楼zhmvb（星期六）回复于 2006-12-01 14:46:50 得分 0
markTop
67 楼king2003（鱼）回复于 2006-12-04 11:52:56 得分 0
而第1个查询窗口中的 UPDATE 已经锁定了记录, 因为你没有提交数据
他后面不是有COMMIT吗?邹建老师Top
68 楼king2003（鱼）回复于 2006-12-05 15:37:39 得分 0
加锁之后如果另一个线程再读你这个加锁的记录的话会等待还是怎么样呀!Top
69 楼linwu（木木）回复于 2006-12-06 09:31:08 得分 0
markTop
70 楼shuqinpeng（苏秦）回复于 2006-12-06 10:37:30 得分 0
markTop
71 楼gwallan（）回复于 2006-12-06 14:08:04 得分 0
markTop
72 楼crazyflower（小狂花，学习中……）回复于 2006-12-06 14:10:40 得分 0
过来捧场，学习学习Top
73 楼xjchen（星际浪子）回复于 2007-01-26 10:43:14 得分 0
mark一下Top
