Need a separate transaction, not a nested one [sql-server]

Question

(Code is at bottom, this is on SQL Server 2019)

I have a problem with a script I have that creates monthly statements. The script is long and each invoice can take multiple seconds to process for a single transaction. Because the server itself is underutilized, I am trying to split this one script into several that can run simultaneously, each one running a portion of the customer record and generating statements.

The problem is that the process of generating IDs hits a very small table and is creating deadlocks. The first process to grab an ID off of Ticker grabs a lock and won't release it until the statement is complete, some of which can take well over a minute to complete. This creates a ginormous lock that keeps everyone from updating that specific ID until the process is complete. This has always been a problem during EOM billing, and trying to split this into multiple processes has only made it worse.

I am trying to figure out a way to update this table as a separate transaction so the lock is released immediately upon update rather than sit and wait for the long process to complete. I have no need for these IDs to be consecutive, so I don't care if IDs are grabbed by other threads in the middle of processing. I just need to grab an ID in such a way that it does not force everything else to wait until everything is done, and instead just lock the row for the time to update that table alone, and not keep the lock until the outer transaction is complete.

Any ideas?

CREATE TABLE [dbo].[Ticker](
    [DocType] [char](4) NOT NULL,
    [NextDocID] [int] NOT NULL,
    [RowID] [int] IDENTITY(1,1) NOT NULL,
    [Prefix] [varchar](8) NULL,
    [Size] [tinyint] NULL,
    [Description] [varchar](100) NULL,
 CONSTRAINT [PK_TICKER] PRIMARY KEY NONCLUSTERED 
(
    [DocType] ASC
)
)
INSERT  [dbo].[Ticker]
           ([DocType]
           ,[NextDocID]
           ,[Prefix]
           ,[Size]
           ,[Description])
     VALUES
           ('CHRG'
           ,50
           ,'CHRG'
           ,11
           ,'Charge Records')

go
go
CREATE proc [dbo].[GetNextChargeID]
    @DocID char(15) = '' OUTPUT
as

set nocount on

/*DESCRIPTION: This proc will update the CHRG ticker and return the full
               15 character document value*/
begin
BEGIN TRANSACTION
update Ticker WITH (ROWLOCK) set NextDocID = NextDocID + 1, @DocID = NextDocID
    where DocType = 'CHRG' 

select @DocID = 'CHRG' + replicate('0', 11 - len(rtrim(@DocID))) + @DocID

COMMIT TRANSACTION
end
GO
/* run a copy of this in two windows*/
begin tran
exec GinormousTran
waitfor delay '00:00:20'
commit;

Answer 1

Your options are

1. Stop updating a table to generate keys. Instead create a separate sequence for each DocType and use that to generate the keys.

2. Use SQL CLR or Linked Server emulate an "Autonomous Transaction"