Как SQL Server рассматривает заявления внутри хранимых процедур в отношении транзакций?

Скажем, у меня есть хранимая процедура, состоящая из нескольких отдельных операторов SELECT, INSERT, UPDATE и DELETE. Нет явной логики BEGIN TRANS/COMMIT TRANS/ROLLBACK TRANS.

Как SQL Server будет обрабатывать эту хранимую процедуру по-разному? Будет ли неявное соединение для каждого утверждения? Или будет одна транзакция для хранимой процедуры?

Кроме того, как я мог найти это самостоятельно самостоятельно, используя T-SQL и/или SQL Server Management Studio?

Спасибо!

Ответ 1

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

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

Однако, если вы до того, как вы вызовете хранимую процедуру, вы выдаете BEGIN TRANSACTION, то все операторы сгруппированы в транзакции и могут либо быть COMMITted, либо ROLLBACKed после выполнения хранимой процедуры.

Внутри хранимой процедуры вы можете определить, работаете ли вы в транзакции, проверив значение системной переменной @@TRANCOUNT (Transact-SQL). Нуль означает, что транзакции нет, что-то еще показывает, сколько вложенных уровней транзакций вы находитесь. В зависимости от вашей версии сервера sql вы можете использовать XACT_STATE (Transact-SQL ).

Если вы выполните следующее:

BEGIN TRANSACTION

EXEC my_stored_procedure_with_5_statements_inside @Parma1

COMMIT

все в рамках процедуры покрывается транзакцией, все 6 операторов (EXEC - это оператор, охватываемый транзакцией, 1 + 5 = 6). Если вы это сделаете:

BEGIN TRANSACTION

EXEC my_stored_procedure_with_5_statements_inside @Parma1
EXEC my_stored_procedure_with_5_statements_inside @Parma1

COMMIT

все в пределах двух вызовов процедуры покрывается транзакцией, все 12 операторов (2 EXECs - оба оператора, охватываемые транзакцией, 1 + 5 + 1 + 5 = 12).

Ответ 2

Вы можете узнать самостоятельно, создав небольшую хранимую процедуру, которая делает что-то простое, скажем, вставьте запись в тестовую таблицу. Тогда Бегин Тран; запустить sp_test; откатить; Есть ли новая запись? Если это так, то SP игнорирует внешнюю транзакцию. Если нет, то SP - это просто еще один оператор, выполняемый внутри транзакции (что, я уверен, имеет значение).