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

Vous avez mentionné que vous serez optimisation de cette ancienne application.

L'une des premières choses à faire, et la plus simple, pour améliorer les performances est de supprimer tous les BEGIN TRAN et COMMIT TRAN pour les procédures stockées qui ne font que des SELECT.

Voici un test simple pour le démontrer :

/* Compare basic SELECT times with and without a transaction */

DECLARE @date DATETIME2
DECLARE @noTran INT
DECLARE @withTran INT

SET @noTran = 0
SET @withTran = 0

DECLARE @t TABLE (ColA INT)
INSERT @t VALUES (1)

DECLARE 
  @count INT,
  @value INT

SET @count = 1

WHILE @count < 1000000
BEGIN

  SET @date = GETDATE()
  SELECT @value = ColA FROM @t WHERE ColA = 1
  SET @noTran = @noTran + DATEDIFF(MICROSECOND, @date, GETDATE())

  SET @date = GETDATE()
  BEGIN TRAN
  SELECT @value = ColA FROM @t WHERE ColA = 1
  COMMIT TRAN
  SET @withTran = @withTran + DATEDIFF(MICROSECOND, @date, GETDATE())

  SET @count = @count + 1
END

SELECT 
  @noTran / 1000000. AS Seconds_NoTransaction, 
  @withTran / 1000000. AS Seconds_WithTransaction

/** Results **/

Seconds_NoTransaction                   Seconds_WithTransaction
--------------------------------------- ---------------------------------------
14.23600000                             18.08300000

Vous pouvez voir qu'il y a des frais généraux associés aux transactions.

Remarque : cela suppose que ces procédures stockées n'utilisent pas de niveaux d'isolation spéciaux ou d'indications de verrouillage (pour quelque chose comme la gestion d'une concurrence pessimiste). Dans ce cas, il est évident que vous souhaitez les conserver.

Donc, pour répondre à la question, je ne laisserais dans les transactions que celles où vous essayez réellement de préserver l'intégrité des modifications de données en cas d'erreur dans le code, dans SQL Server ou dans le système d'exploitation.

Answer 2

Je peux seulement dire que placer un bloc de transaction comme celui-ci dans chaque procédure stockée peut être le travail d'un novice.

Une transaction ne doit être placée que dans un bloc comportant plus d'une instruction d'insertion/mise à jour. En dehors de cela, il n'est pas nécessaire de placer un bloc de transaction dans la procédure stockée.

Answer 3

La syntaxe BEGIN TRANSACTION / COMMIT ne devrait pas être utilisée par défaut dans toutes les procédures stockées, sauf si vous essayez de couvrir les scénarios suivants :

1. Vous incluez l'option AVEC MARQUE parce que vous voulez prendre en charge la restauration de la base de données à partir d'une sauvegarde à un moment précis dans le temps.

2. Vous avez l'intention de porter le code de SQL Server vers une autre plateforme de base de données comme Oracle. Oracle ne commet pas de transactions par défaut.