Select and Delete in the same transaction using TOP clause

Question

I have table in which the data is been continuously added at a rapid pace.

And i need to fetch record from this table and immediately remove them so i cannot process the same record second time. And since the data is been added at a faster rate, i need to use the TOP clause so only small number of records go to business logic for processing at the time.

I am using the below query to

BEGIN TRAN readrowdata
SELECT    
 top 5 [RawDataId],    
 [RawData]    
FROM    
 [TABLE]  with(HOLDLOCK)  

 WITH  q AS
    (
        SELECT    
        top 5 [RawDataId],    
        [RawData]    
        FROM    
        [TABLE]  with(HOLDLOCK)  
    )

DELETE from q  
COMMIT TRANSACTION readrowdata

I am using the HOLDLOCK here, so new data cannot insert into the table while i am performing the SELECT and DELETE operation. I used it because Suppose if there are only 3 records in the table now, so the SELECT statement will get 3 records and in the same time new record gets inserted and the DELETE statement will delete 4 records. So i will loose 1 data here.

Is the query is ok in performance term? If i can improve it then please provide me your suggestion.

Thank you

I would urge you to read [SQL Server, using a table as a queue](http://stackoverflow.com/questions/4686726/sql-server-using-a-table-as-a-queue) and the links from there — AakashM, Feb 23 '12 at 09:34

score 3 · Answer 1 · answered Feb 23 '12 at 09:32

3

Personally, I'd use a different approach. One with less locking, but also extra information signifying that certain records are currently being processed...

DECLARE @rowsBeingProcessed TABLE (
    id INT
);

WITH rows AS (
  SELECT top 5 [RawDataId] FROM yourTable WHERE processing_start IS NULL
)
UPDATE rows SET processing_start = getDate() WHERE processing_start IS NULL
OUTPUT INSERTED.RowDataID INTO @rowsBeingProcessed;

-- Business Logic Here

DELETE yourTable WHERE RowDataID IN (SELECT id FROM @rowsBeingProcessed);

Then you can also add checks like "if a record has been 'beingProcessed' for more than 10 minutes, assume that the business logic failed", etc, etc.

answered Feb 23 '12 at 09:32

MatBailie

83,401
18
103
137

@PankajGarg - I'm not sure what you mean exactly. The UPDATE/OUTPUT is within an implicit transaction. This means that you update a record AND log it to a table variable and ***no*** other process can intervene there. – MatBailie Feb 23 '12 at 17:11
Just imagine that, suppose user1 insert 10 records and user2 inserts 11 records and so on ...What will happen in that case.... – Pankaj Feb 23 '12 at 17:18

score 1 · Answer 2 · answered Feb 23 '12 at 09:30

By locking the table in this way, you force other processes to wait for your transaction to complete. This can have very rapid consequences on scalability and performance - and it tends to be hard to predict, because there's often a chain of components all relying on your database.

If you have multiple clients each running this query, and multiple clients adding new rows to the table, the overall system performance is likely to deteriorate at some times, as each "read" client is waiting for a lock, the number of "write" clients waiting to insert data grows, and they in turn may tie up other components (whatever is generating the data you want to insert).

Diego's answer is on the money - put the data into a variable, and delete matching rows. Don't use locks in SQL Server if you can possibly avoid it!

score 0 · Answer 3 · answered Jul 29 '14 at 07:18

I know that it's old question, but I found some solution here https://www.simple-talk.com/sql/learn-sql-server/the-delete-statement-in-sql-server/:

DECLARE @Output table
(
  StaffID INT,
  FirstName NVARCHAR(50),
  LastName NVARCHAR(50),
  CountryRegion NVARCHAR(50)
);
DELETE SalesStaff
OUTPUT DELETED.* INTO @Output
FROM Sales.vSalesPerson sp
  INNER JOIN dbo.SalesStaff ss
  ON sp.BusinessEntityID = ss.StaffID
WHERE sp.SalesLastYear = 0;
SELECT * FROM @output;

Maybe it will be helpfull for you.

score 0 · Answer 4 · edited Feb 23 '12 at 21:47

0

If I understand you correctly, you are worried that between your select and your delete, more records would be inserted and the first TOP 5 would be different then the second TOP 5?

If that so, why don't you load your first select into a temp table or variable (or at least the PKs) do whatever you have to do with your data and then do your delete based on this table?

edited Feb 23 '12 at 21:47

Pankaj

9,749
32
139
283

answered Feb 23 '12 at 09:26

Diego

34,802
21
91
134

How will you know about if in case of multiple users doing inserts simultaneously? – Pankaj Feb 23 '12 at 16:52
what's the problem? Once you have your 5 IDs more rows can be inserted because you wont select them again – Diego Feb 23 '12 at 16:59
Thanks for the discussion. I wanted to discuss about the missing records - which got skipped during the simultaneous inserts by multiple users... – Pankaj Feb 23 '12 at 17:01

Pankaj · Answer 5 · 2012-02-23T18:54:34.093

You can do it very easily with TRIGGERS. Below mentioned is a kind of situation which will help you need not to hold other users which are trying to insert data simultaneously. Like below...

Data Definition language

CREATE TABLE SampleTable
(
    id int
)

Sample Record

insert into SampleTable(id)Values(1)

Sample Trigger

CREATE TRIGGER SampleTableTrigger
on SampleTable AFTER INSERT
AS
IF Exists(SELECT id FROM INSERTED)
BEGIN
    Set NOCOUNT ON
    SET XACT_ABORT ON
    Begin Try
        Begin Tran
                 Select ID From Inserted
                 DELETE From yourTable WHERE ID IN (SELECT id FROM Inserted);

        Commit Tran
    End Try
    Begin Catch
        Rollback Tran
    End Catch


End

Hope this is very simple and helpful

Select and Delete in the same transaction using TOP clause

5 Answers5