CODEWITHSUNDEEP
sqlsql-serverconcurrency
wwelles
wwelles

Reputation: 49

Row concurrency problems multi threading

I am having troubles with concurrency. A 3rd party software is executing my Stored Procedure which I need to capture a unique list of IDs in a table. The code works until multi threading is brought into the mix (gasp).

I have tried various transaction features including isolation levels to seemingly no avail.

Essentially given the following, I need table 'IDList' to contain only the unique IDs that have ever been sent.

When other threads from the 3rd party software execute the example calling code, I consistently end up with duplicates in 'IDList'. It is my estimation that the following is happening, but am unable to resolve:

Result: Duplicates

I realize the example may seem silly, I have boiled it down as not to reveal confidential code.

Calling code:

DECLARE @ids IdType
INSERT INTO @ids
SELECT '123'

EXEC insertMissingIDs @ids

User defined Type:

CREATE TYPE [dbo].[IdType] AS TABLE(
    [ID] [nvarchar](250) NULL
)

Procedure:

ALTER PROCEDURE [dbo].[insertMissingIDs] 
    @ids IdType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO IDList (ID)
    SELECT p.ID
    FROM @ids i
    LEFT JOIN IDList ON i.ID = IDList.ID
    WHERE IDList.ID IS NULL
END

Thanks in advance!

Upvotes: 2

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270843

I think you basically have two choices. You can set the appropriate transaction isolation level (documentation here). I think using set transaction isolation level serializable would do the trick. This could introduce a big overhead on your transactions. You would be locking the table for both reads and writes. One call will have to wait for the previous one to finish. In more complicated situations, you might end up with a deadlock.

Another option is to define the primary key table IDLIST using the IGNORE_DUP_KEY option. This allows inserts into the table. If duplicates are in the data being inserted, they are ignored.

Here is a blog post about creative ways to use this option.

Upvotes: 1

RBarryYoung
RBarryYoung

Reputation: 56755

First, you should probably put a unique key on IDList(ID) in any event. That will guarantee that there are no duplicates, though in the case of concurrent processes above, one of the process would get an error instead.

If you want to insure that both processes can execute concurrently without error, then change the stored procedure's isolation to serializable and add transaction-handling.

Something like this should work:

ALTER PROCEDURE [dbo].[insertMissingIDs] 
    @ids IdType READONLY
AS
BEGIN
    SET NOCOUNT ON;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
    Begin Transaction

        INSERT INTO IDList (ID)
        SELECT p.ID
        FROM @ids i
        LEFT JOIN IDList ON i.ID = IDList.ID
        WHERE IDList.ID IS NULL
    Commit Transaction
END

Of course you might get some blocking also.

Upvotes: 0

Related Questions