Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Транзакции в хранимых процедурах  [new]
Yadrinez
Member

Откуда: :адуктО
Сообщений: 360
Вопрос такой. MS SQL 2005. Есть ХП. В ней в транзакции выполняются две вставки.
То есть примерно так:
BEGIN TRY
  DECLARE 
    @Trans INT;
  ....
  BEGIN TRANSACTION 
    SET @Trans=@@TRANCOUNT
    INSERT..
    INSERT..
 COMMIT TRANSACTION 
END TRY
BEGIN CATCH
  --Определяем стратовала ли транзакция, если да откатываем ее
  IF @Trans>0
    ROLLBACK TRANSACTION
  ....
END CATCH

Но возникает затык в связи с этим
BOL

Если за время выполнения хранимой процедуры значение @@TRANCOUNT изменяется, происходит информационная ошибка (266). Это может случиться в двух следующих сценариях.

Хранимая процедура вызывается со значением @@TRANCOUNT, равным 1 или более, и выполняет инструкцию ROLLBACK TRANSACTION. Значение @@TRANCOUNT уменьшается до 0, что приводит к ошибке 266 при завершении хранимой процедуры.

Хранимая процедура вызывается со значением @@TRANCOUNT, равным 1 или более, и выполняет инструкцию COMMIT TRANSACTION. Значение @@TRANCOUNT уменьшается на 1, что приводит к ошибке 266 при завершении хранимой процедуры. Однако если после инструкции COMMIT TRANSACTION будет выполнена инструкция BEGIN TRANSACTION, ошибка не возникнет.


То есть при вызове процедуры во внешей транзакции вываливается ошибка типа
MS SQL
Msg 266, Level 16, State 2, Procedure iv_InsertPostClnt, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.


В связи с этим вопрос! Использовать ли транзакции в хранимой процедуре. Или лучше отказаться от них.
Но тогда обязательно придеться каждую процедуру вызывать в транзакции.
Как обычно все это делается? Мне требуется в произвести несколько инсертов, причем если один из них не прошел, то откатить все предыдущие
27 окт 09, 12:47    [7843779]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36818
Что бы не было ошибок, транзакцию во вложенных процедурах откатывать нельзя. Поищите по форуму, шаблонов предлагалась масса.
27 окт 09, 12:49    [7843796]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
весь текст процедуры не виден...

я думаю, что если вы вызваете эту процедуру в уже открытой транзакции, то
ваш коммит к примеру закроет и охватывающую, или роллбэк откати охватывающую тоже.
Потому и ошибка.
Можно делать точки сохранения и до них откатывать, или закрывать, или не открывать впроцедуре, если она уже в транзакции
типа такого:

if @@TRANCOUNT=0
begin
begin tran

end
27 окт 09, 12:52    [7843820]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Yadrinez
Member

Откуда: :адуктО
Сообщений: 360
В БОЛе нашел неплохой пример и поиск по форуму помог

В примере показано использование точки сохранения транзакции для отката изменений, сделанных хранимой процедурой, если выполнение активной транзакции запущено до выполнения хранимой процедуры.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.objects
           WHERE name = N'SaveTranExample')
    DROP PROCEDURE SaveTranExample;
GO
CREATE PROCEDURE SaveTranExample
    @InputCandidateID INT
AS
    -- Detect if the procedure was called
    -- from an active transaction and save
    -- that for later use.
    -- In the procedure, @TranCounter = 0
    -- means there was no active transaction
    -- and the procedure started one.
    -- @TranCounter > 0 means an active
    -- transaction was started before the 
    -- procedure was called.
    DECLARE @TranCounter INT;
    SET @TranCounter = @@TRANCOUNT;
    IF @TranCounter > 0
        -- Procedure called when there is
        -- an active transaction.
        -- Create a savepoint to be able
        -- to roll back only the work done
        -- in the procedure if there is an
        -- error.
        SAVE TRANSACTION ProcedureSave;
    ELSE
        -- Procedure must start its own
        -- transaction.
        BEGIN TRANSACTION;
    -- Modify database.
    BEGIN TRY
        DELETE HumanResources.JobCandidate
            WHERE JobCandidateID = @InputCandidateID;
        -- Get here if no errors; must commit
        -- any transaction started in the
        -- procedure, but not commit a transaction
        -- started before the transaction was called.
        IF @TranCounter = 0
            -- @TranCounter = 0 means no transaction was
            -- started before the procedure was called.
            -- The procedure must commit the transaction
            -- it started.
            COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- An error occurred; must determine
        -- which type of rollback will roll
        -- back only the work done in the
        -- procedure.
        IF @TranCounter = 0
            -- Transaction started in procedure.
            -- Roll back complete transaction.
            ROLLBACK TRANSACTION;
        ELSE
            -- Transaction started before procedure
            -- called, do not roll back modifications
            -- made before the procedure was called.
            IF XACT_STATE() <> -1
                -- If the transaction is still valid, just
                -- roll back to the savepoint set at the
                -- start of the stored procedure.
                ROLLBACK TRANSACTION ProcedureSave;
                -- If the transaction is uncommitable, a
                -- rollback to the savepoint is not allowed
                -- because the savepoint rollback writes to
                -- the log. Just return to the caller, which
                -- should roll back the outer transaction.

        -- After the appropriate rollback, echo error
        -- information to the caller.
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE();
        SELECT @ErrorSeverity = ERROR_SEVERITY();
        SELECT @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, -- Message text.
                   @ErrorSeverity, -- Severity.
                   @ErrorState -- State.
                   );
    END CATCH
