Duplicate key violation with composite primary key on high frequency insert

I have a table as follows:

CREATE TABLE [Alg].[Sequence](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [SequenceId] [int] NOT NULL,
    [CustomerId] [bigint] NOT NULL,
    [Data] [text] NULL,
 CONSTRAINT [PK_Sequence] PRIMARY KEY CLUSTERED 
(
    [SequenceId] ASC,
    [CustomerId] ASC
)

And this is a block from insert/update trigger of another table where I insert data to Sequence table:

--insert data into sequence table
SELECT @MaxSeqId =  ISNULL(MAX(SequenceId),0)
FROM Alg.[Sequence] WITH (ROWLOCK)
WHERE CustomerId = @CustomerId

INSERT INTO Alg.[Sequence]
VALUES (
@MaxSeqId + 1
,@CustomerId
,@SendingData
,GETDATE()
)

So whenever high frequency insertion processes (on the table with trigger), violation of duplicate key error occurs. I tried ROWLOCK, but didn't work. How can I prevent this from happenning?

Update

I have been asked why I am not using built-in sequence, I tried but couldn't find how to use sequence with composite primary key. I don't want the SequenceId column be identity, in fact, I would like to keep SequenceId as identity for each CustomerId. You can see my another question related with this

Solution

This is a poor design. You should really use sequences. Really.

One of the reasons this is a poor design is that it's easy to get bugs like the one you have. Here's how to make that code actually work:

begin transaction

SELECT @MaxSeqId =  ISNULL(MAX(SequenceId),0)
FROM Alg.[Sequence] WITH (ROWLOCK, UPDLOCK, SERIALIZABLE)
WHERE CustomerId = @CustomerId

INSERT INTO Alg.[Sequence](SequenceId, CustomerId, Data)
VALUES (
@MaxSeqId + 1
,@CustomerId
,@SendingData
)

commit transaction

UPDLOCK instructs SQL Server to place a restrictive lock on the rows read, and to ignore the version store. SERIALIZABLE instructs SQL to use range locking, so that even if no row is found, a U lock is taken on the key range to prevent a concurrent SELECT from discovering that there is no row until the first session performs the INSERT and commits the transaction.

A slightly better pattern is to use a Application Lock around the sequence generator, calling sp_getapplock before generating the key, and then calling sp_releaseapplock immediately afterword. Then concurrent sessions wouldn't have to wait until the first session's transaction to commit before generating the next key value.