Transactions dans la boucle dans la procédure stockée

EDIT: comme indiqué par Remus ci-dessous, les sliders n'ouvrent PAS une transaction par défaut; ainsi, ce n'est pas la réponse à la question posée par le PO. Je pense toujours qu'il existe de meilleures options qu'un slider, mais cela ne répond pas à la question.

Stu

RÉPONSE ORIGINALE

Le symptôme spécifique que vous décrivez est dû au fait qu'un slider ouvre une transaction par défaut, donc peu importe comment vous le travaillez, vous allez avoir une transaction de longue durée tant que vous utilisez un slider (sauf si vous évitez les verrous tout à fait, ce qui est une autre mauvaise idée).

Comme d'autres le soulignent, les sliders SUCK. Vous n'en avez pas besoin pendant 99,9999% du time.

Vous avez vraiment deux options si vous voulez faire cela au niveau de la database avec SQL Server:

1. Utilisez SSIS pour effectuer votre opération; très rapide, mais peut ne pas être disponible dans votre propre saveur de SQL Server.

2. Étant donné que vous avez affaire à des servers distants et que vous vous inquiétez de la connectivité, vous devrez peut-être utiliser un mécanisme de bouclage. Utilisez plutôt WHILE et validez les lots à la fois. Bien que WHILE ait de nombreux problèmes identiques à ceux d'un slider (la création de loops aspire toujours dans SQL), vous évitez de créer la transaction externe.

Stu

La procédure EXEC ne crée pas de transaction. Un test très simple montrera ceci:

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

Le @@ trancount à l'intérieur de usp_foo est 0, donc l'instruction EXEC ne démarre pas une transaction implicite. Si vous avez commencé une transaction en entrant UpdateRemoteServer cela signifie que quelqu'un a commencé cette transaction, je ne peux pas dire qui.

Cela étant dit, l'utilisation de servers distants et de DTC pour mettre à jour les éléments va avoir de très mauvais résultats. L'autre server est-il également SQL Server 2005 au less? Vous pouvez peut-être mettre en queue les requests de mise à jour et d'utilisation de la messagerie entre le server local et le server distant et requestr au server distant d'effectuer les mises à jour en fonction des informations du message. Cela fonctionnerait nettement mieux car les deux servers ne doivent traiter que des transactions locales et vous bénéficiez d'une meilleure disponibilité en raison du couplage lâche de la messagerie en queue.

Actualisé

Les sliders ne démarrent pas réellement les transactions. Le traitement par lots basé sur un slider typique est généralement basé sur des sliders et des mises à jour de lots dans des transactions d'une certaine taille. Ceci est assez commun pour les travaux de nuit, car il permet de meilleures performances (stream de vidage de journal en raison de la taille plus grande des transactions) et les travaux peuvent être interrompus et repris sans perdre de time. Une version simplifiée d'une boucle de traitement par lots est typiquement comme ceci:

 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; 

J'ai omis la partie de gestion des erreurs (begin try / begin catch) et les vérifications de fantaisie @@ fetch_status (les sliders statiques n'en ont pas besoin de toute façon). Ce code de démonstration montre qu'au cours de l'exécution, plusieurs transactions différentes ont été lancées (différents ID de transaction). Plusieurs fois, les lots déploient également des points de sauvegarde sur chaque élément traité afin qu'ils puissent ignorer un élément provoquant une exception, en utilisant un model similaire à celui de mon lien, mais cela ne s'applique pas aux transactions dissortingbuées. .

Est-ce que vous exécutez ceci uniquement depuis le server sql ou depuis une application? Si c'est le cas, obtenez la list à traiter, puis faites une boucle dans l'application pour ne traiter que les sous-sets requirejs.

Ensuite, la transaction devrait être traitée par votre application, et ne devrait verrouiller que les éléments mis à jour / pages les articles sont.

NE JAMAIS traiter un élément à la fois dans une boucle lorsque vous effectuez un travail transactionnel. Vous pouvez faire défiler les groupes de traitement des loggings, mais ne jamais faire un seul logging à la fois. Faites plutôt des inserts basés sur des sets et votre performance passera de quelques heures à quelques minutes, voire quelques secondes. Si vous utilisez un slider pour insert la mise à jour ou supprimer et qu'il ne gère pas au less 1000 rowa dans chaque instruction (pas une à la fois), vous faites la mauvaise chose. Les sliders sont une pratique extrêmement pauvre pour une telle chose.

Juste une idée ..

• Ne traiter que quelques éléments lors de l'appel de la procédure (par exemple, ne traiter que les TOP 10)
• Traiter les

Espérons que ce sera la fin de la transaction.

Ensuite, écrivez un wrapper qui appelle la procédure tant qu'il y a plus de travail à faire (soit utiliser un simple count (..) pour voir s'il y a des items ou avoir la procédure return true indiquant qu'il y a plus de travail à faire.

Je ne sais pas si cela fonctionne, mais peut-être que l'idée est utile.