Nie używaj transakcji do procedury składowanej

Mam procedurę składowaną, która uruchamia kilka poleceń. Nie chcę, aby te polecenia były pakowane w transakcję procedury składowanej. Jeśli 4. polecenie nie powiedzie się, chcę, aby 1., 2. i 3. pozostały, a nie wycofały się.

Czy można zapisać procedurę składowaną w taki sposób, aby nie wszystkie były wykonywane jako jedna duża transakcja?

Wszystkie transakcje nie zostaną wykonane w jednej transakcji. Spójrz na ten przykład:

use TestDB;
go

if exists (select 1 from sys.tables where object_id = object_id('dbo.TestTranTable1'))
    drop table dbo.TestTranTable1;
create table dbo.TestTranTable1
(
    id int identity(1, 1) not null,
    some_int int not null
        default 1
);
go

insert into dbo.TestTranTable1
default values;
go 4

select *
from dbo.TestTranTable1;

if exists (select 1 from sys.sql_modules where object_id = object_id('dbo.ChangeValues'))
begin
    drop proc dbo.ChangeValues;
end
go
create proc dbo.ChangeValues
as
    update dbo.TestTranTable1
    set some_int = 11
    where id = 1;

    update dbo.TestTranTable1
    set some_int = 12
    where id = 2;

    update dbo.TestTranTable1
    set some_int = 13
    where id = 3;

    -- this will error out (arithmetic overflow)
    update dbo.TestTranTable1
    set some_int = 2147483648
    where id = 4;
go

exec dbo.ChangeValues;

select *
from dbo.TestTranTable1;

Oto wynik:

Tworząc sesję zdarzeń rozszerzonych w celu monitorowania sql_transactionzdarzenia, oto wynik działaniadbo.ChangeValues :

Jak widać na powyższym zrzucie ekranu, dla każdego z czterech wyciągów są osobne transakcje. Pierwsze 3 zatwierdzenie, a ostatnie wycofuje się z powodu błędu.

Myślę, że może tu być pewne zamieszanie dotyczące partii w porównaniu do transakcji .

Transakcja jest oświadczenie lub zestaw instrukcji, które będą albo uda lub nie jako jednostki. Wszystkie instrukcje DDL są w samych transakcjach (tzn. Jeśli zaktualizujesz 100 wierszy, ale wiersz 98 zgłasza błąd, żaden z wierszy nie zostanie zaktualizowany). Możesz zawinąć szereg wyciągów w transakcję, używając, BEGIN TRANSACTIONa następnie alboCOMMIT albo ROLLBACK.

partii jest szereg stwierdzeń, które są wykonywane razem. Procedura składowana jest przykładem partii. W procedurze składowanej, jeśli jedna instrukcja zawiedzie i wystąpi pułapka błędu (zwykle TRY/CATCHblokuje), kolejne instrukcje nie zostaną wykonane.

Podejrzewam, że twój problem polega na tym, że partia jest anulowana, gdy wystąpi błąd, ponieważ albo w przechowywanym proc, albo w zewnętrznym zakresie (takim jak aplikacja lub przechowywany proc, który wywołuje tę procedurę) występuje pułapka błędu. W takim przypadku trudniej jest to rozwiązać, ponieważ musisz dostosować sposób obsługi błędów w dowolnym zakresie, w którym są one zatrzymywane.

Wszystko na serwerze SQL jest zawarte w transakcji.

Gdy wyraźnie to określisz, begin transactiona end transactionnastępnie nazywa się to Transakcja jawna . Jeśli nie, to jest to transakcja niejawna .

Aby zmienić tryb, w którym się znajdujesz, możesz użyć

set implicit_transactions on

lub

set implicit_transactions off

select @@OPTIONS & 2

jeśli powyżej zwraca 2, jesteś w trybie transakcji niejawnych. Jeśli zwróci 0, jesteś w automatycznym zatwierdzaniu.

Transakcja jest WSZYSTKO lub nic, aby utrzymać bazę danych w spójnym stanie. Pamiętaj właściwości ACID.

