Avoir TRANSACTION dans toutes les requêtes

Question

Pensez-vous que le fait de toujours avoir une transaction autour des instructions SQL dans une procédure stockée est une bonne pratique ? Je suis sur le point d'optimiser cette ancienne application dans mon entreprise, et j'ai constaté que chaque procédure stockée a une transaction. BEGIN TRANSACTION . Même une procédure avec une seule instruction de sélection ou de mise à jour en possède une. J'ai pensé que ce serait bien d'avoir BEGIN TRANSACTION si vous effectuez plusieurs actions, mais pas une seule. Je peux me tromper, c'est pourquoi j'ai besoin que quelqu'un d'autre me conseille. Merci pour votre temps.

Answer 1

C'est totalement inutile car chaque instruction SQL s'exécute de manière atomique, c'est-à-dire comme si elle était déjà en cours d'exécution dans sa propre transaction. En fait, l'ouverture de transactions inutiles peut entraîner un verrouillage accru, voire des blocages. Oublier de faire correspondre les COMMIT avec les BEGIN peut laisser une transaction ouverte aussi longtemps que la connexion à la base de données est ouverte et interférer avec d'autres transactions dans la même connexion.

Un tel codage signifie presque certainement que la personne qui a écrit le code n'a pas beaucoup d'expérience dans la programmation de bases de données et est un signe certain qu'il peut y avoir d'autres problèmes.

Answer 2

La seule raison possible que je vois pour cela est la possibilité de devoir annuler la transaction pour une raison autre qu'un échec SQL.

Cependant, si le code est littéralement

begin transaction
statement
commit

Dans ce cas, je ne vois absolument aucune raison d'utiliser une transaction explicite, et c'est probablement le cas parce que ça a toujours été fait de cette façon .

Answer 3

Je ne vois pas l'intérêt de ne pas utiliser les transactions auto commit pour ces déclarations.

Les inconvénients possibles de l'utilisation de transactions explicites partout pourraient être que cela ne fait qu'ajouter du désordre au code et qu'il est donc moins facile de voir quand une transaction explicite est utilisée pour assurer la correction de plusieurs déclarations.

De plus, cela augmente le risque qu'une transaction soit laissée ouverte en conservant des verrous, à moins que des précautions ne soient prises (par exemple avec METTRE XACT_ABORT ON ).

Il y a aussi est une incidence mineure sur les performances, comme le montre l'exemple suivant La réponse de @8kb . Ceci l'illustre d'une autre manière en utilisant le profileur de Visual Studio.

Configuration

(Test contre une table vide)

CREATE TABLE T (X INT)

Explicite

SET NOCOUNT ON

DECLARE @X INT

WHILE ( 1 = 1 )
  BEGIN
      BEGIN TRAN

      SELECT @X = X
      FROM   T

      COMMIT
  END

Auto Commit

SET NOCOUNT ON

DECLARE @X INT

WHILE ( 1 = 1 )
  BEGIN
      SELECT @X = X
      FROM   T
  END 

Tous deux finissent par passer du temps dans CMsqlXactImp::Begin y CMsqlXactImp::Commit mais dans le cas des transactions explicites, il passe une proportion nettement plus importante du temps d'exécution dans ces méthodes et donc moins de temps à faire un travail utile.

+--------------------------------+----------+----------+
|                                | Auto     | Explicit |
+--------------------------------+----------+----------+
| CXStmtQuery::ErsqExecuteQuery  | 35.16%   | 25.06%   |
| CXStmtQuery::XretSchemaChanged | 20.71%   | 14.89%   |
| CMsqlXactImp::Begin            | 5.06%    | 13%      |
| CMsqlXactImp::Commit           | 12.41%   | 24.03%   |
+--------------------------------+----------+----------+

Answer 4

Lorsque l'on effectue plusieurs insertions/mises à jour/suppressions, il est préférable d'avoir une transaction pour assurer l'atomicité de l'opération et garantir que toutes les tâches de l'opération sont exécutées ou non.

Pour une déclaration unique d'insertion/mise à jour/suppression, cela dépend du type d'opération (du point de vue de la couche métier) que vous effectuez et de son importance. Si vous effectuez un calcul avant l'insertion/mise à jour/suppression unique, il est préférable d'utiliser la transaction, car il se peut que certaines données aient changé après avoir été récupérées pour l'insertion/mise à jour/suppression.

Answer 5

Un point positif est que vous pouvez ajouter un autre INSERT (par exemple) et c'est déjà sûr.

Mais il y a aussi le problème des transactions imbriquées si une procédure stockée en appelle une autre. Un rollback interne provoquera l'erreur 266.

Si chaque appel est un simple CRUD sans imbrication, c'est inutile : mais si vous imbriquez ou avez plusieurs écritures avant TXN, il est bon d'avoir un modèle cohérent.