CODEWITHSUNDEEP
sqlsql-serversqltransaction
George L
George L

Reputation: 13

How many transactions were started during the execution of the request?

I have a transaction. The code is

set implicit_transactions on
select getdate()
begin transaction
begin transaction
   select * from price p1, product p2 where p1.product_id = p2.product_id
rollback
   delete from PRODUCT where product_id = 100871
   select 100860,2,3,getdate(),null from PRODUCT
commit
drop table price

The question is how many transactions were started during the execution of the request? As I understand it, some people refer to this as a "nested transaction" although this is not official terminology.

So, from the server's point of view, were there 2 transactions started, or 1?

Upvotes: 1

Views: 204

Answers (1)

Charlieface
Charlieface

Reputation: 72501

The answer is neither of those.

It is three: one rolled back, one committed, the last one hanging.

EDIT: Original version was wrong about select getdate

Quote from the docs: SELECT statements that do not select from a table do not start implicit transactions. For example SELECT GETDATE(); or SELECT 1, 'ABC'; do not require transactions.

As follows:

  • You switch on implicit_transactions, this means commands open and leave open a transaction, they do not auto-commit.
  • select getdate() does not open a transaction, as no table is referenced
  • begin transaction opens a transaction
  • begin transaction increments @@trancount to 2
  • select * from makes no change, would normally open a transaction
  • rollback rolls everything back
  • delete from opens, but does not commit, a transaction.
  • select 100860 does nothing
  • commit commits
  • drop table price opens and leaves hanging a transaction

What happens if implicit_transactions is off? The answer is also three transactions, all committed except for the explicit one which is rolled back

  • select getdate() does nothing.
  • begin transaction opens, but does not commit, a transaction.
  • begin transaction increments @@trancount to 2
  • select * from makes no change
  • rollback rolls everything back
  • delete from opens, and commits, a transaction.
  • select 100860 does nothing.
  • commit commits nothing
  • drop table price opens, and commits, a transaction.

Please note: rollback transaction without a semi-colon ; is dangerous, as the next word can be interpreted as a transaction name. An incorrect transaction name means nothing happens. This is why you should always terminate statements with a semi-colon.

Upvotes: 1

Related Questions