Первым делом необходимо уяснить, каким образом транзакции работают внутри БД. Транзакции позволяют вам быть уверенным, что изменения, сделанные в БД в рамках транзакции, будут или все приняты или все отвергнуты. Есть несколько уровней изоляции, которые определяют, могут ли данные, считанные во время работы транзакции, быть изменены в то время, пока не закончила работу эта транзакция. По умолчанию уровень установлен в Read Committed, что говорит о том, что данные могут быть изменены в контексте незавершенной транзакции, но вы не получите данных, которые только частично изменены из другой транзакции. Если же вы хотите убедиться в том, что данные запроса не меняются другой транзакцией до тех пор, пока не завершена ваша транзакция, вам необходимо установить уровень изоляции в Repeatable Read или Serializable. Более подробно об этих уровнях можно прочитать в SQL Books online, также известном как BOL. Каждый индивидуальный запрос, исполняемый SQL Server’ом, автоматически оформляется как транзакция. Допустим, у вас есть команда update, модифицирующая 100 записей в таблице. Если что-то пошло не так на записи номер 99, то все изменения откатываются и, после возникновения ошибки, никаких в таблице модификаций вы не увидите. Если же вы используете ХП для доступа к вашей БД, эти ХП также должны оформлять SQL-команды в транзакции уровня этой ХП. Например, рассмотрим простую ХП:

Эта ХП использует свою собственную транзакцию для команд SELECT и INSERT. SELECT убеждается в том, что передан существующий StatusID (например для того, чтобы убедиться, что не нарушена ссылочная целостность), а INSERT добавляет запись к таблице Orders. Если у вас несколько запросов в ХП выполнены виде транзакции, возможно, вы захотите управлять транзакциями на этом уровне. Откат транзакции в любом месте перед командой COMMIT TRANSACTION предупредит запись изменений в БД.

Вы можете подумать, что необязательно включать оператор SELECT в транзакцию, поскольку он не меняет БД. Во многих ситуациях вы будете не правы. Выполнение подобных запросов похоже на программирование многопоточных приложений. Вы должны предполагать, что между выполнением команд SELECT и INSERT кто-то может модифицировать таблицу OrderStatus и удалить значение, которое вы только что проверяли на существование, в результате команда INSERT не выполнится. Вам часто придётся убеждаться в том, что все запросы, которые вы выполняете, выполняются с одним и тем же состоянием БД и лучший метод убедиться в этом – оформить такие запросы в виде транзакции и при необходимости установить уровень транзакции в Repeatable Read или Serializable.

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

Транзакции в ADO.NET

А что если вам надо выполнить несколько ХП из кода на C# согласно тому же принципу ‘всё или ничего’? Как раз для этих целей ADO.NET содержит класс SqlTransaction. Технология достаточно проста - вызываете BeginTransaction для объекта SqlConnection, чтобы получить новый объект транзакции, если всё Ок - вызываем SqlTransaction.Commit, если не всё так хорошо, как хотелось - SqlTransaction.Rollback.

В общем, это просто, не так ли? Хитрость заключается в том, чтобы правильно обрабатывать исключения в блоке catch и понимать те исключения, которые вызываются. Если вы вызываете ХП, которые не управляют своими транзакциями, не должно быть никаких проблем. Если ошибка возникает где-то во время выполнения вашего кода, то будет вызвано исключение SqlException, которое попадёт в блок catch. Код в этом блоке вызовет Rollback и никакие обновления с начала транзакции не попадут в вашу БД.

Не пугайтесь своих вложенных инстинктов

А что же происходит, если ХП, которые вы вызываете, сами управляют своими транзакциями? Не означают ли эти вызовы COMMIT TRANSACTION внутри ХП того, что изменения будут внесены в БД не смотря на откат в коде на C#? Ответ – нет, и причина такого ответа в том, как выполняются вложенные транзакции на SQL Server’e. Когда вы стартуете транзакцию в своём коде, на самом деле вы стартуете её на сервере, все последующие транзакции являются для неё вложенными, до тех пор, пока кем-то не будет дана команда Commit или Rollback. Этим кем-то может быть ваш код на C#, код в ХП или сам SQL Server, если на нём произойдёт ошибка.
Если ХП сама пытается стартовать транзакцию с помощью BEGIN TRANSACTION, то она просто попадает в область видимости существующей транзакции. Поэтому любой откат, произошедший внутри ХП, откатит и внешнюю транзакцию, стартовавшую в вашем коде, чего на самом деле нам и хотелось.
Единственно, о чём надо упомянуть, так это о том, что в случае отката транзакции вызывается SqlException с таким описанием:

Это исключение вызывается SQL Server’ом, который видит, что при входе в ХП была транзакция, однако при выходе из ХП её не осталось, поскольку был откат. Поскольку такая ситуация обрабатывается как исключительная, то вы её отловите. Никакого вреда повторный Rollback не нанесёт, поэтому просто вызывайте Rollback в обработчике исключений; и не надо его отлавливать по какому-то условию, основывающемуся на информации об исключении.

Что ещё можно сделать, так это убрать информацию ошибки, содержащую количество транзакций, особенно если вы собираетесь где-нибудь вести лог ошибок. По всей вероятности такая информация вызовет у просматривающего недоумение, особенно если он не понимает механизм действия вложенных транзакций. Путём итерации через коллекцию Errors можно получить все ошибки, номер ошибки SQL для счетчика транзакций равен 266, поэтому такую ошибку можно выделить и обработать отдельно – например, просто ни в какой лог её не писать.

В коде для скачивания находится небольшой проект, с которым можно поиграться и в котором продемонстрированы основные моменты работы с БД, вышеизложенные в данной статье.

Код к этой статье можно скачать здесь - скачать.