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

[zl_c]

设：表[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写成一句

[zlp321002]

--是这个意思吗?没发现问题啊...
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

[hery2002]

使用排它锁行不行,

[zl_c]

按楼上并不是真正的同一时间执行啊.

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

[zl_c]

[Quote=引用 2 楼 hery2002 的回复:]
使用排它锁行不行,
[/Quote]
请教代码．．

[zlp321002]

--转一个贴
设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

同时执行，系统会检测出死锁，并中止进程

[zlp321002]

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   --可立即执行   

[zlp321002]

--试试这样,看会不会发生你说的情况....
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

[nzperfect]

或许你遇到了这类问题
但你举得例子不恰当。

不可能有这种情况。

[zl_c]

[Quote=引用 8 楼 perfectaction 的回复:]
或许你遇到了这类问题
但你举得例子不恰当。

不可能有这种情况。
[/Quote]

确实出现这种情况,按你的理解,会是因为什么问题?

[guojingDua]

加行级排他锁

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

[guojingDua]

多打了个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

[nzperfect]

靠，我知道了。这里面有个时间差。

[nzperfect]

是我疏忽了，收回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

[hery2002]

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

[nzperfect]

要想避免，这样：

select @N=[num] from [T] where [id]=1

改为：

select @N=[num] from [T] with(xlock,paglock) where [id]=1

[nzperfect]

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

[-狙击手-]

直接像完美行动那样用锁

[nzperfect]

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

很多东西贴来贴去的，又有几个人真正搞懂了索引、事务、锁呢？
到头来还是解决不了很多实际中问题中高并发的情况。

[-狙击手-]

....

[nzperfect]

有时候想多说点，有时候又觉得自己够不够格,只是发发唠叨.

sql server的行级锁，我也很久都没有搞明白为什么行级锁不住所在行，为什么指定了行锁定，还可以放置共享锁,
而指定页级锁就能够锁得行，使之不能放置共享锁.
并且用行级级锁，非要指定readpast锁才可以从侧面解决实际一些问题，在某些时候仍然不能使用。

后来我才明白，行级锁对于sql server来讲，需要付出太多的代价，如果使用行锁，在同一个page里面的不同行进行更新时，极容易交错,而产生死锁，与其这样，如果直接使用页锁更为安全，但却损失了高并发性能,所以一般较强的服务器对于秒级的百个并发，处理都较为吃力。

这是现在的理解，至于以后是否还有新理解，我还不知道。
对于未来，我不知道是继续着程序员，还是该做些什么,喝多了，大家原谅