54

Say I have a stored procedure consisting of several separate SELECT, INSERT, UPDATE and DELETE statements. There is no explicit BEGIN TRANS / COMMIT TRANS / ROLLBACK TRANS logic.

How will SQL Server handle this stored procedure transaction-wise? Will there be an implicit connection for each statement? Or will there be one transaction for the stored procedure?

Also, how could I have found this out on my own using T-SQL and / or SQL Server Management Studio?

Thanks!

Tom H
  • 46,766
  • 14
  • 87
  • 128
Sleepless
  • 1,097
  • 2
  • 11
  • 11
  • This https://dba.stackexchange.com/a/46266/6548 gives a nice little demo of the fact that statements inside a stored proc are NOT within a single transaction; an implicit transaction is created for each individual statement. – Rory Jun 08 '22 at 20:37

3 Answers3

45

There will only be one connection, it is what is used to run the procedure, no matter how many SQL commands within the stored procedure.

since you have no explicit BEGIN TRANSACTION in the stored procedure, each statement will run on its own with no ability to rollback any changes if there is any error.

However, if you before you call the stored procedure you issue a BEGIN TRANSACTION, then all statements are grouped within a transaction and can either be COMMITted or ROLLBACKed following stored procedure execution.

From within the stored procedure, you can determine if you are running within a transaction by checking the value of the system variable @@TRANCOUNT (Transact-SQL). A zero means there is no transaction, anything else shows how many nested level of transactions you are in. Depending on your sql server version you could use XACT_STATE (Transact-SQL) too.

If you do the following:

BEGIN TRANSACTION

EXEC my_stored_procedure_with_5_statements_inside @Parma1

COMMIT

everything within the procedure is covered by the transaction, all 6 statements (the EXEC is a statement covered by the transaction, 1+5=6). If you do this:

BEGIN TRANSACTION

EXEC my_stored_procedure_with_5_statements_inside @Parma1
EXEC my_stored_procedure_with_5_statements_inside @Parma1

COMMIT

everything within the two procedure calls are covered by the transaction, all 12 statements (the 2 EXECs are both statement covered by the transaction, 1+5+1+5=12).

KM.
  • 101,727
  • 34
  • 178
  • 212
  • So you say that in effect, each statement within the stored procedure forms its own transaction, i.e. a stored procedure with five statements inside gets executed in five transactions? – Sleepless Apr 12 '10 at 12:59
  • If there is no transaction in the procedure or outside wrapping the procedure, then each statement in the procedure is an autonomous unit of work. There not really transactions, since you can't commit or roll then back. If you wrap a procedure call in a transaction, then everything in the procedure is within executed within that single transaction. – KM. Apr 12 '10 at 13:04
  • Thanks! BTW, how do you do that code formatting I obviously failed at? – Sleepless Apr 12 '10 at 13:15
  • 2
    @Sleepless, there is a toolbar above the edit window, highlight the complete section of code then click on the 101010 icon and it will show in the preview window as code. I generaly write code on my PC using an editor then indent everything 4 spaces, when I paste it into the editor on this site it is automatically displayed as code. Also, click on the orange help "?" icon, here is the link: http://stackoverflow.com/editing-help – KM. Apr 12 '10 at 13:28
0

You can find out on your own by creating a small stored procedure that does something simple, say insert a record into a test table. Then Begin Tran; run sp_test; rollback; Is the new record there? If so, then the SP ignores the outside transaction. If not, then the SP is just another statement executed inside the transaction (which I am pretty sure is the case).

MJB
  • 7,639
  • 2
  • 31
  • 41
0

You must understand that a transaction is a state of the session. The session can be in an explicit transaction state because there is at least one BEGIN TRANSACTION that have been executed in the session wherever the command "BEGIN TRANSACTION" has been throwed (before entering in a routine or inside the routine code). Otherwise, the state of the session is in an implicit transaction state. You can have multiple BEGIN TRANSACTION, but only the first one change the behavior of the session... The others only increase the @@TRANCOUNT global sesion variable.

Implicit transaction state means that all SQL orders (DDL, DML and DCL comands) wil have an invisble integrated transaction scope.

SQLpro
  • 3,994
  • 1
  • 6
  • 14
  • If additional `BEGIN TRANSACTION` statements do nothing but increment `@@TRANCOUNT` - what is the point of counting how many times you issued `BEGIN TRANSACTION`? – youcantryreachingme Apr 25 '21 at 23:22
  • Because it is one way to know if your are in the transactional state or not. When @@TRANCOUT is over 0 the state of the session is transactional when @@TRANSCOUT = 0 you are not. And also to know how many COMMITs you must execute before having a real COMMIT of the transaction. – SQLpro Apr 29 '21 at 08:33