Prawidłowe użycie transakcji w SQL Server

236

Mam 2 polecenia i potrzebuję, aby oba zostały wykonane poprawnie lub żadne z nich nie zostało wykonane. Myślę więc, że potrzebuję transakcji, ale nie wiem, jak prawidłowo z niej korzystać.

W czym problem z następującym skryptem?

BEGIN TRANSACTION [Tran1]

INSERT INTO [Test].[dbo].[T1]
    ([Title], [AVG])
VALUES ('Tidd130', 130), ('Tidd230', 230)

UPDATE [Test].[dbo].[T1]
  SET [Title] = N'az2' ,[AVG] = 1
  WHERE [dbo].[T1].[Title] = N'az'

COMMIT TRANSACTION [Tran1]
GO

INSERTPolecenie jest wykonywane, ale UPDATEkomenda ma problem.

Jak mogę to zaimplementować, aby wycofać oba polecenia, jeśli któreś z nich ma błąd w wykonywaniu?

— Saeid
źródło

513

Dodaj blok try / catch, jeśli transakcja się powiedzie, zatwierdzi zmiany, jeśli transakcja się nie powiedzie, transakcja zostanie wycofana:

BEGIN TRANSACTION [Tran1]

  BEGIN TRY

      INSERT INTO [Test].[dbo].[T1] ([Title], [AVG])
      VALUES ('Tidd130', 130), ('Tidd230', 230)

      UPDATE [Test].[dbo].[T1]
      SET [Title] = N'az2' ,[AVG] = 1
      WHERE [dbo].[T1].[Title] = N'az'

      COMMIT TRANSACTION [Tran1]

  END TRY

  BEGIN CATCH

      ROLLBACK TRANSACTION [Tran1]

  END CATCH

— Darren
źródło

1

Nie powinien BEGIN TRANSACTION [Tran1]być umieszczony w środku TRY? W każdym razie - bardzo prosty i elegancki fragment kodu.

— Piotr Nawrot

4

@PiotrNawrot Nie, jeśli tworzenie transakcji nie powiodło się, nie ma potrzeby przywracania jej w catch.

— Monsignor

114

Na początku procedury składowanej należy ustawić SET XACT_ABORT ON, aby poinstruować serwer Sql, aby automatycznie wycofał transakcję w przypadku błędu. Jeśli zostaniesz zwolniony lub ustawiony na WYŁ., Po każdej instrukcji należy przetestować @@ BŁĄD lub użyć SPRÓBUJ ... Cofnij blok wycofania .

— Nikola Markovinović
źródło

2

Innymi słowy, twoja transakcja nie jest atomowa, chyba że najpierw ustawisz XACT_ABORT ON.

— 4.00

Trudno to zobaczyć z podkreśleniem XACT_ABORT

— adresu

32

Łatwe podejście:

CREATE TABLE T
(
    C [nvarchar](100) NOT NULL UNIQUE,
);

SET XACT_ABORT ON -- Turns on rollback if T-SQL statement raises a run-time error.
SELECT * FROM T; -- Check before.
BEGIN TRAN
    INSERT INTO T VALUES ('A');
    INSERT INTO T VALUES ('B');
    INSERT INTO T VALUES ('B');
    INSERT INTO T VALUES ('C');
COMMIT TRAN
SELECT * FROM T; -- Check after.
DELETE T;

— Bohdan
źródło