sql更新update多条select信息时如何不脏读排他

Question

项目用的vs2003和sql2000 需要提取  num_product 不同条件的 数据信息同时在表中加上标示， 为了达到显示的时候，别的用户无法就把select的表数据给更新了一下标示，好方便提取 现在就是如何在更新读取避免重新更新和脏读 比如一次需要提取下面的几条语句的数据一起显示到前台 select top 10 Pro_id  from num_product where Adr_Code='jn'  AND  Pro_State=0  AND Sort_Type=1 AND TelType=0 AND pro_price='菏泽_普通号码' ORDER BY NEWID() select top 2 Pro_id from num_product where Adr_Code='jn'  AND  Pro_State=0  AND Sort_Type=1 AND TelType=0 AND pro_price in(SELECT Step_Name FROM Num_Step WHERE Num_Step.ComType=6) ORDER BY NEWID() 如何做到 同时很多人读取，不产生冲突                 sql = "UPDATE Num_Product with(rowlock) SET Pro_State = 3,User_AdrCode = 'jn.01',Pro_UseCount = Pro_UseCount + 1,Agent_Code ='jn.01.01'  WHERE Pro_id IN(select top  10  Pro_id from num_product where Adr_Code='jn'  AND  Pro_State=0  AND Sort_Type=1 AND TelType=0 AND pro_price='菏泽_普通号码' ORDER BY NEWID())" 原来我是用这样更新的 哪位大哥 看看我这样写行吗 CREATE PROCEDURE [usp_AgentHand] @Adr_Code varchar(50),  @User_AdrCode varchar(50), @Pro_Ord varchar(50), @TelType varchar(10), @Agent_Code varchar(100), @ComEnd6 varchar(10), @ComHand4 varchar(10), @ComEnd4 varchar(10), @ComOther varchar(10)ASSET TRANSACTION ISOLATION LEVEL REPEATABLE READdeclare   @sql   varchar(4000)begin transaction  SET @sql='UPDATE Num_Product SET Pro_State=3,User_AdrCode='''+@User_AdrCode+''',Pro_UseCount=Pro_UseCount+1,Agent_Code ='''+@Agent_Code+''',Pro_Ord = '''+@Pro_Ord +''' WHERE Pro_id IN(select top '+ @ComOther+' Pro_id from num_product where Adr_Code='''+@Adr_Code+'''AND  Pro_State=0 AND Sort_Type=1 AND TelType='+@TelType+' AND pro_price=''菏泽_普通号码'' ORDER BY NEWID());' SET @sql=@sql+' UPDATE Num_Product SET Pro_State=3,User_AdrCode ='''+@User_AdrCode +''',Pro_UseCount = Pro_UseCount+1,Agent_Code ='''+@Agent_Code+''',Pro_Ord = '''+@Pro_Ord +''' WHERE Pro_id IN(select top '+@ComEnd6+' Pro_id from num_product where Adr_Code='''+@Adr_Code+''' AND Pro_State=0 AND Sort_Type=1 AND TelType='+@TelType+' AND pro_price IN(SELECT Step_Name FROM Num_Step WHERE ComType=6) ORDER BY NEWID());' SET @sql=@sql+' UPDATE Num_Product SET Pro_State=3,User_AdrCode='''+@User_AdrCode+''',Pro_UseCount=Pro_UseCount+1,Agent_Code ='''+@Agent_Code+''',Pro_Ord = '''+@Pro_Ord +''' WHERE Pro_id IN(select top '+ @ComEnd4+' Pro_id from num_product where Adr_Code='''+@Adr_Code+'''AND  Pro_State=0 AND Sort_Type=1 AND TelType='+@TelType+' AND pro_price IN(SELECT Step_Name FROM Num_Step WHERE Num_Step.ComType=4) ORDER BY NEWID());' SET @sql=@sql+' UPDATE Num_Product SET Pro_State=3,User_AdrCode='''+@User_AdrCode+''',Pro_UseCount=Pro_UseCount+1,Agent_Code ='''+@Agent_Code+''',Pro_Ord = '''+@Pro_Ord +''' WHERE Pro_id IN(select top '+ @ComHand4+' Pro_id from num_product where Adr_Code='''+@Adr_Code+'''AND  Pro_State=0 AND Sort_Type=1 AND TelType='+@TelType+' AND pro_price=''菏泽_普通号码'' AND left(Pro_Telenumber,7) like ''%4%'' ORDER BY NEWID());' exec(@sql) SELECT * FROM num_product WHERE Pro_State=3 AND User_AdrCode=@User_AdrCode AND Agent_Code =@Agent_Code AND TelType=@TelType AND Pro_Ord =@Pro_Ord--print @sqlcommit TRANSACTIONGO    

Answer 1

在你的update前后加上事务锁定

Answer 2

可以

select top 10 * from num_product where   Pro_State=0 for update

select top 6 * from num_product where   Pro_State=1 for update

你就可以在這個事務中做update操作，但是對其他的用戶

     1.允许普通select查询，不允许做for update查询。

     2. 不允许对表中任何记录做update操作;

     3. 允许insert操作;

     4. 不允许delete操作。

Answer 3

能帮忙详细指点一下吗 谢谢