Início / dba / Perguntas / 134265
Accepted
Simon Novak
Asked: 2016-04-04 22:31:47 +0800 CST

Dica de updlock no procedimento armazenado

  • 772

Se alguém executa o procedimento armazenado abaixo muito rápido (alguns milhares de execução por segundo), a variável @amount pode mudar durante a execução e executar a atualização mais de uma vez?

CREATE PROCEDURE [dbo].[PayoutInvestment] 
    @userid AS VARCHAR(50)
    ,@idi AS INT
AS
BEGIN
    BEGIN TRANSACTION;

    DECLARE @amount AS DECIMAL(18, 2) = (
            SELECT amount
            FROM investments
            WHERE userid = @userid
                AND idi = @idi
                AND date_stop IS NULL
            );

    IF (@amount IS NOT NULL)
    BEGIN
        UPDATE investments
        SET date_stop = GETUTCDATE()
        WHERE idi = @idi;

        UPDATE u
        SET balance = u.balance + @amount
        FROM users u
        INNER JOIN investments i ON u.userid = i.userid
        WHERE i.idi = @idi;
    END;

    COMMIT TRANSACTION;
END

Eu sei que funciona 100% se eu fizer isso com dica de updlock. Mas gostaria de saber se é necessário fazer dessa forma.

CREATE PROCEDURE [dbo].[PayoutInvestment] 
    @userid AS VARCHAR(50)
    ,@idi AS INT
AS
BEGIN
    BEGIN TRANSACTION;

    DECLARE @amount AS DECIMAL(18, 2) = (
            SELECT amount
            FROM investments WITH (
                    ROWLOCK
                    ,UPDLOCK
                    )
            WHERE userid = @userid
                AND idi = @idi
                AND date_stop IS NULL
            );

    IF (@amount IS NOT NULL)
    BEGIN
        UPDATE investments
        SET date_stop = GETUTCDATE()
        WHERE idi = @idi;

        UPDATE u
        SET balance = u.balance + @amount
        FROM users u
        INNER JOIN investments i ON u.userid = i.userid
        WHERE i.idi = @idi;
    END

    COMMIT TRANSACTION;
END

Estou usando o nível de isolamento Read Committed.

sql-server concurrency

1 respostas

  1. Best Answer

    Cada execução do procedimento armazenado obtém uma cópia separada da variável, portanto, esse não é o risco com o qual você precisa se preocupar. O valor da variável é 'privado' para sua transação no sentido de que nenhuma outra transação simultânea pode vê-la ou alterar seu valor (elas têm sua própria variável).

    O problema de simultaneidade com o qual você precisa se preocupar é que os dados no banco de dados podem mudar entre as instruções ou até mesmo durante a execução de uma instrução.

    O uso de uma dica de bloqueio de atualização na tabela de investimentos apenas evita que a linha tocada seja alterada por uma sessão simultânea antes da conclusão da transação. Isso não necessariamente impede que outras alterações no banco de dados (como o usuário que possui aquele investimento mude ou o id do usuário seja alterado), nem impede que outras transações leiam esse valor.

    Em vez de adicionar dicas de bloqueio aleatórias que exigem uma análise detalhada de especialistas para avaliar (e ainda são fáceis de errar), geralmente é mais simples e sensato pensar no nível de isolamento que sua transação exige.

    Se você precisar que a transação tenha efeitos persistentes como se fosse a única transação em execução no sistema (eliminando assim as preocupações com alterações simultâneas), você pode executá-la no nível de isolamento serializável. Se o isolamento de instantâneo (não RCSI!) estiver disponível para você e os conflitos de gravação forem raros, esse também pode ser um nível de isolamento adequado para usar. Outra solução do tipo serialização seria usar bloqueios de aplicativo.

    Por exemplo, usando isolamento serializável:

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

DECLARE @amount decimal(18,2);

UPDATE dbo.investments
SET date_stop = GETUTCDATE(),
    @amount = amount
FROM dbo.investments AS I
JOIN dbo.users AS U
    ON U.userid = I.userid
WHERE 
    I.idi = @idi;

IF @amount IS NOT NULL
BEGIN
    UPDATE dbo.users
    SET balance += @amount
    WHERE userid = @userid;
END;

COMMIT TRANSACTION;

    Você precisará adicionar tratamento de erros para detectar e resolver quaisquer impasses ou conflitos de gravação que possam ocorrer.

    Para obter mais informações, consulte minha série de artigos sobre níveis de isolamento do SQL Server:

    Níveis de Isolamento do SQL Server: Série A

    • 4

relate perguntas