Transactions within loop within stored procedure

Question

I'm working on a procedure that will update a large number of items on a remote server, using records from a local database. Here's the pseudocode.

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

No matter how much we optimize it, the routine is going to take a while, so we don't want the whole thing to be processed as a single transaction. The items are flagged after being processed, so it should be possible to pick up where we left off if the process is interrupted.

Wrapping the contents of the loop ("process the item") in a begin/commit tran does not do the trick... it seems that the whole statement

EXEC UpdateRemoteServer

is treated as a single transaction. How can I make each item process as a complete, separate transaction?

Note that I would love to run these as "non-transacted updates", but that option is only available (so far as I know) in 2008.

Answer 1

EXEC procedure does not create a transaction. A very simple test will show this:

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

exec usp_foo;

The @@trancount inside usp_foo is 0, so the EXEC statement does not start an implicit transaction. If you have a transaction started when entering UpdateRemoteServer it means somebody started that transaction, I can't say who.

That being said, using remote servers and DTC to update items is going to perform quite bad. Is the other server also SQL Server 2005 at least? Maybe you can queue the requests to update and use messaging between the local and remote server and have the remote server perform the updates based on the info from the message. It would perform significantly better because both servers only have to deal with local transactions, and you get much better availability due to the loose coupling of queued messaging.

Updated

Cursors actually don't start transactions. The typical cursor based batch processing is usually based on cursors and batches updates into transactions of a certain size. This is fairly common for overnight jobs, as it allows for better performance (log flush throughput due to larger transaction size) and jobs can be interrupted and resumed w/o losing everithing. A simplified version of a batch processing loop is typically like this:

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;

I ommitted the error handling part (begin try/begin catch) and the fancy @@fetch_status checks (static cursors actually don't need them anyway). This demo code shows that during the run there are several different transactions started (different transaction IDs). Many times batches also deploy transaction savepoints at each item processed so they can skip safely an item that causes an exception, using a pattern similar to the one in my link, but this does not apply to distributed transactions since savepoints and DTC don't mix.