Sql-server – Identity Column in Concurrency, Multithreading, Parallel Processing

concurrencydatabase-designmulti-threadprimary-keysql server

Our company would like to understand the effects:

If we have single table with Primary Key Identity column, int primary key IDENTITY(1,1),
and the Application is conducting multi-threading parallel processing, trying to conducts 1000s of simultaneous inserts on one table.

Would the identity primary key column fail, with a primary key violation in any way, for example: processors are trying to input the same identity number?

Or was SQL designed to be successful even in multithreading parallel insert?

Note: IDENTITY_INSERT is OFF

Best Answer

Would the identity primary key column fail, with a primary key violation in any way, example: processors are trying to input the same identity number?

This is covered in the SQL Server product documentation:

CREATE TABLE (Transact-SQL) IDENTITY (Property)

Identity columns can be used for generating key values. The identity property on a column guarantees the following:

Each new value is generated based on the current seed & increment.

Each new value for a particular transaction is different from other concurrent transactions on the table.

The second point is the guarantee you need.

It is valid no matter how many user processes or threads access SQL Server concurrently.

To state it explicitly: yes, identity allocation is thread-safe.

A call to CMEDSeqGen::GenerateNewValue allocates the next value to a caller, and is protected by a latch (a lightweight synchronization object). It is not affected by user transactions, which means you can end up with gaps in case of rollbacks. Concurrent transactions can generate identity (and sequence) values without having to wait on a lock.

For more details, see my answer to a related question.

Related Solutions

Mysql – Concurrency question

I may be wrong, but I don't think this is possible unless you restrict access to the data to be via stored procedures - then the procedure can trigger audit mechanism that you can expose to the users. You would also need polling of the audit if both users need to be aware.

Sql-server – Work queue with complex select and long processing: Ensuring concurrency

You mentioned you could not use 'locking by deletion' because the row must stay in the table. The code example below does use it, but only after creating a global queue first and populating it. It then uses the 'locking by deletion' for the ##Queue table rather than the table you're working with.

USE master
GO

    -- 1. Create a Stored Procedure that creates the Queue and populates it based on the criteria you have.
    --      (this doesn't have to be a Stored Procedure, but can also be run via some other method to populate the queue.)
    --      (also update 'DatabaseName.SchemaName.the_table' to be the name of your table.)

    IF EXISTS (SELECT * FROM master.dbo.sysobjects o WHERE o.xtype IN ('P') AND o.id = object_id('master.dbo.CreateQueue'))
    DROP PROC CreateQueue
    GO

    CREATE PROCEDURE dbo.CreateQueue
    AS
    BEGIN
        IF OBJECT_ID('Tempdb.dbo.##Queue') IS NOT NULL
        DROP TABLE ##Queue

        SELECT * INTO ##Queue FROM DatabaseName.SchemaName.the_table WHERE <replace with your criteria> 
    END
    GO

    -- 2. Execute the SP above or use the code some other way to create the ##Queue

    -- 3. Copy the code below and run it via multiple processes if you like, as there should not be concurrency issues.
    WHILE 1 = 1
        BEGIN
            DELETE TOP ( 1 )
                    ##Queue WITH ( READPAST )
            OUTPUT  Deleted.*
                    INTO #RowToProcess
            IF @@ROWCOUNT > 0 
                BEGIN
                    --Place logic here to work with the row...
                    DELETE  FROM #RowToProcess
                END
            ELSE
                BREAK
        END

Best Answer

Related Solutions

Mysql – Concurrency question

Sql-server – Work queue with complex select and long processing: Ensuring concurrency

Related Question