sql sql server 加行锁注意事项(防止锁全表)

sql  sql server 加行锁注意事项(防止锁全表)

实例:

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET LOCK_TIMEOUT 5000
--初始化分配一个@sn
declare @SN varchar(20)
select  top 1 @SN=SN 
from  Tablename WITH ( ROWLOCK, UPDLOCK )
where Data_Flag<>'acb'
order by  CIG_SN


update  SF_Cp_Detail  
set Data_Flag='acb'
where CIG_SN=@CIG_SN

 COMMIT TRAN;

1.加行锁时 必套在事物中 (BEGIN TRAN  ..... COMMIT TRAN;)

2.加行锁时不能使用微软的函数,如我改成如下:

BEGIN TRAN
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET LOCK_TIMEOUT 5000
--初始化分配一个@sn
declare @SN varchar(20)
select  @SN=min(SN) 
from  Tablename WITH ( ROWLOCK, UPDLOCK )
where Data_Flag<>'acb'


update  Tablename  
set Data_Flag='acb'
where SN=@SN

虽然并发时锁加的成功,但是因为MIn本身含有事物,导致锁全表,而不是锁一行


                              


 COMMIT TRAN;