事务、锁以及存储过程的并发问题

Question

设：表[T]有一字段num,int类型
---- ------
[id]  [num]
1     1
存储过程如下：
begin tran
declare @N int
select @N=[num] from [T] where [id]=1
if(@N<2)
update [T] set [num]=[num]+1 where [id]=1
commit tran要求：执行完存储过程后，表中的num值不能大于２，但如果有两个用户同一时间执行上面存储过程的话，就会出现num的值为３．
请教如果解决？注意：请不要将select和update写成一句

Answer 1

--是这个意思吗?没发现问题啊...
create table t(id int, num int)
insert into t 
select 1,1
begin tran
declare @N int
select @N=[num] from [T] where [id]=1
if(@N<2)
    update [T] set [num]=[num]+1 where [id]=1
commit tran
begin tran
declare @n1 int
select @n1=[num] from [T] where [id]=1
if(@n1<2)
    update [T] set [num]=[num]+1 where [id]=1
commit tran

Answer 2

按楼上并不是真正的同一时间执行啊.
begin tran
declare @N int
select @N=[num] from [T] where [id]=1--两用户同一时间点执行存储过程，取得@N的值都为１
if(@N<2)--都符合此条件
    update [T] set [num]=[num]+1 where [id]=1--两用户都执行了这句，num的值变为了３
commit tran

Answer 3

--转一个贴
设table1(A,B,C)   
  A         B         C   
  a1       b1       c1   
  a2       b2       c2   
  a3       b3       c3   
    
  1）排它锁   
  新建两个连接   
  在第一个连接中执行以下语句   
  begin   tran   
        update   table1   
        set   A='aa'   
        where   B='b2'   
        waitfor   delay   '00:00:30'     --等待30秒   
  commit   tran   
  在第二个连接中执行以下语句   
  begin   tran   
        select   *   from   table1   
        where   B='b2'         
  commit   tran   
    
  若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒   
    
  2）共享锁   
  在第一个连接中执行以下语句   
  begin   tran   
        select   *   from   table1   holdlock   -holdlock人为加锁   
        where   B='b2'     
        waitfor   delay   '00:00:30'     --等待30秒   
  commit   tran   
    
  在第二个连接中执行以下语句   
  begin   tran   
        select   A,C   from   table1   
        where   B='b2'     
        update   table1   
        set   A='aa'   
        where   B='b2'         
  commit   tran   
    
  若同时执行上述两个语句，则第二个连接中的select查询可以执行   
  而update必须等待第一个连接中的共享锁结束后才能执行   即要等待30秒   
    
  3）死锁   
  增设table2(D,E)   
  D         E   
  d1       e1   
  d2       e2   
  在第一个连接中执行以下语句   
  begin   tran   
        update   table1   
        set   A='aa'   
        where   B='b2'     
        waitfor     delay   '00:00:30'   
        update   table2   
        set   D='d5'   
        where   E='e1'     
  commit   tran   
          
  在第二个连接中执行以下语句   
  begin   tran   
        update   table2   
        set   D='d5'   
        where   E='e1'     
        waitfor     delay   '00:00:10'   
        update   table1   
        set   A='aa'   
        where   B='b2'       
  commit   tran   
    
  同时执行，系统会检测出死锁，并中止进程

Answer 4

1   如何锁一个表的某一行   
  A   连接中执行   
  SET   TRANSACTION   ISOLATION   LEVEL   REPEATABLE   READ   
  begin   tran   
  select   *   from   tablename   with   (rowlock)   where   id=3   
  waitfor   delay   '00:00:05'   
  commit   tran   
  B连接中如果执行   
  update   tablename   set   colname='10'   where   id=3   --则要等待5秒   
  update   tablename   set   colname='10'   where   id<>3   --可立即执行   

Answer 5

--试试这样,看会不会发生你说的情况....
SET   TRANSACTION   ISOLATION   LEVEL   REPEATABLE   READ   
begin tran
declare @N int
select @N=[num] from [T] with   (rowlock)  where [id]=1
if(@N<2)
    update [T] set [num]=[num]+1 where [id]=1
commit tran

Answer 6

加行级排他锁begin tran
declare @N int
select @N=[num] from [T] with WITH (ROWLOCK   XLOCK   readpast) where [id]=1
if(@N<2)
    update [T] set [num]=[num]+1 where [id]=1
commit tran

Answer 7

多打了个with,不好意思...begin tran
declare @N int
select @N=[num] from [T] with (ROWLOCK   XLOCK   readpast) where [id]=1
if(@N<2)
    update [T] set [num]=[num]+1 where [id]=1
commit tran

Answer 8

是我疏忽了，收回8楼的话扩大时间差就明显了：
第一个用户开始：
begin tran
declare @N int
select @N=[num] from [T] where [id]=1
waitfor  delay  '00:00:10'
if(@N<2)
    update [T] set [num]=[num]+1 where [id]=1
    waitfor  delay  '00:00:05'
commit tran--然后，随着第二个用户开始：begin tran
declare @N int
select @N=[num] from [T] where [id]=1
waitfor  delay  '00:00:20'
if(@N<2)
    update [T] set [num]=[num]+1 where [id]=1
commit tran

Answer 9

代码他们都贴了很多了,
你自己在研究研究.
设置事务的隔离级别也可以.
参考:
数据库引擎中的隔离级别
http://msdn.microsoft.com/zh-cn/library/ms189122.aspx

Answer 10

要想避免，这样：
select @N=[num] from [T] where [id]=1
改为：
select @N=[num] from [T] with(xlock,paglock) where [id]=1

Answer 11

用with(rowlock xlock readpast)也可以,这样第二个用户读到的是null,null和任意比都是else,同样达到了效果。

Answer 12

有时候想多说点，有时候又觉得自己够不够格,只是发发唠叨.sql server的行级锁，我也很久都没有搞明白为什么行级锁不住所在行，为什么指定了行锁定，还可以放置共享锁,
而指定页级锁就能够锁得行，使之不能放置共享锁.
并且用行级级锁，非要指定readpast锁才可以从侧面解决实际一些问题，在某些时候仍然不能使用。后来我才明白，行级锁对于sql server来讲，需要付出太多的代价，如果使用行锁，在同一个page里面的不同行进行更新时，极容易交错,而产生死锁，与其这样，如果直接使用页锁更为安全，但却损失了高并发性能,所以一般较强的服务器对于秒级的百个并发，处理都较为吃力。这是现在的理解，至于以后是否还有新理解，我还不知道。
对于未来，我不知道是继续着程序员，还是该做些什么,喝多了，大家原谅