Managing Transactions with Locks on Specific Tables in SQL Server

lockingsql serverstored-procedurestransaction

NOTE : If there is similar question, please refer me to it.

I have two tables Request & Receipt.

I want table Receipt remain locked for CRUD operations from anywhere until transaction finished. I need the transaction be in a Stored Procedure.

Is it impossible? How?

Why should Receipt remain locked?
Cause an internal request for insert or delete Receipt will be unable to manipulate it! Note that I need ReceiptId's be consecutive per each Request from its start, to its finish!

details

Best Answer

Use a key table to allocate receipt numbers in a way that is concurrency friendly, and still guarantees receipt numbers will never be re-used, and will (almost) never contain gaps.

I've created a minimally complete verifiable example that you could use as a basis for learning about concurrency that should set you on a good path.

Do this work in tempdb so we don't kill anything important in your actual work:

USE tempdb;

If the objects we're creating already exist, then we'll delete them first. This allows us to easily modify the code below and re-run it multiple times for testing.

IF OBJECT_ID(N'dbo.Requests', N'U') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.CreateRequests;
    DROP TABLE dbo.Keys;
    DROP TABLE dbo.Receipts;
    DROP TABLE dbo.Requests;
END
GO

Requests table - add columns as required:

CREATE TABLE dbo.Requests
(
    RequestID int NOT NULL IDENTITY(1,1)
        CONSTRAINT Requests_PK
        PRIMARY KEY CLUSTERED
    , RequestDate datetime NOT NULL
    , Notes nvarchar(100) NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

Receipts table. ReceiptID is generated by the system, ReceiptNum is the number we'll show to users.

CREATE TABLE dbo.Receipts
(
    ReceiptID int NOT NULL IDENTITY(1,1)
        CONSTRAINT Receipt_PK
        PRIMARY KEY CLUSTERED
    , RequestID int NOT NULL
        CONSTRAINT Receipt_Request_FK
        FOREIGN KEY
        REFERENCES dbo.Requests(RequestID)
    , ReceiptNum int NOT NULL
    , ReceiptCreatedOn datetime NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

The Key table, where we store the most recently allocated ReceiptNum value:

CREATE TABLE dbo.Keys
(
    KeyName varchar(100) NOT NULL
        CONSTRAINT Keys_PK
        PRIMARY KEY CLUSTERED
    , LastUsedKeyValue int NOT NULL
) ON [PRIMARY];
GO

The CreateRequests stored procedure:

CREATE PROCEDURE dbo.CreateRequests
(
    @NumReceipts int = 1 /* default to 1 receipt */
    , @RequestNote nvarchar(100) = N''
    , @RequestID int OUTPUT /* this will contain the newly created RequestID */
)
AS
BEGIN
    SET NOCOUNT ON; /* prevent stored procedure from displaying "x records affected" */
    DECLARE @msg nvarchar(1000) = N''; /* used to display error messages */
    DECLARE @ret int = 0; /* stored procedure return value */
    DECLARE @LastUsedKey int;
    DECLARE @keys TABLE
    (
        KeyValue int NOT NULL
    );
    DECLARE @req TABLE 
    (
        RequestID int NOT NULL PRIMARY KEY
    );

    IF @NumReceipts > 0 AND @NumReceipts <= 10000 /* 10,000 is the arbitrarily chosen 
        maximum number of receipts this stored proc supports */
    BEGIN
        /* Atomically allocate @NumReceipts keys to use for Receipt Numbers */
        UPDATE dbo.Keys 
        SET LastUsedKeyValue = LastUsedKeyValue + @NumReceipts
        OUTPUT inserted.LastUsedKeyValue INTO @keys (KeyValue)
        WHERE KeyName = 'ReceiptNum';
        SELECT @LastUsedKey = k.KeyValue 
        FROM @keys k;
        IF @LastUsedKey IS NULL /* the first time we run this code, we insert a new Key */
        BEGIN
            INSERT INTO dbo.Keys (KeyName, LastUsedKeyValue)
            VALUES ('ReceiptNum', @NumReceipts)
            SET @LastUsedKey = @NumReceipts;
        END

        /* Create a new Request row, outputting the inserted RequestID 
            into a temporary table for use in the Receipts table */
        INSERT INTO dbo.Requests (RequestDate, Notes)
        OUTPUT inserted.RequestID INTO @req(RequestID)
        SELECT GETDATE(), @RequestNote;

        /* Create the new Receipts rows */
        ;WITH src AS ( /* this is configured to support a maximum of 10,000 new receipts */
            SELECT TOP(@NumReceipts) 
                ReceiptNum = (v5.Num + (v4.Num * 10) + (v3.Num * 100) + (v2.Num * 1000) 
                     + (v1.Num * 10000)) + 1 + (@LastUsedKey - @NumReceipts)
            FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(Num)
               CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(Num)
               CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v3(Num)
               CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v4(Num)
               CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v5(Num)
            ORDER BY v1.Num
                , v2.Num
                , v3.Num
                , v4.Num
                , v5.Num
        )
        INSERT INTO dbo.Receipts (RequestID, ReceiptNum, ReceiptCreatedOn)
        SELECT r.RequestID, ReceiptNum, GETDATE()
        FROM @req r
            CROSS JOIN src
        ORDER BY src.ReceiptNum;

        /* we'll return the newly created RequestID in @RequestID */
        SELECT @RequestID = r.RequestID
        FROM @req r;

        /* -1 indicates success */
        SET @ret = -1; 
    END
    ELSE
    BEGIN
        SET @msg = N'Invalid number of receipts requested.';
        RAISERROR (@msg, 14, 1);
        SET @ret = 1;
    END
    RETURN @ret;
END
GO

Here, we add three different requests, with a varying number of receipts to show the functionality of the stored procedure:

DECLARE @ReqID int;
DECLARE @ReturnValue int;
EXEC @ReturnValue = dbo.CreateRequests @NumReceipts = 19
    , @RequestNote = N'Test Request #1'
    , @RequestID = @ReqID OUT
SELECT ReturnValue = @ReturnValue, ReqID = @ReqID;
SELECT *
FROM dbo.Receipts rec
WHERE rec.RequestID = @ReqID;

The output:

╔═════════════╦═══════╗
║ ReturnValue ║ ReqID ║
╠═════════════╬═══════╣
║          -1 ║     1 ║
╚═════════════╩═══════╝

╔═══════════╦═══════════╦════════════╦═════════════════════════╗
║ ReceiptID ║ RequestID ║ ReceiptNum ║    ReceiptCreatedOn     ║
╠═══════════╬═══════════╬════════════╬═════════════════════════╣
║         1 ║         1 ║          1 ║ 2017-06-01 09:04:37.107 ║
║         2 ║         1 ║          2 ║ 2017-06-01 09:04:37.107 ║
║         3 ║         1 ║          3 ║ 2017-06-01 09:04:37.107 ║
║         4 ║         1 ║          4 ║ 2017-06-01 09:04:37.107 ║
║         5 ║         1 ║          5 ║ 2017-06-01 09:04:37.107 ║
║         6 ║         1 ║          6 ║ 2017-06-01 09:04:37.107 ║
║         7 ║         1 ║          7 ║ 2017-06-01 09:04:37.107 ║
║         8 ║         1 ║          8 ║ 2017-06-01 09:04:37.107 ║
║         9 ║         1 ║          9 ║ 2017-06-01 09:04:37.107 ║
║        10 ║         1 ║         10 ║ 2017-06-01 09:04:37.107 ║
║        11 ║         1 ║         11 ║ 2017-06-01 09:04:37.107 ║
║        12 ║         1 ║         12 ║ 2017-06-01 09:04:37.107 ║
║        13 ║         1 ║         13 ║ 2017-06-01 09:04:37.107 ║
║        14 ║         1 ║         14 ║ 2017-06-01 09:04:37.107 ║
║        15 ║         1 ║         15 ║ 2017-06-01 09:04:37.107 ║
║        16 ║         1 ║         16 ║ 2017-06-01 09:04:37.107 ║
║        17 ║         1 ║         17 ║ 2017-06-01 09:04:37.107 ║
║        18 ║         1 ║         18 ║ 2017-06-01 09:04:37.107 ║
║        19 ║         1 ║         19 ║ 2017-06-01 09:04:37.107 ║
╚═══════════╩═══════════╩════════════╩═════════════════════════╝

Run #2:

EXEC @ReturnValue = dbo.CreateRequests @NumReceipts = 3
    , @RequestNote = N'Test Request #2'
    , @RequestID = @ReqID OUT
SELECT ReturnValue = @ReturnValue, ReqID = @ReqID;
SELECT *
FROM dbo.Receipts rec
WHERE rec.RequestID = @ReqID;

The output:

╔═════════════╦═══════╗
║ ReturnValue ║ ReqID ║
╠═════════════╬═══════╣
║          -1 ║     2 ║
╚═════════════╩═══════╝

╔═══════════╦═══════════╦════════════╦═════════════════════════╗
║ ReceiptID ║ RequestID ║ ReceiptNum ║    ReceiptCreatedOn     ║
╠═══════════╬═══════════╬════════════╬═════════════════════════╣
║        20 ║         2 ║         20 ║ 2017-06-01 09:04:37.160 ║
║        21 ║         2 ║         21 ║ 2017-06-01 09:04:37.160 ║
║        22 ║         2 ║         22 ║ 2017-06-01 09:04:37.160 ║
╚═══════════╩═══════════╩════════════╩═════════════════════════╝

Run #3:

EXEC @ReturnValue = dbo.CreateRequests @NumReceipts = 9999
    , @RequestNote = N'Test Request #3'
    , @RequestID = @ReqID OUT
SELECT ReturnValue = @ReturnValue, ReqID = @ReqID;
SELECT *
FROM dbo.Receipts rec
WHERE rec.RequestID = @ReqID;

The output:

╔═════════════╦═══════╗
║ ReturnValue ║ ReqID ║
╠═════════════╬═══════╣
║          -1 ║     3 ║
╚═════════════╩═══════╝

╔═══════════╦═══════════╦════════════╦═════════════════════════╗
║ ReceiptID ║ RequestID ║ ReceiptNum ║    ReceiptCreatedOn     ║
╠═══════════╬═══════════╬════════════╬═════════════════════════╣
║        23 ║         3 ║         23 ║ 2017-06-01 09:04:37.210 ║
║        24 ║         3 ║         24 ║ 2017-06-01 09:04:37.210 ║
║        25 ║         3 ║         25 ║ 2017-06-01 09:04:37.210 ║
║        26 ║         3 ║         26 ║ 2017-06-01 09:04:37.210 ║
.....  
║     10021 ║         3 ║      10021 ║ 2017-06-01 09:04:37.210 ║
╚═══════════╩═══════════╩════════════╩═════════════════════════╝

To verify concurrency, and that the allocation of Receipt Numbers is reliable, I ran the following code simultaneously in three separate SSMS windows, creating nearly 30,000 receipts:

DECLARE @note nvarchar(100);
DECLARE @ReqID int;
DECLARE @ReturnValue int;
DECLARE @loop int = 1;
DECLARE @maxloops int = 1000;
WHILE @loop < @maxloops
BEGIN
    SET @note = N'Concurrency request loop ' + CONVERT(nvarchar(10), @loop);
    EXEC @ReturnValue = dbo.CreateRequests @NumReceipts = 5
        , @RequestNote = @note
        , @RequestID = @ReqID OUT;
    SET @loop += 1;
END;
Related Question