主页 / dba / 问题 / 175137
Accepted
ABS
Asked: 2017-06-02 03:23:36 +0800 CST

锁定特定表的事务

  • 772

注意：如果有类似的问题，请参考我。

我有两张桌子Request& Receipt。

我希望表Receipt在任何地方保持锁定以进行 CRUD 操作，直到事务完成。我需要事务在存储过程中。

不可能吗？如何？

为什么要Receipt保持锁定状态？ 导致内部插入或删除请求Receipt将无法操作！请注意，我需要从 its到 its 的每一个ReceiptId都是连续的！Requeststartfinish

细节

sql-server stored-procedures

2 个回答

  1. Best Answer

    使用密钥表以并发友好的方式分配收据编号，并且仍然保证收据编号永远不会被重复使用，并且（几乎）永远不会包含间隙。

    我已经创建了一个最低限度的完整可验证示例，您可以将其用作学习并发的基础，这应该会让您走上一条好的道路。

    在 tempdb 中完成这项工作，这样我们就不会在您的实际工作中杀死任何重要的东西：

    USE tempdb;

    如果我们正在创建的对象已经存在，那么我们将首先删除它们。这使我们可以轻松修改下面的代码并重新运行多次以进行测试。

    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

    请求表 - 根据需要添加列：

    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);

    收据表。ReceiptID是系统生成的，ReceiptNum是我们要显示给用户的编号。

    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);

    Key 表，我们存储最近分配的 ReceiptNum 值：

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

    CreateRequests 存储过程：

    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

    在这里，我们添加三个不同的请求，以及不同数量的收据以显示存储过程的功能：

    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;

    输出：

    ╔═════════════╦═══════╗
║ 返回值 ║ 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 ║
╚═══════════╩═══════════╩════════════╩════════════ ═════════════╝

    运行#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;

    输出：

    ╔═════════════╦═══════╗
║ 返回值 ║ 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 ║
╚═══════════╩═══════════╩════════════╩════════════ ═════════════╝

    运行#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;

    输出：

    ╔═════════════╦═══════╗
║ 返回值 ║ 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 ║
╚═══════════╩═══════════╩════════════╩════════════ ═════════════╝

    为了验证并发性以及收据编号的分配是否可靠，我在三个单独的 SSMS 窗口中同时运行了以下代码，创建了近 30,000 张收据：

    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;
    • 9

  2. 我不确定适当的锁定。但是你可以找到表是否被锁定

    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
    • 1

相关问题