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);
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;
I am not sure about appropriate locking. but you can find if table is lock
DECLARE @output INT
BEGIN TRY
IF EXISTS (
SELECT o.object_id
FROM sys.dm_tran_locks l
,sys.objects o
WHERE l.resource_associated_entity_id = o.object_id
AND resource_database_id = DB_ID()
AND o.NAME = 'Receipt '
)
BEGIN
BEGIN TRANSACTION
INSERT INTO Request
SELECT *
FROM Receipt WITH (HOLDLOCK)
SET @output = 1
COMMIT
END
ELSE
SET @output = 0
END TRY
BEGIN CATCH
SET @output = - 1
IF (@@trancount > 0)
ROLLBACK
END CATCH
