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

Откуда:
Сообщений: 77
Доброго времени суток.
Возникла такая проблема: некую ХП нужно выполнить на нескольких не связанных друг с другом серверах, при наличии ошибки выполнения на одном из серверов откатить выполнение ХП на всех, где ХП ранее была выполнена успешно - исходя из этого требования, транзакции могут быть только клиентскими; приложение выступает как координатор транзакций. Далее, весь T-SQL-ный код в ХП заключен в begin try/end try/begin catch/end catch, т.к. ХП может выбрасывать ошибки бизнес-логики (через raiserror с serverity=16), а такие ошибки без begin try не прерывают исполнение T-SQL кода. И всё бы ничего - само приложение нормально ловит ошибки, но в тексте сообщения постоянно фигурирует Uncommitable transaction detected at the end of batch - что, в общем-то, понятно, я транзакцию, которая находится в uncommitable state, выпускаю за пределы батча (ХП), а это отдельная ошибка. Если я откатываю транзакцию (с проверкой xact_state) внутри ХП, то вылетает вот такое:

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.
Previous count = 1, current count = 0.

что, в общем-то, тоже понятно, почему. В результате, чтобы получить ошибку, из-за которой T-SQL код сваливается в catch-блок, я сделал так (в упрощенном виде):
alter procedure tmp.up_test
as
begin
  set xact_abort on;
  begin try
    raiserror('test', 16, 0);
    insert into tmp.test values(0);
    insert into tmp.test values(0);
  end try
  begin catch
    if xact_state() in (-1, 1)
    begin
      rollback;
      begin tran; -- dummy transaction
    end;
    declare @err_msg varchar(2000);
    set @err_msg=dbo.uf_ErrMsg();
    raiserror(@err_msg, 16, 1);
  end catch
end;

(таблица tmp.test вот такая:
create table tmp.test (n int not null primary key);

)
+ клиентский код (тоже в упрощенном виде):
static void Main(string[] args)
{
  using (var cnn = new SqlConnection("Data Source=.;Initial Catalog=test;Integrated Security=True"))
  {
    cnn.Open();
    using (var tran = cnn.BeginTransaction())
    using(var cmd = new SqlCommand("tmp.up_test", cnn, tran){CommandType =  CommandType.StoredProcedure})
    {
      try
      {
        cmd.ExecuteNonQuery();
        Console.WriteLine("commiting...");
        tran.Commit();
      }
      catch(Exception e)
      {
        Console.WriteLine("rolling back...");
        tran.Rollback();
        Console.WriteLine(e.Message);
      }
    }
  }
}

что во всем в это не устраивает - во-первых, тем, что откатом транзакции на сервере, где появилась ошибка, занимается все-таки серверный код, а не клиентский; во-вторых, необходимость открытия той самой dummy transaction. Нет ли способа добиться, чтобы в итоге незакоммиченная uncommitable transaction всё же добиралась до клиентского кода?
Сервера, на которых выполняется ХП - 2005-й и 2008-й R2, поэтому при написании ХП ориентировался на возможности 2005-го.
14 окт 15, 16:09    [18278759]     Ответить | Цитировать Сообщить модератору
 Re: Клиентская транзакция и обработка ошибок внутри хранимой процедуры  [new]
Владислав Колосов
Member

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

уберите xact_abort
уберите rollback и блока catch.

Всю работу с транзакциями должен выполнять клиент.
Алгоритм клиента такой:
1. открыть транзакцию N
2. выполнить процедуру N
3. обработать ошибку N

4. завершить все транзакции на всех подключениях (успех/откат).

try .. catch нужен только для прекращения выполнения пакета и не более.
15 окт 15, 00:16    [18280547]     Ответить | Цитировать Сообщить модератору
 Re: Клиентская транзакция и обработка ошибок внутри хранимой процедуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31910
WinterGraveyard
что во всем в это не устраивает - во-первых, тем, что откатом транзакции на сервере, где появилась ошибка, занимается все-таки серверный код, а не клиентский; во-вторых, необходимость открытия той самой dummy transaction. Нет ли способа добиться, чтобы в итоге незакоммиченная uncommitable transaction всё же добиралась до клиентского кода?
Странно, что вы хотите управлять транзакциями с клиента, и при этом используете try...catch

Одного set xact_abort on было бы достаточно

Правда, нельзя будет генерить ошибку. Ну да, вот в сиквеле (кроме последнего) так делать нельзя.

Нужно с этим смириться, с ограниченным йункционалом обработки ошибок в T-SQL.

Либо уж вставить таки пару строк в клиента, что бы обрабатывать ошибки нежелательного вида :-)
15 окт 15, 01:06    [18280648]     Ответить | Цитировать Сообщить модератору
 Re: Клиентская транзакция и обработка ошибок внутри хранимой процедуры  [new]
WinterGraveyard
Member

Откуда:
Сообщений: 77
alexeyvg
Одного set xact_abort on было бы достаточно
Правда, нельзя будет генерить ошибку. Ну да, вот в сиквеле (кроме последнего) так делать нельзя.

Да, действительно, вполне достаточно. И, кстати, ошибки бизнес-логики (raiserror) вполне попадают на клиента, и там вполне себе считаются ошибками (т.е. вызывают попадание в catch, и полная информация об ошибке - имя процедуры, сообщение, строка возникновения, severity, state - доступны через экземляр типа SqlException). Надо просто каждый raiserror скомбинировать с return, и получится примерно то, что надо.
Спасибо.
15 окт 15, 07:19    [18280802]     Ответить | Цитировать Сообщить модератору
 Re: Клиентская транзакция и обработка ошибок внутри хранимой процедуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31910
WinterGraveyard
И, кстати, ошибки бизнес-логики (raiserror) вполне попадают на клиента, и там вполне себе считаются ошибками
Да, это само собой.
WinterGraveyard
Надо просто каждый raiserror скомбинировать с return, и получится примерно то, что надо.
Да. Единственно, что неудобно, по сравнению с нормальной обработкой ошибок - что при вложенных процедурах return вернёт управление на уровень выше, в вызывающую процедуру, а не в клиента.
В этом смысле нормальный throw, в сочетании с set xact_abort on, был бы удобнее. Но - нету, ничего не поделаешь.
15 окт 15, 09:59    [18281290]     Ответить | Цитировать Сообщить модератору
 Re: Клиентская транзакция и обработка ошибок внутри хранимой процедуры  [new]
Владислав Колосов
Member

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

raiserror
throw
15 окт 15, 14:01    [18282725]     Ответить | Цитировать Сообщить модератору
 Re: Клиентская транзакция и обработка ошибок внутри хранимой процедуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31910
Владислав Колосов
throw

WinterGraveyard
Сервера, на которых выполняется ХП - 2005-й и 2008-й R2, поэтому при написании ХП ориентировался на возможности 2005-го.
15 окт 15, 14:13    [18282806]     Ответить | Цитировать Сообщить модератору
 Re: Клиентская транзакция и обработка ошибок внутри хранимой процедуры  [new]
Владислав Колосов
Member

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

точно, пропустил мелкий шрифт...
15 окт 15, 14:21    [18282865]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить