高并发下的获取ID解决方案

 

/****** 第一种解决方案：锁+事务 保证原子性 ******/

begin transaction;
declare @sn bigint;
select top 0 id from IDS with (tablockx);
set @sn=isnull(IDENT_CURRENT('IDS'),IDENT_INCR('IDS')) + {0};
DBCC CHECKIDENT ('IDS', RESEED, @sn);
commit transaction;
select @sn

/****** 第二种解决方案：未测试 可能缺乏原子性 ******/

set identity_insert IDS on;
insert into UID (id,trTime) values (ident_current('IDS')+{0},getdate());
select SCOPE_IDENTITY();
set identity_insert IDS off

 /****** 第三种解决方案：锁+事务 保证原子性 ******/

declare @i int;
set @i=0;
while @i<200000
begin
    begin transaction;
    declare @sn bigint,@temp int;
    set @temp=(select top 0 id from IDS with (tablockx));
    set identity_insert IDS  on;
    insert into IDS (id,trTime) values (ident_current('IDS')+10,getdate());
    insert into SN(uid,OperationTime,ClientIp)values(SCOPE_IDENTITY(),GETDATE(),'127.0.0.1');
    set identity_insert IDS off;
    set @i=@i+1;  
    commit transaction;    
end