请教关于锁的问题？

Question

楼主用触发器
create table test_ta(id  char(3),name varchar(10))
create trigger test_ta_tr on test_ta
for insert
as
declare @i int
set @i=0
set nocount on
update test_ta
set id=replicate('0',3-len((select isnull(max(cast(id as int)),0) from test_ta)+@i))+
cast((select isnull(max(cast(id as int)),0) from test_ta)+@i as varchar)
,@i=@i+1
where id is null
set nocount off
--测试一次增加999记录
declare @i int,@j int
select @i=1,@j=999
while @i!>@j
begin
set nocount on
insert test_ta(name) VALUES(@i)
set nocount off
select @i=@i+1
end

Answer 1

这里有一个关于处理并发（锁）列子
USE tempdbGO CREATE TABLE dbo.tb(id int identity(1, 1), name nvarchar(128)) INSERT tb(name)SELECT TOP 100 nameFROM syscolumns
-- 查询窗口发出下面的查询语句
--   模拟第1个用户
BEGIN TRAN        -- 事务不提交或者回滚, 以保持锁不释放SET ROWCOUNT 20SELECT * FROM tb WITH(UPDLOCK, READPAST)        -- UPDLOCK 让锁保留到事务结束, READPAST 跳过已经锁定的数据
--   模拟第2个用户
-- 查询窗口发出下面的查询语句BEGIN TRAN        -- 事务不提交或者回滚, 以保持锁不释放SET ROWCOUNT 20SELECT * FROM tb WITH(UPDLOCK, READPAST)        -- UPDLOCK 让锁保留到事务结束, READPAST 跳过已经锁定的数据
--提示
你会看到查询窗口1列出了前20条数据查询窗口2列出了21-40条数据 这样就实现了不同的用户取不同数据的需求. 注: 处理完成后, 删除记录, 然后提交事务就可以了

Answer 2

用2楼方法：先建一个模拟数据表：
CREATE TABLE test (id int identity(1,1),name nvarchar(128))INSERT test (name) SELECT TOP 100 name FROM syscolumns
然后在窗口1执行：BEGIN TRAN
        -- 事务不提交或者回滚, 以保持锁不释放
SELECT min(id) FROM test WITH(UPDLOCK, READPAST)--得出最小ID 1在窗口2执行：BEGIN TRAN
        -- 事务不提交或者回滚, 以保持锁不释放
SELECT min(id) FROM test WITH(UPDLOCK, READPAST)--得出最小ID 是NULL，但是我想得到2

Answer 3

我用2楼的试了，不行，上面说了我的测式情况。再有：我试了结束事务，但窗口2还得出NULL。

Answer 4

用2楼方法：
先建一个模拟数据表：
CREATE TABLE test (id int identity(1,1),name nvarchar(128))
INSERT test (name) SELECT TOP 100 name FROM syscolumns
-------------
你的流水表中流水号字段是自增的吗？

Answer 5

楼主要以下这样递增的效果，不用人为新增。用我上面的触发器就可以实现。
0001
0002
锁的问题
BEGIN TRAN
SET ROWCOUNT 1SELECT min(id)FROM tb WITH(UPDLOCK, READPAST)

Answer 6

TO:roy_88(论坛新星_燃烧你的激情！！)我用的方法：先在窗口1执行：
--窗口1BEGIN TRAN
SET ROWCOUNT 1
sELECT min(id) FROM test WITH(UPDLOCK, READPAST)--得出 0001然后在窗口2执行:
--窗口2BEGIN TRAN
SET ROWCOUNT 1
sELECT min(id) FROM test WITH(UPDLOCK, READPAST)--得出NULL--想得出0002
另外解锁呢？

Answer 7

BEGIN TRAN
SET ROWCOUNT 1
SELECT top 1 id FROM ta WITH(UPDLOCK, READPAST) order by id asc
COMMIT TRAN--提交事务rollback tran还原事务

Answer 8

