How to move records in one transaction? Or Producer-Consumer pattern in SQL

Question

Surprisingly can't find a match for my question. I have one table that I need to use a source of data to insert to another and then delete whatever has been inserted. This has to be accomplished in blocking manner, i.e. if the same query/SP is executed at the same time, the same records must not be moved thus creating duplicates.

I have a feeling it's something relatively simple but I am not sure I totally understand how locking works in SQL. It appears super trivial in C# (just a monitor) but SQL...

As an example, you may consider producer-consumer model, i.e. some sort of a table that serves as a queue, some threads insert into it some consume. Insertion apparently is not a problem, but consumption is what I am wondering about.

UPDATE: Two good candidate solutions for me:

• use SELECT FOR UPDATE (need to figure out for how long row locks are hold)
• use a field to mark records before manipulating on them

And still need to figure out that SERIALIZABLE IL thing...

Thank you everyone who took an effort and replied - this community is so great.

Answer 1

Edit 1: added one small note regarding ANSI_WARNINGS & ARITHABORT OFF.

If you use SQL Server 2008 (I see you have questions regarding this version) you could try composable DML.

Simple solution:

INSERT  Target
SELECT  q.Id, q.Name, q.Type
FROM
(
        DELETE  Source 
        OUTPUT  deleted.Id, deleted.Name, deleted.Type
        WHERE   Type = @Type --or another search condition 
) q;

Complex scenario (including errors):

1.First test case demonstrates this "technique".

2.The second test demonstrates the behavior when an error is encountered during statement execution: the statement (INSERT + DELETE OUTPUT) is canceled but the batch is still executed till last statement.

3.For the third test you can see that an error can abort the "entire" batch and the statement (INSERT + DELETE OUTPUT) is, also, canceled.

The behavior regarding errors is controlled in this script using three settings: ANSI_WARNINGS, ARITHABORT and XACT_ABORT. When both settings (ANSI_WARNINGS and ARITHABORT) are OFF then this expression 1/0 will be evaluated to NULL => so, will be INSERT ... NULL.

SET NOCOUNT ON;

CREATE TABLE dbo.Source (Id INT PRIMARY KEY, Name VARCHAR(10) NOT NULL, Type TINYINT NOT NULL);
INSERT  dbo.Source (Id, Name, Type) VALUES (1,'A',1), (2, 'B',1), (3, 'C',2), (4, 'D',2), (5, 'E',2);

CREATE TABLE dbo.Target (Id INT PRIMARY KEY, Name VARCHAR(10) NOT NULL, Type TINYINT /*NOT*/ NULL);


    --***** Test 1 Ok *****
        DECLARE @Type INT = 1;

        SELECT  'Test 1 Ok' AS Description;
        BEGIN TRAN;
        INSERT  Target
        SELECT  q.Id, q.Name, q.Type
        FROM
        (
                DELETE  Source 
                OUTPUT  deleted.Id, deleted.Name, deleted.Type
                WHERE   Type = @Type
        ) q;

        SELECT  * FROM Target;
        SELECT  * FROM Source;
        --It will be fine to COMMIT transaction but I will cancel to run the second and third test
        ROLLBACK TRAN 
        SELECT  'End of Test 1 Ok' AS Description;
        GO
    --***** End of Test 1 *****

    --***** Test 2 Err *****
        --Start another batch
        GO 
        SET ARITHABORT ON;
        SET ANSI_WARNINGS ON;
        SET XACT_ABORT OFF;

        DECLARE @Type INT = 1;

        SELECT  'Test 2 Err' AS Description, SESSIONPROPERTY('ARITHABORT') [ARITHABORT_STATUS], SESSIONPROPERTY('ANSI_WARNINGS') [ANSI_WARNINGS_STATUS];

        INSERT  Target
        --Divide by zero => Abort statement only
        SELECT  q.Id, q.Name, CASE WHEN q.Id <> 2 THEN q.Type ELSE  1/0 END 
        FROM
        (
                DELETE  Source 
                OUTPUT  deleted.Id, deleted.Name, deleted.Type
                WHERE   Type = @Type
        ) q;

        SELECT  * FROM Target;
        SELECT  * FROM Source;
        SELECT  'End of Test 2 Err' AS Description;
    --***** End of Test 2 *****

    --***** Test 3 *****
        --Start another batch
        GO 
        SET ANSI_WARNINGS OFF;
        SET ARITHABORT ON;
        SET XACT_ABORT OFF;

        DECLARE @Type INT = 1;

        SELECT  'Test 3 Err' AS Description, SESSIONPROPERTY('ARITHABORT') [ARITHABORT_STATUS], SESSIONPROPERTY('ANSI_WARNINGS') [ANSI_WARNINGS_STATUS];

        INSERT  Target
        --Divide by zero => Abort batch
        SELECT  q.Id, q.Name, CASE WHEN q.Id <> 2 THEN q.Type ELSE  1/0 END
        FROM
        (
                DELETE  Source 
                OUTPUT  deleted.Id, deleted.Name, deleted.Type
                WHERE   Type = @Type
        ) q

        --This statement is not executed 
        SELECT  * , 1 AS Statement  FROM Target;
        --This statement is not executed 
        SELECT  * , 1 AS Statement FROM Source;
        --This statement is not executed 
        SELECT  'End of Test 3 Err' AS Description

        GO --Start another batch    
        SELECT  * , 2 AS Statement FROM Target;
        SELECT  * , 2 AS Statement FROM Source;
    --***** End of Test 3 *****

    DROP TABLE dbo.Source;
    DROP TABLE dbo.Target;

Answer 2

some suggestions...

1. make sure your tables have proper keys or unique contraints such that duplicates can not be inserted

2. I would use a stored procedure to do the bulk move (insert + delete) within a begin/commit transaction. I would also make sure the rows that are selected to move is done so with row level locking. (This could however have a performance impact if these tables have a lot of select requests).

3. alternatively, you could actually lock on the C# code that invokes this action, blocking to make sure that no to user can enter the invoke the method at the same time.

Answer 3

Use select for update to lock rows from the source table, copy those rows to the destination table and then delete those. Another thread that executes the same logic will wait at the select for update call.

Answer 4

If you use SQL Server 2008 you can use MERGE