Início / dba / Perguntas / 175137
Accepted
ABS
Asked: 2017-06-02 03:23:36 +0800 CST

Transação com bloqueio em uma tabela específica

  • 772

NOTA: Se houver uma pergunta semelhante, por favor , encaminhe-me para ela.

Eu tenho duas tabelas Request& Receipt.

Eu quero que a tabela Receiptpermaneça bloqueada para operações CRUD de qualquer lugar até que a transação seja concluída. Eu preciso que a transação esteja em um procedimento armazenado.

É impossível? Como?

Por que deve Receiptpermanecer bloqueado? Porque uma solicitação interna de inserção ou exclusão Receiptnão poderá manipulá-la! Observe que eu preciso ReceiptIdser consecutivo por cada um Requestde seu startpara seu finish!

detalhes

sql-server stored-procedures

2 respostas

  1. Best Answer

    Use uma tabela de chaves para alocar números de recibos de uma maneira que seja amigável à simultaneidade e ainda garanta que os números de recibos nunca serão reutilizados e (quase) nunca conterão lacunas.

    Eu criei um exemplo verificável minimamente completo que você pode usar como base para aprender sobre simultaneidade que deve colocá-lo em um bom caminho.

    Faça este trabalho em tempdb para que não matemos nada importante em seu trabalho real:

    USE tempdb;

    Se os objetos que estamos criando já existirem, vamos excluí-los primeiro. Isso nos permite modificar facilmente o código abaixo e executá-lo várias vezes para teste.

    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

    Tabela de solicitações - adicione colunas conforme necessário:

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

    Tabela de recibos. ReceiptID é gerado pelo sistema, ReceiptNum é o número que mostraremos aos usuários.

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

    A tabela Key, onde armazenamos o valor ReceiptNum alocado mais recentemente:

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

    O procedimento armazenado 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

    Aqui, adicionamos três solicitações diferentes, com um número variável de recibos para mostrar a funcionalidade do procedimento armazenado:

    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;

    A saída:

    ╔═════════════╦═══════╗
║ 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 ║ 01-06-2017 09:04:37.107 ║
║ 14 ║ 1 ║ 14 ║ 01-06-2017 09:04:37.107 ║
║ 15 ║ 1 ║ 15 ║ 01-06-2017 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 ║
╚═══════════╩═══════════╩════════════╩════════════ ═════════════╝

    Execução nº 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;

    A saída:

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

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

    Execução nº 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;

    A saída:

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

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

    Para verificar a simultaneidade e se a alocação de números de recibo é confiável, executei o seguinte código simultaneamente em três janelas SSMS separadas, criando quase 30.000 recibos:

    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. Não tenho certeza sobre o travamento apropriado. mas você pode descobrir se a tabela está bloqueada

    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

relate perguntas