create table ta(id int)
truncate table tadeclare @i int,@j int
select  @i=1 ,@j=20
while @i!>@j
begin
insert ta(id)values(@i)
set @i=@i+1
end
以上测试表
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRAN
SET ROWCOUNT 1
SELECT top 1 * FROM ta WITH(UPDLOCK, READPAST) 
窗口2
BEGIN TRAN
SET ROWCOUNT 1
SELECT top 1 * FROM ta WITH(UPDLOCK, READPAST) 
查询完
COMMIT TRAN在窗口1、2提交事务
SET ROWCOUNT 0--还原设置

Answer 9

按照你的要求：
1、产生测试表
create table ta(id int)
truncate table tadeclare @i int,@j int
select  @i=1 ,@j=20
while @i!>@j
begin
insert ta(id)values(@i)
set @i=@i+1
end
2、
--窗口1（查询分析器）代码
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GOBEGIN TRAN
SET ROWCOUNT 1
SELECT top 1 * FROM ta WITH(UPDLOCK, READPAST) order by id asc3、
--窗口2（查询分析器）代码
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GOBEGIN TRAN
SET ROWCOUNT 1
SELECT top 1 * FROM ta WITH(UPDLOCK, READPAST) order by id asc
4、先执行窗口1代码，得出
ID 
15、然后执行窗口2代码，没有得出记录
（所影响的行数为 0 行）---------------我想得出
ID
26、在窗口1执行：
COMMIT TRAN --提交事务
SET ROWCOUNT 0 --还原设置7、再执行窗口2代码得出
ID 
1

Answer 10

create  proc serial_read
as
BEGIN TRAN
declare @id int
       
       select @id=-1,@id=min(id) from test     
       while @id<>-1
begin                            
     
              if exists(SELECT id FROM test with(updlock,readpast) where id=@id)  --判断最小值是否已被其它用户读取 
                    begin
                        select * from test where id=@id
        break                                    --取得最小值，退出
                    end 
           else
                   begin
                     set  
                     select top 1 @id=-1,@id=id from test where id>@id order by id --最小值被其它用户读取，重新读最小值　               
                   end   
       end         waitfor delay '00:00:5'
rollback tranGO

Answer 11

MakeToday(改变)的方法不错。按照我要求还要改动一下，有结果了告诉大家，再次感谢！调试完一点结帖！

Answer 12

给个思路：
1、给流水表加个字段，如 flag，值为 Y, N, Y 时为已有人读取,初值为 N.
2、A 用户读取后将 flag update 成 Y，update 语句如下：
   
   Select min(lsh) from table where flag = 'N'   Update table set flag = 'Y' 
   where lsh = '00001' and flag = 'N'
   
3、如果已有人读取，update 失败。继续读下一个。

Answer 13

TO:iamx(阿麦) 如果有两个客户端同时执行你的第2步呢？

Answer 14

基本上赞同一楼的方法，流水号用insert触发器生成。

Answer 15

TO: libin_ftsafe(子陌红尘：当libin告别ftsafe)  
   基本上赞同一楼的方法，流水号用insert触发器生成。-----------------------------------------------------产生流水号没有问题，我用种子字段
1
2
3不用加00也可以。
主要问题是多个客户端有可能同时取最小流水号，然后加个标志，不让其他客户取重复了。
  
 

Answer 16

触发器不会存在并发的问题，多个客户端有可能同时取最小流水号，但在触发器的触发顺序上仍然存在一个先后的顺序
------------------------------是这样的：流水表（流水号是种子字段）
流水号 标志
1       0
2       0
3       0客户端A （只有一个）客户端A不定期Insert一个0到标志字段
(INSERT INTO 流水表 (标志) VALUES (0))
客户端B有多个，取标志为0最小流水号，然后修改标志为1。
因为有多个客户端B，所以存在并发问题。

Answer 17

基本解决了，正在调试 MakeToday(改变)的方法。

Answer 18

TO:iamx(阿麦) 如果有两个客户端同时执行你的第2步呢？那是不可能的。所有SQL都要一条一条来执行的。假设有两个人A， B，步骤A1， A2， B1， B2
A1， B1 是 Select, A2, B2 是 UpdateA1 -> A2 -> B1 -> B2 没问题
A1 -> B1 -> A2 -> B2 B会失败，要重取号码
A1 -> B1 -> B2 -> A2 A会失败，要重取号码

Answer 19