CREATE TABLE [dbo].[Products](
    [ProductID] [int] NOT NULL,
    [ProductName] [varchar](25) NULL,
    [DatabaseName] [sysname] NOT NULL,
 CONSTRAINT [pk_Product_ID_ServerName] PRIMARY KEY CLUSTERED 
(
    [ProductID] ASC,
    [DatabaseName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


-- insert some data 
INSERT INTO [dbo].[Products]([ProductID], [ProductName], [DatabaseName])
SELECT 1, N'repl1_product1', N'repl1' UNION ALL
SELECT 1, N'repl2_product1_02', N'repl2' UNION ALL
SELECT 1, N'repl3_product1_03', N'repl3' UNION ALL
SELECT 2, N'repl1_product1_01', N'repl1' UNION ALL
SELECT 2, N'repl2_product1', N'repl2' UNION ALL
SELECT 2, N'repl3_product1_03', N'repl3' UNION ALL
SELECT 3, N'repl1_product1_01', N'repl1' UNION ALL
SELECT 3, N'repl2_product1_02', N'repl2' UNION ALL
SELECT 3, N'repl3_product1', N'repl3' UNION ALL
SELECT 4, N'repl1_product1_01', N'repl1' UNION ALL
SELECT 4, N'repl2_product1_02', N'repl2' UNION ALL
SELECT 5, N'repl1_product1_01', N'repl1' UNION ALL
SELECT 5, N'repl2_product1_02', N'repl2'

- utwórz teraz SP - zwróć uwagę, że pierwsze 3 odniosą sukces, a czwarte nie powiedzie się z powodu obcięcia łańcucha ...

IF OBJECT_ID ('usp_UpdateProducts', 'P') IS NOT NULL
    DROP PROCEDURE usp_UpdateProducts;
GO
create procedure usp_UpdateProducts
as 
begin try
update Products 
set ProductName = 'repl1_product1'
where DatabaseName = 'repl1'and ProductID = 1;
update Products
set ProductName = 'repl2_product1'
where DatabaseName = 'repl2' and ProductID = 2;
update Products
set ProductName = 'repl3_product1'
where DatabaseName = 'repl3' and ProductID = 3;
update Products
set ProductName = 'repl3_product1_03&&&&&&&&&&39399338492w9924389234923482' -- this will fail ...
where DatabaseName = 'repl3' and ProductID = 4;
SELECT 1/0;
end try
begin catch
SELECT 
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() as ErrorState,
        ERROR_PROCEDURE() as ErrorProcedure,
        ERROR_LINE() as ErrorLine,
        ERROR_MESSAGE() as ErrorMessage;
end catch
go

Zobacz: Czy zawsze jest to zła praktyka?

W ten sposób domyślnie działają procedury składowane. Procedura przechowywana nie jest automatycznie pakowana w transakcję.

Jeśli chcesz, aby procedura przechowywana zatrzymała się, gdy wystąpi pierwszy błąd, umieść tam login TRY / CATCH, aby powrócić w przypadku na przykład problemu z poleceniem 2.

Będziesz potrzebował indywidualnych transakcji dla każdego polecenia. Możesz to również zrobić za pomocą zapisanych transakcji:

Widzieć SAVE TRANSACTION (Transact-SQL) w dokumentacji produktu.

Chcę zakwalifikować te pojedyncze transakcje jako zachowanie domyślne dla procedur przechowywanych, ponieważ wszystkie instrukcje są opakowane w transakcje niejawne; jednak nikt nie powinien polegać na niejawnych transakcjach w celu kontrolowania przeznaczenia swojego kodu. O wiele lepszą praktyką jest jawne kontrolowanie sposobu obsługi transakcji w kodzie produkcyjnym.

oddziel każdą część BEGIN TRAN i sprawdź, czy transakcja się powiodła. jeśli zostało to zatwierdzone, w przeciwnym razie wykonaj wycofanie, ponieważ wszystkie wykonują z tego samego poziomu, będziesz mógł zatwierdzić każdą sekcję osobno, bez konieczności cofania wszystkich, jeśli jedna zawiedzie.

Więcej informacji można znaleźć na stronie: http://msdn.microsoft.com/en-us/library/ms188929.aspx