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

Откуда:
Сообщений: 416
Добрый день, коллеги!

Есть цикл - цикл выполняет скажем 5 последовательных модифицирующих действий(добавляет новые объекты и местами их модифицирует). Соответственно каждая итерация цикла должна иметь возможность откатиться(итерация заложена в try/catch). Вопрос в том, что эффективнее использовать в такой ситуации:

1. каждый раз создавать новую транзакцию и по завершению итерации цикла делать её commit/rollback
2. использовать одну транзакцию и делать точки сохранения в каждой итерации, соответственно по завершению итерации делать commit/rollback до последней точки сохранения.

Стоит отметить, что сам цикл так-же заложен в транзакцию( есть определенные действия до него и после него), т.е. если создавать каждый раз новую транзакцию, то она будет как дочерняя от основной. А с точками сохранения будет всего одна большая транзакция.
1 апр 13, 11:25    [14120477]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Ennor Tiegael
Member

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

Вложенных транзакций не существует, это фикция. Так что, если вам нужно коммитить изменения из предыдущих шагов, независимо от успешности последующих, то только сейвпойнты.
1 апр 13, 11:28    [14120505]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Exproment
Member

Откуда:
Сообщений: 416
Ennor Tiegael, Не могли бы вы пояснить. Если я создаю именнованную транзакцию и затем только её и откатываю. Или sql server воспримет её как save point ?

+ слышал уже из многих источников, что глубокая вложенность даже не именнованных транзакций плохо сказывается на производительности, ввиду чего в дочерних процедурах лучше применять счетчики транзакций.
1 апр 13, 11:41    [14120601]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Exproment
Если я создаю именнованную транзакцию и затем только её и откатываю. Или sql server воспримет её как save point ?

BOL, BEGIN TRANSACTION
transaction_name
Is the name assigned to the transaction. transaction_name must conform to the rules for identifiers, but identifiers longer than 32 characters are not allowed. Use transaction names only on the outermost pair of nested BEGIN...COMMIT or BEGIN...ROLLBACK statements.

Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error. None of the statements executed before the rollback is, in fact, rolled back at the time this error occurs. The statements are rolled back only when the outer transaction is rolled back.
1 апр 13, 11:45    [14120633]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Exproment
Member

Откуда:
Сообщений: 416
Гость333, понял, виноват! :) Спасибо!
1 апр 13, 12:01    [14120775]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Crimean
Member

Откуда:
Сообщений: 13148
осторожно с try / catch. можете попасть в "нон-комитабл" состояние транзакции. и придется откатить все.
1 апр 13, 12:54    [14121116]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Exproment
Member

Откуда:
Сообщений: 416
Crimean, прошу прощения, можете дать любой линк на эту тему ? Что-то не могу найти упоминаний на просторах всевышнего:)
1 апр 13, 13:00    [14121154]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Exproment
можете дать любой линк на эту тему ?

Тема проста: в блоке CATCH нужно проверять значение функции XACT_STATE(). Откатиться до точки сохранения можно лишь в том случае, если это значение равно 1. Если значение равно -1, то можно сделать лишь ROLLBACK всей транзакции. При этом нужно следить за тем, чтобы транзакция откатывалась в той же процедуре, где они и открывалась, иначе получите Msg 266 "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = %ld, current count = %ld."

Линк — смотреть документацию по XACT_STATE, наверное.
1 апр 13, 13:10    [14121210]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Exproment
Member

Откуда:
Сообщений: 416
спасибо! Очень дельные замечания. Вот только теперь я не понимаю как могу гарантированно продолжить выполнение транзакции ? Раньше у меня в цикле создавалась новая транзакция, и выше этого уровня транзакции не было. И в результате возникновения ошибок я текущую просто убивал и небыло проблем. Теперь-же, когда пришлось обернуть сам цикл в транзакцию ума не приложу как можно гарантированно продолжить выполнение. Хотябы с той целью, чтобы собрать ошибки по каждой из итерации а не только по первой. Это вообще возможно ?
1 апр 13, 13:43    [14121377]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Ennor Tiegael
Member

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

Ошибки на каждом этапе можно собирать в табличную переменную - они внетранзакционны.
1 апр 13, 14:22    [14121590]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Ennor Tiegael,

Если на некотором шаге цикла получилось состояние XACT_STATE = -1, то следующих шагов цикла уже не будет, т.к. insert, update и прочий delete будет невозможен.
1 апр 13, 14:30    [14121631]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
invm
Member