GO
27 окт 09, 13:16    [7844047]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
UniqueNick
Member

Откуда: Москва
Сообщений: 42
Winnipuh

я думаю, что если вы вызваете эту процедуру в уже открытой транзакции, то
ваш коммит к примеру закроет и охватывающую...

Commit "завершает" только одну транзакцию (начавшуюся последней) (если их было несколько, то фактически он не делает ничего, кроме уменьшения @@TranCount)

Вложенные транзакции вполне нормально работают
27 окт 09, 13:45    [7844287]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
UniqueNick
Winnipuh

я думаю, что если вы вызваете эту процедуру в уже открытой транзакции, то
ваш коммит к примеру закроет и охватывающую...

Commit "завершает" только одну транзакцию (начавшуюся последней) (если их было несколько, то фактически он не делает ничего, кроме уменьшения @@TranCount)

Вложенные транзакции вполне нормально работают


т.е. COMMIT TRAN завершает только текущую транзакцию????
вы уверены в этом?
27 окт 09, 14:08    [7844519]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Кудряшка
Member

Откуда: Сидней
Сообщений: 2219
Winnipuh

т.е. COMMIT TRAN завершает только текущую транзакцию????
вы уверены в этом?


конечно.
а вот ROLLBACK откатывает все открытые транзакции в текущем подключении.
27 окт 09, 14:51    [7844799]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Кудряшка
Winnipuh

т.е. COMMIT TRAN завершает только текущую транзакцию????
вы уверены в этом?


конечно.
а вот ROLLBACK откатывает все открытые транзакции в текущем подключении.


да, точно
27 окт 09, 14:52    [7844808]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Кудряшка
Member

Откуда: Сидней
Сообщений: 2219
Yadrinez,

А зачем нужна переменная @Trans ?
Почему не используете просто @@TRANCOUNT ?
27 окт 09, 14:54    [7844816]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Yadrinez
Member

Откуда: :адуктО
Сообщений: 360
Кудряшка
Yadrinez,

А зачем нужна переменная @Trans ?
Почему не используете просто @@TRANCOUNT ?


Ее сперва использовал на всякий случай, помятуя о примечании MS по поводу транзакций в ХП.
Потом наткнулся в БОЛе на пример. Взял его на вооружение. Оказалось использовал не зря.

Сейчас нормально ХП отрабатывает и при вызове в транзакции внешней или без нее.
27 окт 09, 15:08    [7844925]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Полностью согдасен с Кудряшка и UniqueNick. COMMIT закрывает последнюю открытую транзакцию, а ROLLBACK откатывает все открытые транзакции.
Все норм работает, сам тестил.
27 окт 09, 15:18    [7845032]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
LexMinsk
Member

Откуда: Republic of Belarus
Сообщений: 451
Вот пример из BOL
CREATE TABLE my_books
    (
    Isbn        int PRIMARY KEY,
    Title       NVARCHAR(100)
    );
GO

BEGIN TRY
    BEGIN TRANSACTION;
        -- This statement will generate an error because the 
        -- column author does not exist in the table.
        ALTER TABLE my_books
            DROP COLUMN author;
    -- If the DDL statement succeeds, commit the transaction.
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() as ErrorNumber,
        ERROR_MESSAGE() as ErrorMessage;

    -- Test XACT_STATE for 1 or -1.
    -- XACT_STATE = 0 means there is no transaction and
    -- a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.
    IF (XACT_STATE()) = -1
    BEGIN
        PRINT
            N'The transaction is in an uncommittable state. ' +
            'Rolling back transaction.'
        ROLLBACK TRANSACTION;
    END;

    -- Test whether the transaction is active and valid.
    IF (XACT_STATE()) = 1
    BEGIN
        PRINT
            N'The transaction is committable. ' +
            'Committing transaction.'
        COMMIT TRANSACTION;   
    END;
