Incremental Numbers with MAX() to avoid Concurrency Issues

• dba_user

  Default port

  Points: 1458

  More actions

  August 22, 2006 at 12:29 pm

  #173780

  We have a table where the primary key is an integer column that is calculated by doing MAX() + 1.  We can't use an Identity on this (long story).  So, what I am doing is I have a function called dbo.FN_ODOMETER

   

  CREATE FUNCTION dbo.FN_ODOMETER

  ()

  returns integer

  AS

  begin

  DECLARE @NEXT_VALUE

   

  select @NEXT_VALUE = MAX(TM_ID) + 1 FROM TEAMS WHERE TM_ID > 1000000

   

  RETURN @NEXT_VALUE

  end

  Then I have an insert USP called dbo.USP_TEAMS_INS

  CREATE PROCEDURE DBO.USP_TEAMS_INS

  (@TMNAME VARCHAR(100))

  AS

   

  INSERT INTO DBO.TEAMS (TM_ID, TMNAME)

  VALUES (DBO.FN_ODOMETER, @TMNAME)

   

  RETURN 0

   

  What can I do to eliminate concurrency issues (excluding the option of an odometer table that holds the next value)?  Thanks in advance! 

   

   

   

   

   

• AndrewMurphy

  SSCertifiable

  Points: 5586

  More actions

  August 23, 2006 at 4:45 am

  #656314

  Other than a counter table....the only thing I can think of is...

  DECLARE @NEXT_VALUE as integer

  select @NEXT_VALUE = MAX(a.TM_ID) + 1 FROM (select top 1 TM_ID FROM TEAMS order by TM_ID desc) a

  On a query estimation basis it however seems 'on my system' not to be as resource efficient.

  But "select top 1 TM_ID FROM TEAMS order by TM_ID desc" looks to be better still...but the problem is getting the value into a variable to be returned.

   

  However it does have the benefit of getting away from the 'hardcoded' minimum value of 1000000...which over time may be obselete...or not work.

• Ward Pond

  SSC Eights!

  Points: 999

  More actions

  August 25, 2006 at 2:42 pm

  #656949

  I had a similar requirement and ended up going with what you're calling an odometer table.  Even then, locking and concurrency were issues.  Here's the code I ended up with in SQL2K; perhaps this will be useful in your scenario:

  begin

  tran

  -- get the incident number

  if

  (select DateDiff(dy,DateModified,@CreatedDate)

  from dbo.NumberBroker with (tablockx, holdlock)

  where ObjectName = N'IncidentNumber') > 0

  -- start the new day

  update dbo.NumberBroker with (tablockx, holdlock)

  set @LastIntegerUsed = 1, LastIntegerUsed = 1, DateModified = @CreatedDate

  where ObjectName = N'IncidentNumber'

  else

  -- increment the existing day

  update dbo.NumberBroker with (tablockx, holdlock)

  set @LastIntegerUsed = LastIntegerUsed + 1, LastIntegerUsed = @LastIntegerUsed

  from dbo.NumberBroker

  where ObjectName = N'IncidentNumber'

  commit

  tran

  Hope this helps. 

  ---

  Take care..

  - Ward Pond
  blogs.technet.com/wardpond