Откуда: Москва
Сообщений: 9402
Exproment
Теперь-же, когда пришлось обернуть сам цикл в транзакцию ума не приложу как можно гарантированно продолжить выполнение
Гарантированно -- никак. Или форсмажор (например, транзакция выбрана жертвой дедлока) не учитываем?

Про коллекционирование ошибок уже писали. Еще, если позволяет версия, можете вынести итерации на loopback linked server со сброшенной опцией "remote proc transaction promotion". Но это костыль.

Вообще говоря, такого рода требования, в большинстве случаев говорят об ошибках проектирования.
1 апр 13, 14:36    [14121657]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Ennor Tiegael
Member

Откуда:
Сообщений: 3274
Гость333,

Я понял так, что у ТС несколько логически независимых транзакций, и он готов обломиться с произвольным их количеством, но хочет получить все ошибки, а не только последнюю. В этом случае несколько отдельных транзакций + сбор ошибок в табличную переменную идеально решают проблему.

Или вы имеете в виду, что XACT_STATE() ломает батч? Дык вроде не должен.
1 апр 13, 15:05    [14121861]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
invm
Member

Откуда: Москва
Сообщений: 9402
Гость333
Ennor Tiegael,

Если на некотором шаге цикла получилось состояние XACT_STATE = -1, то следующих шагов цикла уже не будет, т.к. insert, update и прочий delete будет невозможен.
Шаги будут, только ошибка будет все время 3930. И данные в табличных переменных можно будет модифицировать, ибо они живут в других транзакциях.
1 апр 13, 15:21    [14121943]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Ennor Tiegael,

Я говорил про то, что если снаружи есть одна "большая" транзакция, то гарантированно выполнить все шаги цикла не получится:
BEGIN TRANSACTION;

-- Шаг 1
BEGIN TRY
  SAVE TRANSACTION step1;
  INSERT INTO tab1 ...
  UPDATE tab1 ...
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION step1;
END CATCH;

-- Шаг 2
BEGIN TRY
  SAVE TRANSACTION step2;
  INSERT INTO tab2 ...
  UPDATE tab2 ...
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION step2;
END CATCH;

COMMIT TRANSACTION;

Если, например, на шаге 1 транзакция свалилась в uncommitable state (XACT_STATE = -1), то не сработает ни откат к точке сохранения, ни INSERT/UPDATE на втором шаге, т.к. они свалятся по Msg 3930 "The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction."

ТС хочет такого:
Exproment
Теперь-же, когда пришлось обернуть сам цикл в транзакцию ума не приложу как можно гарантированно продолжить выполнение

Так что, как уже сказали, гарантированно — никак.
1 апр 13, 15:27    [14121976]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
invm
Шаги будут, только ошибка будет все время 3930.

Согласен, с двумя "но":
1) если над циклом нет внешнего TRY/CATCH;
2) это будут уже неполноценные шаги, от которых ценность информации об ошибках сомнительна :-)
1 апр 13, 15:30    [14121998]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
invm
Member

Откуда: Москва
Сообщений: 9402
Гость333
1) если над циклом нет внешнего TRY/CATCH;
Вот тут такого не происходит:
use tempdb;
go

create table dbo.TestTable (d date);
declare @l table (msg varchar(255), ec int, xst int);
declare @c int = 3;

begin try;

begin tran;

while @c > 0
 begin try
  insert into dbo.TestTable
  values (cast('20122501' as date));

  select @c = 0;
 end try
 begin catch
  insert into @l
  select error_message(), error_number(), xact_state();

  select @c -= 1;
 end catch;

if @@trancount > 0
 rollback;

end try
begin catch
end catch;
 
select * from @l;
go

drop table dbo.TestTable;
go

Гость333
2) это будут уже неполноценные шаги, от которых ценность информации об ошибках сомнительна :-)
А вдруг у ТСа при чтении генерируются пользовательские ошибки? :)
1 апр 13, 15:39    [14122076]     Ответить | Цитировать Сообщить модератору
 Re: Транзакции. Точки сохранения или завершение/открытие. Производительность.  [new]
Exproment
Member

Откуда:
Сообщений: 416
Рассматриваемый механизм есть асинхронный робот, точнее асинхронная процедура и гарантировать его выполнение - вполне понятное желание, пусть там хоть какие ошибки возникнут - я хочу для каждой итерации эти ошибки увидеть, независимо от того, я о них сообщил или они системные. В качестве временного решения вернул старый вариант без родительской транзакции.

На днях планирую попробовать выполнить тоже самое через ETL пакет. Еще не анализировал но есть надежда, что их средствами такая задача адекватно решаема. Не охота оставлять потенциально опасный код.
1 апр 13, 16:08    [14122342]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить