Les Transactions à l'intérieur de la boucle à l'intérieur de la procédure stockée
Je suis en train de travailler sur une procédure qui permettra de mettre à jour un grand nombre d'éléments sur un serveur distant, à l'aide des enregistrements à partir d'une base de données locale. Voici le pseudo-code.
CREATE PROCEDURE UpdateRemoteServer
pre-processing
get cursor with ID's of records to be updated
while on cursor
process the item
Peu importe combien nous l'optimiser, la routine va prendre un certain temps, donc nous ne voulons pas le tout d'être traitée comme une transaction unique. Les éléments sont marqués, après avoir été traitées, de sorte qu'il devrait être possible de reprendre là où nous nous sommes quittés si le processus est interrompu.
Emballage le contenu de la boucle ("l'élément") dans un begin/commit tran ne fait pas l'affaire... il semble que l'intégralité de la déclaration
EXEC UpdateRemoteServer
est traitée comme une transaction unique. Comment puis-je faire de chaque élément de processus comme un système complet, transaction distincte?
Remarque que j'aimerais pour l'exécution de ces "non-traitées mises à jour", mais cette option n'est disponible (pour autant que je sache) en 2008.
OriginalL'auteur harpo | 2009-10-08
Vous devez vous connecter pour publier un commentaire.
EDIT: comme l'a fait remarquer Remus ci-dessous, les curseurs ne PAS ouvrir une transaction par défaut; ainsi, ce n'est pas la réponse à la question posée par l'OP. Je pense toujours qu'il ya de meilleures options que d'un curseur, mais qui ne répond pas à la question.
Stu
RÉPONSE ORIGINALE À CETTE QUESTION:
Spécifiques symptôme que vous décrivez est dû au fait qu'un curseur ouvre une transaction par défaut, par conséquent, peu importe comment vous l'utilisez, vous allez avoir une longue opération, tant que vous êtes à l'aide d'un curseur (à moins que vous éviter des écluses au total, ce qui est une autre mauvaise idée).
Que d'autres sont soulignant, les curseurs de le SUCER. Vous n'avez pas besoin d'eux pour 99.9999% du temps.
Vraiment vous avez deux options si vous voulez faire cela au niveau base de données avec SQL Server:
Utiliser SSIS pour effectuer l'opération; très rapide, mais peut ne pas être disponible pour vous en votre saveur particulière de SQL Server.
Parce que vous avez affaire avec des serveurs distants, et vous êtes inquiet au sujet de la connectivité, vous pourriez avoir à utiliser un mécanisme de bouclage, afin de l'utiliser bien au contraire, et de commettre des lots à la fois. Bien que TOUT en a beaucoup des mêmes problèmes que le curseur (en boucle suce toujours dans SQL), vous éviter de créer de la transaction externe.
Stu
Je ne suis pas d'accord "avec les curseurs de sucer", mais c'est une autre discussion.
vous avez raison; les curseurs sont un outil spécialisé qui doit être utilisé dans une institution spécialisée circonstance (un peu comme un extracteur de goujon; vous n'en avez pas besoin tous les jours. Si vous le faites, vous faites quelque chose de mal). En disant "ils sucer" est plus facile. 🙂
Les curseurs de ne pas ouvrir de transaction par défaut. Comme tous les autres DML, le curseur de sélection de l'devez exécuter le cadre de l'opération, et, implicitement, les ouvre, mais la transaction n' pas durée de l'ensemble de la durée de vie du curseur. Il y aura une courte opération à chaque extraction, mais elle durera le duraction de l'EXTRACTION.
OriginalL'auteur Stuart Ainsworth
EXEC procédure ne pas créer une transaction. Une épreuve très simple affichera ceci:
@@Trancount à l'intérieur de usp_foo est 0, donc l'instruction EXEC ne pas démarrer une transaction implicite. Si vous avez un démarrage de la transaction lors de la saisie de UpdateRemoteServer cela signifie que quelqu'un a commencé l'opération, je ne peux pas dire qui.
Cela étant dit, l'utilisation de serveurs distants et de DTC pour mettre à jour des éléments va effectuer une assez mauvaise. Est l'autre serveur SQL Server 2005, au moins? Peut-être vous pouvez file d'attente de la demande de mise à jour et l'utilisation messagerie entre le local et le serveur distant et d'avoir le serveur distant effectuer les mises à jour basée sur les informations du message. Il serait effectuer beaucoup mieux parce que les deux serveurs ont uniquement pour traiter les transactions locales, et vous obtenez beaucoup mieux disponibilité en raison du couplage de la file d'attente de messagerie.
Mis à jour
Curseurs fait de ne pas commencer les opérations. La typique du curseur en fonction du traitement par lot est généralement basée sur les curseurs et des lots de mises à jour dans les opérations d'une certaine taille. Cette situation est assez courante pour une nuit d'emplois, car il permet de meilleures performances (vidage du journal de débit suite à l'accroissement de la taille des transactions) et de l'emploi peut être interrompu et repris w/o perdre everithing. Une version simplifiée d'un lot de boucle de traitement est généralement comme ceci:
J'ai oublié l'erreur de manipulation de la partie (commencer/commencer à attraper) et de la fantaisie @@fetch_status contrôles (curseurs statiques n'ont pas réellement besoin d'eux de toute façon). Cette démo montre le code que pendant l'exécution, il y a plusieurs différentes opérations de démarrage (différents Identifiants de transaction). De nombreuses fois des lots aussi déployer des transactions points de sauvegarde à chaque élément traitée de sorte qu'ils peuvent sauter en toute sécurité un élément qui provoque une exception, à l'aide d'un modèle similaire à celui de mon lien, mais cela ne s'applique pas aux transactions distribuées depuis les points d'enregistrement et de DTC ne se mélangent pas.
Juste couru votre test proc, et je suis d'accord; curseurs apparemment il ne maintiennent pas une transaction tout au long de. J'ai même retiré les options spécifiées pour obtenir une dynamique de mise à jour du curseur, et les transactions encore changé. Je vais annuler ma réponse (si ce n'est ma position sur l'utilisation de curseur).
Remus, corrigez-moi si je me trompe, mais une fois que vous commencer à modifier les données entre les serveurs (via un serveur lié) n'a pas de SQL Server lancer automatiquement un implicite distributed transaction, de sorte que si le changement de système à distance échoue, l'échec peut être correctement envoyé en retour à l'appel de SQL Server?
Distance mises à jour nécessitent des transactions distribuées, et rehausser toute transaction existante, y compris la transaction implicite commencé par tout DML, une transaction distribuée. Je ne suis pas sûr que vous suivez, pourquoi a-t-il une différence? Le point que j'essayais de faire dans mon post, c'est que il y a seulement deux façons de démarrer une longue transaction (dernier départ instruction de la durée): 1. explicite BEGIN TRANSACTION ou 2. avoir MIS IMPLICIT_TRANSACTIONS SUR (le plus tard assez bien les changements de tout à propos de mon post).
OriginalL'auteur Remus Rusanu
Sont yo l'exécution de ce qu'à partir de sql server, ou à partir d'une application? si oui, obtenir la liste à être traitées, puis la boucle dans l'application à traiter uniquement pour les sous-ensembles nécessaires.
La transaction devrait être géré par votre application, et ne doit verrouiller les éléments en cours de mise à jour/pages les éléments sont en.
Est-ce la poule toujours traité comme une seule et même opération, si vous avez été de ne pas exécuter à partir d'un sp mais plutôt tout simplement tsql? avaoid le ps, et d'utiliser les ampoules, avec les transactions liées à un tsql session?
Nous pensent de même... le problème est que le processus doit être automatisées, de sorte qu'il doit être exécutable à partir d'une seule commande.
Prochaine question. Automatisé comment? à l'aide?
Il devra être appelés à partir .NET ou lancé par le SQL admin. Pour ce dernier cas, un script comme vous l'avez décrit serait de travailler. Je suppose que .NET pouvez exécuter la "plaine tsql" par le chargement d'un fichier de script... mais ne serait-il pas alors être traitée comme une transaction?
OriginalL'auteur Adriaan Stander
Ne JAMAIS traiter un élément à la fois dans une boucle lorsque vous faites le travail transactionnel. Vous pouvez faire une boucle à travers les documents des groupes de traitement, mais jamais, jamais, jamais faire un enregistrement à la fois. Ne basées sur des inserts au lieu et votre performance va changer à partir de quelques heures à quelques minutes ou même quelques secondes. Si vous utilisez un curseur à insérer, mettre à jour ou supprimer et ce n'est pas de la manipulation d'au moins 1000 rowa dans chaque état (pas un seul à la fois) que vous êtes de faire la mauvaise chose. Les curseurs sont une très mauvaise pratique pour telle chose.
OriginalL'auteur HLGEM
Juste une idée ..
J'espère que ce sera la fin de la transaction.
Puis d'écrire un wrapper qui appelle la procédure tant qu'il y a plus de travail à faire (soit utiliser un simple comptage(..) pour voir si il y a des articles ou d'avoir la procédure de retourner true pour indiquer qu'il n'y a plus de travail à faire.
Ne sais pas si cela fonctionne, mais peut-être que l'idée est utile.
OriginalL'auteur Thorsten