Транзакции внутри цикла внутри хранимой процедуры

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

CREATE PROCEDURE UpdateRemoteServer
    pre-processing
    get cursor with ID's of records to be updated
    while on cursor
        process the item

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

Обертывание содержимого цикла ("обработать элемент") в начале / коммите транса не помогает... кажется, что весь оператор

EXEC UpdateRemoteServer

рассматривается как одна транзакция Как я могу сделать каждый процесс предмета полной завершенной транзакцией?

Обратите внимание, что я хотел бы запускать их как "нетранзакционные обновления", но эта опция доступна (насколько я знаю) только в 2008 году.

Источник

5 ответов

Решение

РЕДАКТИРОВАТЬ: как указано Remus ниже, курсоры по умолчанию НЕ открывают транзакцию; таким образом, это не ответ на вопрос, поставленный ФП. Я все еще думаю, что есть лучшие варианты, чем курсор, но это не отвечает на вопрос.

Stu

ОРИГИНАЛЬНЫЙ ОТВЕТ:

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

Как указывают другие, курсоры сосут. Вам не нужны они в 99,9999% случаев.

У вас действительно есть два варианта, если вы хотите сделать это на уровне базы данных с SQL Server:

  1. Используйте SSIS для выполнения вашей операции; очень быстро, но может быть недоступно вам в вашем конкретном варианте SQL Server.

  2. Поскольку вы имеете дело с удаленными серверами и беспокоитесь о возможности подключения, вам, возможно, придется использовать механизм зацикливания, поэтому используйте вместо этого WHILE и фиксируйте пакеты за раз. Хотя WHILE имеет много тех же проблем, что и курсор (циклы по-прежнему отстой в SQL), вы избегаете создания внешней транзакции.

Stu

Источник

Процедура EXEC не создает транзакцию. Очень простой тест покажет это:

create procedure usp_foo
as
begin
  select @@trancount;
end
go

exec usp_foo;

@@trancount внутри usp_foo равно 0, поэтому оператор EXEC не запускает неявную транзакцию. Если у вас запущена транзакция при вводе UpdateRemoteServer, это означает, что кто-то запустил эту транзакцию, я не могу сказать, кто.

Тем не менее, использование удаленных серверов и DTC для обновления элементов будет работать довольно плохо. Другой сервер также SQL Server 2005 по крайней мере? Возможно, вы можете поставить в очередь запросы на обновление и использовать обмен сообщениями между локальным и удаленным сервером и заставить удаленный сервер выполнять обновления на основе информации из сообщения. Он будет работать значительно лучше, потому что оба сервера будут иметь дело только с локальными транзакциями, и вы получите гораздо лучшую доступность из-за слабой связи в очереди сообщений.

обновленный

Курсоры фактически не запускают транзакции. Типичная пакетная обработка на основе курсора обычно основана на курсорах и пакетных обновлениях в транзакциях определенного размера. Это довольно часто встречается для ночных заданий, так как обеспечивает лучшую производительность (пропускную способность журнала при увеличении размера транзакции), а задания могут прерываться и возобновляться без потери вечности. Упрощенная версия цикла пакетной обработки обычно выглядит следующим образом:

create procedure usp_UpdateRemoteServer
as
begin
  declare @id int, @batch int;
  set nocount on;
  set @batch = 0;

  declare crsFoo cursor 
    forward_only static read_only 
    for 
    select object_id 
    from sys.objects;

  open crsFoo;

  begin transaction
  fetch next from crsFoo into @id ;
  while @@fetch_status = 0
  begin

    -- process here

    declare @transactionId int;
    SELECT @transactionId = transaction_id 
      FROM sys.dm_tran_current_transaction;
    print @transactionId;

    set @batch = @batch + 1
    if @batch > 10
    begin
      commit;
      print @@trancount;
      set @batch = 0;
      begin transaction;
    end
    fetch next from crsFoo into @id ;
  end
  commit;
  close crsFoo;

  deallocate crsFoo;
end
go

exec usp_UpdateRemoteServer;

Я пропустил часть обработки ошибок (begin try / begin catch) и причудливые проверки @@fetch_status (статические курсоры на самом деле не нужны). Этот демонстрационный код показывает, что во время выполнения запущено несколько разных транзакций (разные идентификаторы транзакций). Во многих случаях пакеты также развертывают точки сохранения транзакций для каждого обработанного элемента, чтобы они могли безопасно пропустить элемент, вызывающий исключение, используя шаблон, аналогичный шаблону в моей ссылке, но это не относится к распределенным транзакциям, поскольку точки сохранения и коды DTC не смешиваются,

Источник

Выполняете ли вы это только с сервера SQL или из приложения? если это так, получите список для обработки, затем выполните цикл в приложении, чтобы обрабатывать только те подмножества, которые требуются.

Тогда транзакция должна обрабатываться вашим приложением и должна блокировать только обновляемые элементы / страницы, в которых они находятся.

Источник

НИКОГДА не обрабатывайте один элемент за раз в цикле, когда вы выполняете транзакционную работу. Вы можете просматривать их группы, но никогда не делать одну запись за раз. Вместо этого делайте вставки на основе набора, и ваша производительность будет меняться от часов до минут или даже секунд. Если вы используете курсор для вставки обновления или удаления, и он не обрабатывает как минимум 1000 строк в каждом операторе (а не по одному), вы делаете неправильную вещь. Курсоры - крайне плохая практика для такой вещи.

Источник

Просто идея..

  • Обрабатывать только несколько элементов при вызове процедуры (например, обрабатывать ТОП-10 элементов)
  • Обработать те

Надеюсь, это будет конец сделки.

Затем напишите обертку, которая вызывает процедуру, если есть над чем поработать (либо используйте простой счетчик (..), чтобы увидеть, есть ли элементы, либо верните процедуре значение true, указывающее, что еще есть над чем работать.

Не знаю, работает ли это, но, возможно, идея полезна.

Источник
Другие вопросы по тегам