Global counter from database deadlock

Hi All,

I’ve got a receive port that takes a single CSV file
and uses a disassembler pipeline that generates about 18000 XML messages from
that file. There is a send port with a subscription to this receive port and it
has a custom pipeline component. One of the tasks for this custom pipeline
component is to set a global incremental counter to each of the messages. This
is done by calling the following stored procedure (where COUNTERID is a
parameter, but I simplified it a bit).

 CREATE procedure [dbo].[sprGetNewCounterId] as  
  begin  
    begin tran work  
    declare @Result as bigint  
    update tblCounters set CounterValue = isnull(CounterValue,0) +1 where upper(CounterKey) = ‘COUNTERID’   
    set @Result =  (select CounterValue from tblCounters where upper(CounterKey) = ‘COUNTERID’)   
    commit tran work   
    select @Result as NewIdVal
  end

Now, for a small file this works just fine. But with a
full file the receive port wil generate 18000 messages and they all simultaneously
request a new ID from the database, throwing deadlocks. I could rewrite the solution to use an orchestration and sequentially loop to all of the messages, but I don’t like the orchestration overhead. Would there be any other way to retreive an incremental counter? The counter has to be an integer value (we can’t use a GUID) and it has to be unique, so if a new file arrives it has to continue counting where the last one ended. So we have to store the value somewhere and be sure that every number only gets assigned once.

Is there a way to throttle the send port so it is not activating thousands of messages at once? Or possibly a better solution for this?