Início / dba / Perguntas / 334280
Accepted
The amateur programmer
Asked: 2023-12-22 19:03:07 +0800 CST

Noções básicas sobre bloqueio de tabela em transações MS SQL TSQL

  • 772

Eu queria tornar a consulta antiga a seguir segura contra corridas de dados ao executá-las em paralelo. A consulta verifica se existe uma linha em uma tabela usando critérios específicos e se tal linha não existir, insere uma nova com novos dados. A consulta antiga está colada abaixo:

BEGIN
    DECLARE @txtPer VARCHAR(MAX) = @nro;
    DECLARE @txtCmin VARCHAR(MAX) = @min;
    DECLARE @txtCmax VARCHAR(MAX) = @max;
    IF NOT EXISTS (SELECT 1 FROM SEND WHERE MSG LIKE (@txtPer + '%') AND STATE < 2 AND ID = 1)
    BEGIN
        ----Time of check is not time of use
        ----Someone could possibly do another insert before this == data race
        INSERT INTO SEND (SNDID, ID, MSGCODE, MSG, STATE, INFO, INFO_TEXT, CHANGEDATE, CREATEDATE)
        SELECT MAX(SNDID)+10,1,1,(@txtPer + @txtCmin + '           ' + @txtCmax),0,0,' ',getdate(),getdate() FROM SEND
    END
END

Eu criei uma nova versão que é um pouco mais limpa e usa bloqueio de tabela exclusivo:

BEGIN TRANSACTION;
DECLARE @txtPer VARCHAR(MAX) = @nro;
DECLARE @txtCmin VARCHAR(MAX) = @min;
DECLARE @txtCmax VARCHAR(MAX) = @max;

DECLARE @IdMax INT
--Get MAX and simultaneously acquire lock for the table to prevent modifications during this transaction?
SELECT @IdMax=MAX(SNDID) FROM SEND WITH(TABLOCKX)

IF NOT EXISTS(SELECT 1 FROM SEND WHERE MSG LIKE (@txtPer + '%') AND STATE < 2 AND ID = 1)
BEGIN
    INSERT INTO SEND (SNDID, ID, MSGCODE, MSG, STATE, INFO, INFO_TEXT, CHANGEDATE, CREATEDATE)
    VALUES((@IdMax + 10),1,1,(@txtPer + @txtCmin + '           ' + @txtCmax),0,0,' ',getdate(),getdate())
END
COMMIT; --writes potential change and releases table lock?

Meu entendimento está correto de que o bloqueio de tabela adquirido durante SELECT MAX()a operação (e possivelmente outros na mesma transação) é mantido até que toda a transação seja finalizada com a COMMITinstrução? DB é um antigo MS SQL Server 2005.

t-sql

2 respostas

  1. Best Answer

    Meu entendimento está correto de que o bloqueio de tabela adquirido durante a operação SELECT MAX() (e possivelmente outros na mesma transação) é mantido até que toda a transação seja concluída com a instrução COMMIT?

    Sim, desde que seja TABLOCKXe não TABLOCK. Isso só funciona se você usar bloqueio pessmista, e não bloqueio de instantâneo otimista, conforme mencionado por @DavidBrowne.

    No entanto, isso é extremamente ineficiente.

    • Isso bloqueará completamente a leitura ou gravação de toda a tabela. Em vez disso, use UPDLOCKe SERIALIZABLE(ou HOLDLOCK) dicas.
      • A SERIALIZABLEdica é necessária para bloquear até mesmo a inexistência dos dados (ou seja, um bloqueio de intervalo), garantindo que ninguém mais possa inserir.
      • A UPDLOCKdica é necessária para colocar bloqueios em U na mesa, o que evita que qualquer outra pessoa que solicite um bloqueio em U prossiga, ao mesmo tempo que permite consultas somente leitura. Isso tende a evitar conflitos no nível de isolamento serializável.
    • Certifique-se de que sua tabela esteja indexada nas ID, MSG, STATEcolunas, ou no ID, STATE, MSG, para que NOT EXISTSseja eficiente.
    • Ele também pesquisa o SNDIDvalor máximo em toda a tabela; em vez disso, você deve usar uma IDENTITYcoluna.
    • Use XACT_ABORT ONpara garantir que a reversão ocorra corretamente em caso de erros.
    SET XACT_ABORT ON;
BEGIN TRAN;

IF NOT EXISTS (SELECT 1
    FROM SEND WITH (UPDLOCK, SERIALIZABLE)
    WHERE MSG LIKE CONCAT(@nro, '%')
      AND STATE < 2
      AND ID = 1
)
BEGIN
        INSERT INTO SEND (ID, MSGCODE, MSG, STATE, INFO, INFO_TEXT, CHANGEDATE, CREATEDATE)
        VALUES (1, 1, CONCAT(@nro, @min, '           ', @max), 0, 0, ' ', getdate(), getdate());
END;

COMMIT;
    • 2

  2. Meu entendimento está correto de que o bloqueio de tabela adquirido durante a operação SELECT MAX() (e possivelmente outros na mesma transação) é mantido até que toda a transação seja concluída com a instrução COMMIT?

    Sim. Mas esse bloqueio não impedirá que outra sessão leia a tabela se o banco de dados estiver configurado com READ COMMITTED SNAPSHOT.

    A maneira correta de serializá-los é com uma dica de bloqueio no select que decide se a linha existe, assim:

    SELECT 1 FROM SEND WITH(UPDLOCK,HOLDLOCK)...

    • 1

relate perguntas