Transaction with lock on a specific table

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!

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)

StackExchange Database Administrators Q#175137, answer score: 9