Gents,
I have come into a system that uses a secondary table to generate (for
want of a better word) Identities.
eg
create table myidents
( name sysname not null, ident int not null)
create procedure getnextident @table sysname, @ident int output
as
begin
if not exists (select top 1 1 from myidents where name = @table)
insert into myidents values (@table, 0)
update myidents
set @ident = ident = ident + 1
where name = @table
end
now, (ignoring for now the use of reserved words) the problem is that
this is called frequently, from other procedures. Trouble is that the
calling procedures call it from within a transaction. We now have a
wickedly hot spot on this table, with frequent deadlocks.
Is there any relatively quick fix for this? Some locking hints or
whatever.
Or do we need to go and recode, moving this kind of thing outside the
transaction (which are all rather too long for my liking), and even
cosidering using identity columns as a replacement?
Thanks