而且，你必须要有标志来标识这个流水号已被用过，要不然后面的人还会用这个号的，除非Select 完之后把它删除了，在删除之前还是有人可以取到这个号的。

Answer 20

更正： Select 完之后马上删除是可以的：select id from table  //假设取出的 id 是 001delete table where id = '001'
if sqlnrows <> 1 then
   //失败
end if

Answer 21

A1 -> B1 -> A2 -> B2 B会失败，要重取号码
A1 -> B1 -> B2 -> A2 A会失败，要重取号码那请楼上写段代码，好吗？谢了。

Answer 22

SQL Server 好久没摸了，借 MakeToday(改变)的代码来用一下(有标志的情况)：create  proc serial_read
as
BEGIN TRAN
declare @id int
declare @rv int
       
       select @rv = 0
       
       while @rv <> 1
           select @id=min(id) from test            update test set flag = 'Y' 
           where id = @id
           
           if sqlcode <> -1 then
              select @rv = 1
              commit tran
           else 
              select @rv = -1
              rollback tran
              waitfor delay '00:00:5'
           end
      endGO

Answer 23

无标志的情况（select 后立即删除)create  proc serial_read
as
BEGIN TRAN
declare @id int
declare @rv int
       
       select @rv = 0
       
       while @rv <> 1
           select @id=min(id) from test            delete test where id = @id
           
           if sqlnrows = 1 then
              select @rv = 1
              commit tran
           else 
              select @rv = -1
              rollback tran
              waitfor delay '00:00:5'
           end
      endGO

Answer 24

有标志的情况有个 bug:select @id=min(id) from test 应为：select @id=min(id) from test where flag = 'N'

Answer 25

我只是给个思路，具体代码你要自己测试，我这里没有 SQL Server, 也不知道语法对不对。

Answer 26

我的天，有标志的情况还有一个 bug:update test set flag = 'Y' 
           where id = @id
应为：update test set flag = 'Y' 
           where id = @id and flag = 'N'还有 update 之后应判断 sqlnrows 是否为 1, 判断 sqlcode = 1 可能不行。具体要查一下 SQL server 的用户手册了。

Answer 27

--好多分的帖子啊~~:)
--個人愚見,LZ好像最終的目的是update id最小的那條,不妨create trigger.
/*建立測試表*/
CREATE TABLE test (id int identity(1,1), name nvarchar(128))INSERT test (name) SELECT TOP 100 name FROM syscolumns 
GO
/*建立update觸發器*/
create trigger t1 on test
for update
as
update test 
set [name]='abc' 
where id in (select min(id) from test where [name]<>'abc')GO/*不妨執行SQL命令:*/
update test set name=name

Answer 28

看了一下，其实二楼 roy_88(论坛新星_燃烧你的激情！！) 的思路也是可行的。

Answer 29

这是我的代码，大家看看有没有BUG。另外，不用事务和时间间隔(waitfor delay '00:00:5')会不会有问题CREATE  proc MyTest
as
set nocount on
declare @lnID int,@lnJ int
set @lnJ=0
while @lnJ <> 1
begin
select @lnID=min(流水号) from 流水表 where 状态=0 --状态为0的是没有取过的
update 流水表 set 状态=1 where 流水号=@lnID and 状态=0
if @@rowcount = 1
begin
break --取号成功
end
else  --取号失败
begin
if @lnID is null
begin
break
end
end
end
set nocount off
select @lnID as 流水号,Getdate() as 取号时间,count(*) as 剩余未取数 from 流水表 where 状态=0 and 流水号<>@lnID
GO

Answer 30

else 之后不应该 break 吧？要不然就不要循环，把循环放在客户端。事务是一定要的，除非你在客户端提交事务。 wait for delay 可以不要。

Answer 31

建议楼主对流水表加一个字段，比如Flag bit default 0
当取的时候，首先判断Flag的值，如果是0，首先更新Flag = 1, 然后取出要取的值（...），最后再把Flag更新为0，结束。
判断Flag如果为1，则等待(waitfor delay '00:00:00.500') --半秒钟，然后再判断Flag值，直到Flag = 0时，进入，如上。
这样也就起到并发处理的效果了