1

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?

George L
  • 13
  • 3
  • 1
    You can find out using a `SELECT @@trancount` https://learn.microsoft.com/en-us/sql/t-sql/functions/trancount-transact-sql?view=sql-server-ver15 – Squirrel Feb 28 '21 at 10:57
  • Sql-server has no nested transactions. If you want to save part of the work use `SAVE TRANSACTION` See for example https://stackoverflow.com/questions/63924746/sql-server-partial-commit-in-transaction – Serg Feb 28 '21 at 11:12
  • @Squirrel Yes, but it's not always immediately obvious which commands open a transaction in implicit mode – Charlieface Feb 28 '21 at 13:49

1 Answers1

1

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.

Charlieface
  • 52,284
  • 6
  • 19
  • 43