T SQl. Хранимая процедура + транзакция пишут лишние строки в БД

Question

Простая задача: есть юзеры, надо снять деньги у одного и перевести другому. Транзакция 'запрос на снятие денег --- проверка баланса --- перевод' оформлена как хранимая процедура. Работает корректно.

USE Market;   -- db name

GO
CREATE PROCEDURE TransferMoney
  @SenderID INT,
  @ReceiverID INT,
  @TransferAmount DECIMAL(18,2)
AS
BEGIN
  BEGIN TRANSACTION;

  DECLARE @SenderBalance DECIMAL(18,2);
  SELECT @SenderBalance = Amount FROM Customers WHERE Id = @SenderID;

  DECLARE @ReceiverBalance DECIMAL(18,2);
  SELECT @ReceiverBalance = Amount FROM Customers WHERE Id = @ReceiverID;

  IF @SenderBalance < @TransferAmount
  BEGIN
  ROLLBACK TRANSACTION;
  RAISERROR ('Insufficient balance in sender account', 16, 1);
  RETURN;
  END

  -- Amount неудачное название, Balance был бы лучше
  -- 'Customers' - TableName
  UPDATE Customers
  SET Amount = Amount - @TransferAmount
  WHERE Id = @SenderID;

  UPDATE Customers
  SET Amount = Amount + @TransferAmount
  WHERE Id = @ReceiverID;

  INSERT INTO Customers(Amount)
  VALUES (@SenderBalance);

  INSERT INTO Customers(Amount)
  VALUES (@ReceiverBalance);

  COMMIT TRANSACTION;
END;

Вызов процедуры

USE Market;
DECLARE @N1 INT, @N2 INT, @S DECIMAL(18,2)
SET @N1 = 4 
SET @N2 = 5
SET @S = 300
EXEC TransferMoney @N1, @N2, @S

Проблема в том, что при прошедшей транзакции в таблицу пишутся новые строки.

Как от этого избавиться?

Answer 1

Если в таблице имеется ограничение CHECK (Amount >= 0), то достаточно простого

UPDATE Customers
SET Amount = CASE WHEN Id = @SenderID 
                  THEN Amount - @TransferAmount
                  ELSE Amount + @TransferAmount
                  END
WHERE Id IN (@SenderID, @ReceiverID);

Если такого ограничения не имеется, то его следует проверить в дополнительном подзапросе:

UPDATE Customers
SET Amount = CASE WHEN Id = @SenderID 
                  THEN Amount - @TransferAmount
                  ELSE Amount + @TransferAmount
                  END
WHERE Id IN (@SenderID, @ReceiverID)
  AND EXISTS ( SELECT NULL
               FROM Customers 
               WHERE Id = @SenderID
                 AND @TransferAmount <= Amount
               );

Если баланс плательщика содержит достаточное количество средств, обновятся две записи, иначе ни одной. А поскольку это один запрос, транзакция тут и не требуется.

Было выполнено обновление или нет, можно проверить стандартно, запросом SELECT @@ROWCOUNT;. Если платёж прошёл, вернётся 2, если нет, то 0 (и можно делать RAISERROR). Ну а если вернулось что-то третье, то всё плохо...

PS. Надеюсь, на момент выполнения операции гарантированно проверено, что и плательщик, и получатель существуют, а соотв. уникальное ограничение обеспечивает единственность записей.

БЛОГ НА HUSL

T SQl. Хранимая процедура + транзакция пишут лишние строки в БД

Ответы (1 шт):