I write a query to auto increase the sequence number of new record, Its works fine in single call.

insert into testTable (sequence_no) 
    case when exists (select sequence_no from testTable)
         then (select top(1) sequence_no  1 from testTable order by sequence_no desc)
         else '1'
    end as sequence_no

Then I add a thread remark on it, loop 100000 times, open 2 thread and run it same time.
thread 1:

declare @cnt INT =0;
while @cnt<100000
begin 
insert into testTable (sequence_no, thread_no) 
    case when exists (select sequence_no from testTable)
         then (select top(1) sequence_no  1 from testTable order by sequence_no desc)
         else '1'
    end as sequence_no, '1' as thread_no
SET @cnt = @cnt   1;
END;

thread 2:

declare @cnt INT =0;
while @cnt<100000
begin 
insert into testTable (sequence_no, thread_no) 
    case when exists (select sequence_no from testTable)
         then (select top(1) sequence_no  1 from testTable order by sequence_no desc)
         else '1'
    end as sequence_no, '2' as thread_no
SET @cnt = @cnt   1;
END;

The result around 70% request success, the others fail with

Violation of PRIMARY KEY constraint 'sequence_no'. Cannot insert duplicate key in object 'dbo.testTable'.

I thought it will be solve if I use a transaction on each request, but the result is samiliar, around 70% success ,and others fail with PK duplicate.

Is it means my practise is bad for sequnce number generation?
Can someone give me a improvement?

CodePudding user response：

Each RDBMS system usually has it own "auto-number" (usually used for PrimaryKeys) setup.

MySql, Ms-Sql-Server, ORACLE,

https://www.w3schools.com/sql/sql_autoincrement.asp

Your question is tagged with (microsoft)sql-server, so I'll paste that.

Syntax for SQL Server The following SQL statement defines the "PersonKey" column to be an auto-increment primary key field in the "Person" table:

CREATE TABLE dbo.Person (
    PersonKey int IDENTITY(1,1) PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255)
);

PostGres:

https://chartio.com/resources/tutorials/how-to-define-an-auto-increment-primary-key-in-postgresql/

Do not reinvent the wheel.

So unless you are (trying) to INSERT a million rows in < 2 seconds....use what is already there for you.

Opinions.

You should not care that each primary-key is perfectly in sequence. Aka, "gaps" should be Ok.

if you think you need perfect sequencing, you need to ask yourself "why".

a primary key needs to be unique. having "order" helps with indexing.

but

1,2,3,6,7,9,11 are ordered. (4,5,8 are missing, but does it really matter that they are missing?)

CodePudding user response：

The first example in the question (sequence_no 1) is how NOT to do it! Instead, SQL Server has two ways you can do this, and either is acceptable:

1. Define the column as an identity column, and omit it from the INSERT completely:

   insert into testTable (thread_no) VALUES ('1')
   

2. Create a Sequence, and use the value from the sequence:

    insert into testTAble(sequence_no, thread_no) VALUES (NEXT VALUE FOR testTableSequence, '1')
   

Again, under no circumstances should you ever try to use TOP 1 sequence_no 1 or MAX(sequence_no) 1. That is wrong in SQL Server (really, it's wrong in MySql, too).

CodePudding user response：

Here is how I did it -- I had the last sequence number stored in a table called MetaInfo. This allowed people to pass a next sequence number to the SP and use that if it was greater than the max -- but give the max if not.

Full discussion is on db site here

https://dba.stackexchange.com/questions/3307/emulate-a-tsql-sequence-via-a-stored-procedure

You can see the locks needed and that it had to be in a transaction

CREATE PROCEDURE [dbo].[uspGetNextID]
(
  @inID bigInt 
)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRANSACTION

    UPDATE MetaInfo WITH (ROWLOCK) 
      SET MetaValueLong = CASE 
                            WHEN ISNULL(MetaValueLong,0) > @inID THEN MetaValueLong 1 
                            ELSE @inID 1
                          END 
    WHERE MetaKey = 'Internal-ID-Last'

    SELECT MetaValueLong 
    FROM MetaInfo
    WHERE MetaKey = 'Internal-ID-Last'

    COMMIT TRANSACTION 

END