BEGIN TRANSACTION
declare @maxid int,@ID varchar(50)
set @maxid=0
select @maxid=[MaxRecordNo] from [MaxRecordNoTable] where [TableName]=XX
set @maxid=@maxid+1
...
insert into XX values（@maxid）...
update [MaxRecordNoTable]  set [MaxRecordNo]=@maxid  where [TableName]=XX
COMMIT TRANSACTION

select count(RecordNo) from XX where RecordNo in (select RecordNo from XX group by RecordNo having count(RecordNo)>1)

BEGIN TRANSACTION
declare @maxid int,@ID varchar(50)
set @maxid=0
select @maxid=[MaxRecordNo] from [MaxRecordNoTable] where [TableName]=XX
update [MaxRecordNoTable]  set [MaxRecordNo]=@maxid+1  where [TableName]=XX
--紧接着就修改

set @maxid=@maxid+1
...
insert into XX values（@maxid）...
COMMIT TRANSACTION

------解决方案--------------------
使用Update 中使用with(rowlock,holdlock)控制

e.g.
begin tran
Declare @maxid int
update MaxRecordNoTable  
set  @maxid=[MaxRecordNo] ,[MaxRecordNo]=[MaxRecordNo]+1
from [MaxRecordNoTable] with(rowlock,holdlock)
where [TableName]=XX

insert into XX values（@maxid）..
......

commit tran




------解决方案--------------------
BEGIN TRANSACTION
declare @maxid int,@ID varchar(50)
set @maxid=0
select @maxid=[MaxRecordNo] from [MaxRecordNoTable] with(pagelock,Xlock) where [TableName]=XX
set @maxid=@maxid+1
...
insert into XX values（@maxid）...
update [MaxRecordNoTable]  set [MaxRecordNo]=@maxid  where [TableName]=XX
COMMIT TRANSACTION


在你读取的时候。别人也同时读取是可以的。所以应该限定在读取时锁定不让别人读取即可

------解决方案--------------------
select @maxid=[MaxRecordNo] from [MaxRecordNoTable] with(HOLDLOCK) where [TableName]=XX

------解决方案--------------------

[url=http://blog.csdn.net/zhazhuzhao/article/details/7708362[/url]
我的博客中有个例子，6楼的，也差不多，你可以看看。事务在默认情况下，select之后就把锁释放了，别人同样可以读，而不用等待，所以最好的办法是在一句话中完成更新与赋值。

------解决方案--------------------

try this,
SQL code

set transaction isolation level serializable
begin transaction
declare @maxid int,@ID varchar(50)
set @maxid=0
select @maxid=[MaxRecordNo] from [MaxRecordNoTable] where [TableName]=XX
set @maxid=@maxid+1
...
insert into XX values(@maxid)...
update [MaxRecordNoTable]  set [MaxRecordNo]=@maxid  where [TableName]=XX
commit transaction