I need to deduplicate various data when I am importing them from different sources (Json files, other databases and REST APIs), first I load them into a single table, which defines the type for them and store the data as Json, so later when I run the batch processing, I can look up the type and insert the data to the suitable tables. The number of imported rows are different (each type goes to different table/tables), but always more than 1 million (all together ~10G of data if I place them in Json format in a single table using VARCHAR(MAX)).
As I mentioned, I need to handle duplicates, so I try to define unique indexes for the target tables and enable Ignore Duplicate Keys, which 'only' raises a warning when I insert existing data. The problem is, this only works in few cases. Most of the time, I need to work with 5+ varchar(255) fields, and I cannot add them to a unique index, because of the limit (900 byte, src).
Another thing I am struggling with, is during batch inserting, I need to insert relational data , meaning one table will have foreign keys to another. So first I need to handle the dependencies, and after I got their inserted Ids, using those I can insert the data. Like a product has a manufacturer, so first I insert all the manufacturer names in the current batch, then using those Ids I can insert the products.
The need for returning Ids and doing deduplication results in a query I would like to achieve:
- Will run concurrently, by 8-16 threads
- Should return the inserted Id
- Should only insert data If it is not inserted by another thread before (or not inserted before at all)
First, I tried to handle this, by making stored procedures like this:
- Try to select the data, If found, return the Id
- If not found, start a transaction
- Check again, if it already got inserted by another thread.
- If not, insert and return the new Id.
Code example for this.:
CREATE PROCEDURE [dbo].usp_insert_pdproductdetails
@GDDataSourceVersionId INT,
@ManufacturerNameId BIGINT,
@ManufacturerReference NVARCHAR(255),
@PropertiesJson NVARCHAR(MAX),
@OriginalContentPage NVARCHAR(MAX),
@NewId BIGINT OUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @NewId = [Id] FROM PDProductDetails
WHERE GDDataSourceVersionId = @GDDataSourceVersionId AND
ManufacturerId = @ManufacturerNameId AND
ManufacturerReference = @ManufacturerReference;
IF @NewId IS NULL
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT @NewId = [Id] FROM PDProductDetails
WHERE GDDataSourceVersionId = @GDDataSourceVersionId AND
ManufacturerId = @ManufacturerNameId AND
ManufacturerReference = @ManufacturerReference;
IF @NewId IS NULL
BEGIN
INSERT INTO PDProductDetails (GDDataSourceVersionId, ManufacturerId, ManufacturerReference, PropertiesJson, OriginalContentPage)
VALUES(@GDDataSourceVersionId, @ManufacturerNameId, @ManufacturerReference, @PropertiesJson, @OriginalContentPage);
SELECT @NewId = SCOPE_IDENTITY();
END
COMMIT TRANSACTION
END
SELECT @NewId;
END
GO
Multiple threads would call this and insert the Product details. However, using this I got deadlocked really fast. I changed to a different approach, using Merge:
CREATE PROCEDURE [dbo].usp_insert_pdproductdetails
@GDDataSourceVersionId INT,
@ManufacturerNameId BIGINT,
@ManufacturerReference NVARCHAR(255),
@PropertiesJson NVARCHAR(MAX),
@OriginalContentPage NVARCHAR(MAX),
@NewId BIGINT OUT
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
MERGE
INTO [dbo].[PDProductDetails] T
USING (SELECT @GDDataSourceVersionId, @ManufacturerNameId, @ManufacturerReference, @PropertiesJson, @OriginalContentPage)
AS Source (GDDataSourceVersionId, ManufacturerNameId, ManufacturerReference, PropertiesJson, OriginalContentPage)
ON T.GDDataSourceVersionId = Source.GDDataSourceVersionId AND
T.ManufacturerId = Source.ManufacturerNameId AND
T.ManufacturerReference = Source.ManufacturerReference
WHEN NOT MATCHED THEN
INSERT (GDDataSourceVersionId, ManufacturerId, ManufacturerReference, PropertiesJson, OriginalContentPage)
VALUES(Source.GDDataSourceVersionId, Source.ManufacturerNameId,
Source.ManufacturerReference, Source.PropertiesJson, Source.OriginalContentPage);
COMMIT TRANSACTION;
SELECT @NewId = [Id] FROM PDProductDetails (NOLOCK)
WHERE GDDataSourceVersionId = @GDDataSourceVersionId AND
ManufacturerId = @ManufacturerNameId AND
ManufacturerReference = @ManufacturerReference;
SELECT @NewId;
END
GO
This always merges the row and selects later. It still deadlocks tough, not as fast as the other one, but still.
How can I achieve an insert ignore and return inserted id functionality, which won't deadlock in concurrent environment?