END CATCH;

27 окт 09, 15:24    [7845093]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Кудряшка
Member

Откуда: Сидней
Сообщений: 2219
Yadrinez,

Я имею ввиду почему не так?

IF @@TRANCOUNT > 0
   COMMIT

.....

IF @@TRANCOUNT > 0
    ROLLBACK
27 окт 09, 15:45    [7845244]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Aleksey-K
Member

Откуда: Москва
Сообщений: 3116
Yadrinez
Кудряшка
Yadrinez,

А зачем нужна переменная @Trans ?
Почему не используете просто @@TRANCOUNT ?


Ее сперва использовал на всякий случай, помятуя о примечании MS по поводу транзакций в ХП.
Потом наткнулся в БОЛе на пример. Взял его на вооружение. Оказалось использовал не зря.

Сейчас нормально ХП отрабатывает и при вызове в транзакции внешней или без нее.

Если хотите, что бы внутренняя хранимая процедура не откатывала по ROLLBACK изменения, которые были внесены во внешнем модуле, можно использовать SAVE TRANSACTION
1. При входе в ХП открывате новую транзакицю, если она не было еще открыта (@@TRANCOUNT = 0)
2. Если транзакция уже открыта (@@TRANCOUNT > 0), то выполняете SAVE TRANSACTION NameTran
3. Commit делаете только в том случае, если сами открывали транзакцию
4. При ошибке откат полный (ROLLBACK TRANSACTION ) делаете только в том случае, если открывали транзакцию в ХП.
Иначе проверяет флаг XACT_STATE() . Если он <> -1, то имеет смысл вернутся к точки сохранения через ROLLBACK TRANSACTION NameTran
С уважением, Алексей
27 окт 09, 15:49    [7845276]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Кудряшка
Yadrinez,

А зачем нужна переменная @Trans ?
Почему не используете просто @@TRANCOUNT ?


 DECLARE @TranCounter INT;
    SET @TranCounter = @@TRANCOUNT;
    IF @TranCounter > 0
        -- Procedure called when there is
        -- an active transaction.
        -- Create a savepoint to be able
        -- to roll back only the work done
        -- in the procedure if there is an
        -- error.
        SAVE TRANSACTION ProcedureSave;
    ELSE
        -- Procedure must start its own
        -- transaction.
        BEGIN TRANSACTION;
                                     <-------------здесь они содержат уже разные значения 
    -- Modify database.
27 окт 09, 15:50    [7845292]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Yadrinez
Member

Откуда: :адуктО
Сообщений: 360
Aleksey-K
Yadrinez
Кудряшка
Yadrinez,

А зачем нужна переменная @Trans ?
Почему не используете просто @@TRANCOUNT ?


Ее сперва использовал на всякий случай, помятуя о примечании MS по поводу транзакций в ХП.
Потом наткнулся в БОЛе на пример. Взял его на вооружение. Оказалось использовал не зря.

Сейчас нормально ХП отрабатывает и при вызове в транзакции внешней или без нее.

Если хотите, что бы внутренняя хранимая процедура не откатывала по ROLLBACK изменения, которые были внесены во внешнем модуле, можно использовать SAVE TRANSACTION
1. При входе в ХП открывате новую транзакицю, если она не было еще открыта (@@TRANCOUNT = 0)
2. Если транзакция уже открыта (@@TRANCOUNT > 0), то выполняете SAVE TRANSACTION NameTran
3. Commit делаете только в том случае, если сами открывали транзакцию
4. При ошибке откат полный (ROLLBACK TRANSACTION ) делаете только в том случае, если открывали транзакцию в ХП.
Иначе проверяет флаг XACT_STATE() . Если он <> -1, то имеет смысл вернутся к точки сохранения через ROLLBACK TRANSACTION NameTran
С уважением, Алексей


Примерно так и сделал. В БОЛе пример наверху привел. И по нему сделал, слегка его изменив.
27 окт 09, 16:17    [7845460]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
Кудряшка
Yadrinez,

Я имею ввиду почему не так?

IF @@TRANCOUNT > 0
   COMMIT

.....

IF @@TRANCOUNT > 0
    ROLLBACK


в частности - транзакция может быть начата не вами
27 окт 09, 19:08    [7846436]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции в хранимых процедурах  [new]
Crimean
Member

Откуда:
Сообщений: 13148
тему не читал, но использование try / catch осуждаю :)
остальное уже вроде как понаписали
да, сейвпоинты - зло, хотя часто неизбежное
просаживает скорость, ставит грабли при распределенной работе
27 окт 09, 20:07    [7846582]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить