Наличие ТРАНЗАКЦИИ во всех запросах

Я не знаю каких-либо преимуществ не только от использования транзакций автоматической фиксации для этих операторов.

Возможные недостатки использования явных транзакций повсюду могут заключаться в том, что это просто добавляет беспорядок в код и, таким образом, затрудняет определение того, когда явная транзакция используется для обеспечения корректности нескольких операторов.

Кроме того, увеличивается риск того, что транзакция останется открытой с удерживающими блокировками, если не будут приняты меры (например, с помощью SET XACT_ABORT ON ).

Также имеется , незначительное влияние на производительность, как показано в ответе @ 8kb . Это иллюстрирует другой способ использования профилировщика Visual Studio.

Настройка

(Тестирование с пустой таблицей)

CREATE TABLE T (X INT)

Явное

SET NOCOUNT ON

DECLARE @X INT

WHILE ( 1 = 1 )
  BEGIN
      BEGIN TRAN

      SELECT @X = X
      FROM   T

      COMMIT
  END

Автоматическое подтверждение

SET NOCOUNT ON

DECLARE @X INT

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

Оба они в конечном итоге проводят время в CMsqlXactImp :: Begin и CMsqlXactImp: : Commit , но в случае явных транзакций он тратит значительно большую часть времени выполнения на эти методы и, следовательно, меньше времени на полезную работу.

+--------------------------------+----------+----------+
|                                | 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%   |
+--------------------------------+----------+----------+

Единственная возможная причина, которую я мог видеть для этого, - это то, что у вас есть возможность отката транзакции по другой причине, кроме сбоя SQL.

Однако, если код буквально

begin transaction
statement
commit

, то я не вижу абсолютно никаких причин для использования явной транзакции, и, вероятно, это делается, потому что так всегда делалось .

Один плюс - вы можете добавить еще один INSERT (например), и это уже безопасно.

Но опять же, возникает проблема вложенных транзакций, если хранимая процедура вызывает другую. Внутренний откат вызовет ошибку 266.

Если каждый вызов - это простой CRUD без вложенности, то это бессмысленно: но если у вас вложенность или несколько записей до TXN, то хорошо иметь последовательный шаблон.

Это совершенно излишне, поскольку каждый оператор SQL выполняется атомарно, т.е. так, как если бы он уже выполнялся в собственной транзакции. На самом деле, открытие ненужных транзакций может привести к увеличению блокировок, даже к тупиковым ситуациям. Забыв совместить COMMITы с BEGINами, можно оставить транзакцию открытой до тех пор, пока открыто соединение с базой данных, и помешать другим транзакциям в том же соединении.

Такое кодирование почти наверняка означает, что тот, кто писал код, не очень опытен в программировании баз данных, и это верный признак того, что могут быть и другие проблемы.

Вы упомянули, что будете оптимизировать это устаревшее приложение.

Одна из первых и самых простых вещей, которую вы можете сделать для повышения производительности, это удалить все BEGIN TRAN и COMMIT TRAN для хранимых процедур, которые выполняют только SELECT.

Вот простой тест для демонстрации:

/* 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

Вы можете видеть, что транзакции связаны с определенными накладными расходами.

Примечание: это при условии, что ваши хранимые процедуры не используют никаких специальных уровней изоляции или подсказок блокировки (для чего-то вроде обработки пессимистического параллелизма). В таком случае, очевидно, вы захотите сохранить их.

Поэтому, отвечая на вопрос, я бы оставил только те транзакции, в которых вы действительно пытаетесь сохранить целостность модификаций данных в случае ошибки в коде, SQL Server или аппаратном обеспечении.

Я могу только сказать, что помещать подобный блок транзакции в каждую хранимую процедуру может быть делом рук новичка.

Транзакция должна быть размещена только в блоке, который имеет более одного оператора insert/update, кроме этого, нет необходимости размещать блок транзакции в хранимой процедуре.

При выполнении нескольких операций вставки/обновления/удаления лучше иметь транзакцию, чтобы обеспечить атомарность операции и гарантировать, что все задачи операции выполнены или нет.

При выполнении одиночной вставки/обновления/удаления все зависит от того, какую операцию (с точки зрения бизнес-уровня) вы выполняете и насколько она важна. Если вы выполняете некоторые вычисления перед одиночной вставкой/обновлением/удалением, то лучше использовать транзакцию, возможно, некоторые данные изменились после того, как вы получили данные для вставки/обновления/удаления.

Синтаксис BEGIN TRANSACTION / COMMIT не следует использовать по умолчанию во всех хранимых процедурах, если только вы не пытаетесь охватить следующие сценарии:

1. Вы включаете параметр WITH MARK, потому что хотите поддерживать восстановление базы данных из резервной копии на определенный момент времени.

2. Вы собираетесь перенести код с SQL Server на другую платформу баз данных, например Oracle. Oracle не фиксирует транзакции по умолчанию.