SQL Server Locking Rows/Table-CodePudding

I have a stored procedure that must do the following:

Select unflagged rows from table
Flag selected rows
Dispatch 500 rows to client

I have four clients, each running every minute, and call this stored procedure.

Each client must receive only their own 500 rows. Two clients should never get the same row.

Sometimes it happens that two clients take the same lines, I want to resolve this scenario.

(My idea is that rewrite code to only one update statement with output)

Which is the best approach for this scenario? Do I need configure isolation level?

This is current code example:

SET XACT_ABORT ON

BEGIN TRANSACTION

    DECLARE @IDs TABLE (ID INT) -- declaring table variable
    
    INSERT INTO @IDs    -- insert unflagged IDs (data) into table variable with update lock
        SELECT TOP 500 p.[ID]
        FROM [Core].[Poruka] p WITH (UPDLOCK)
        JOIN Core.vwKorisnickiRacunOmogucenoSlanje vk
             ON p.KorisnickiRacunSifra = vk.KorisnickiRacunSifra
        WHERE 
            (p.Zauzeto = 0 OR p.Zauzeto IS NULL)
            AND vk.OmogucenoSlanjePoruka = 1
        ORDER BY 
            vk.Prioritet

    UPDATE p -- update table to flagged
    SET Zauzeto = 1
    FROM Core.Poruka p
    JOIN @IDs tmp ON tmp.ID = p.ID

    SELECT *      -- select data to client
    FROM [Core].[Poruka] p
    WHERE p.ID IN (SELECT ID 
                   FROM @IDs)

    COMMIT

CodePudding user response：

You don't need a transaction here.

Simply use OUTPUT to output the changed rows to the client

UPDATE p
SET Zauzeto = 1   -- update table to flagged
OUTPUT inserted.* -- output to client
FROM (
    SELECT TOP (500)
        p.*
    FROM [Core].[Poruka] p
        JOIN Core.vwKorisnickiRacunOmogucenoSlanje vk
        ON p.KorisnickiRacunSifra = vk.KorisnickiRacunSifra
    WHERE 
        (p.Zauzeto = 0 OR p.Zauzeto IS NULL)
        AND vk.OmogucenoSlanjePoruka = 1
    ORDER BY 
        vk.Prioritet
) p;

You should really have this join as an EXISTS, to avoid duplicate rows coming back from the join.

UPDATE p
SET Zauzeto = 1   -- update table to flagged
OUTPUT inserted.* -- output to client
FROM (
    SELECT TOP (500)
        p.*
    FROM [Core].[Poruka] p
    WHERE 
        (p.Zauzeto = 0 OR p.Zauzeto IS NULL)
        AND EXISTS (SELECT 1
            FROM Core.vwKorisnickiRacunOmogucenoSlanje vk
            WHERE
                p.KorisnickiRacunSifra = vk.KorisnickiRacunSifra  --join condition
                AND vk.OmogucenoSlanjePoruka = 1
        )
    ORDER BY 
        vk.Prioritet
) p;

The SERIALIZABLE (aka HOLDLOCK) isolation level is wise here.