Nested Transactions

Question

Nested Transactions

October 28, 2013 at 8:39 am

This was removed by the editor as SPAM

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply

Thomas Abraham SSChampion Points: 10761 More actions · Answer 1

L' Eomot Inversé (10/28/2013)
marlon.seton (10/28/2013)
Will dropping the table remove the open transaction on the table?
No - the transaction is still open, and the table is not dropped until the transaction is committed: subsequent commands within the same transaction can't see the table, attempts in another context to access the table will hang up because the uncommitted transaction which has issued the DROP tatement has an exclusive lock (not a data lock, this one will hold up an access attempt even if the other connection uses WITH (NOLOCK). If the session is closed without the transaction being committed (eg by the server closing down) the transaction is rolled back (so the table is not dropped).

Here's the message I got when closing the session window:

There are uncommitted transactions. Do you wish to commit these transactions before closing the window?

Thanks for pointing this out Tom.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 2

Great question, and well spotted Tom.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

tom.w.brannon Hall of Fame Points: 3976 More actions · Answer 3

Thank you for the question and all the discussion. I am not a DBA. The T-SQL training I got barely mentioned that there was a BEGIN TRAN statement along with COMMIT and ROLLBACK and this is the first I've heard of SAVE TRAN. Clears up a lot of confusion over the last few years.

timwell SSCertifiable Points: 5315 More actions · Answer 4

OK. This shows how incomplete understanding can lead to problems...

In further experimenting I found:

If you run the script again it seems to create and drop the table again but it ends up with a yet higher @@TRANCOUNT

If you do a ROLLBACK after the DROP TABLE, then do a select, the table still exists but has no rows.

Thanks to Tom and everyone, I learned something from my own QOTD post...

Revenant SSC-Forever Points: 42467 More actions · Answer 5

Revenant

SSC-Forever

Points: 42467

October 28, 2013 at 9:59 am

#1662071

Nice and easy - thanks, Tim!

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 6

I almost got it wrong - noticed just at the last moment that the savepoint and one of the transactions have the same name. For the record - giving savepoints and transactions the same name is not a good idea for writing maintainable code. (It's great for writing sneaky QotD entries, though).

Surprised to see that the answer that would have been correct if the savepoint had a different name is the least popular. I expected to see close to 50% there; it's only 9%.

Great addition by Tom. I never use named transactions and I didn't bother to look it up; I expected that SQL Server would attempt to match a named rollback or commit to the corresponding transaction. Makes you wonder what the popint of using those names is - apart from rolling back to a savepoint or using named transactions as markers in the log for a point in time restore (but if you know in advance yoou're going to need a point in time restore, why not avoid making the "ooops"?)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 7

PChiragS

SSCarpal Tunnel

Points: 4965

October 28, 2013 at 1:00 pm

#1662144

good question..

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 8

Great question

It's easy to get lost between Nested Transactions specailly when we use same naming for SAVE point & TRAN.

+1

Ken SSCoach Points: 17191 More actions · Answer 9

Ken

SSCoach

Points: 17191

October 28, 2013 at 7:54 pm

#1662206

Nice question. Thanks Tim!

jclementz SSC Eights! Points: 910 More actions · Answer 10

marlon.seton (10/28/2013)
L' Eomot Inversé (10/28/2013)
Nice question.
Has one small problem though: the code leaves a transaction open - nothing is actually committed.
It's clearly stated in BOL:-
Although each COMMIT TRANSACTION statement has a transaction_name parameter, there is no relationship between the COMMIT TRANSACTION and BEGIN TRANSACTION statements. The transaction_name parameters are simply readability aids to help the programmer ensure that the proper number of commits are coded to decrement @@TRANCOUNT to 0 and thereby commit the outer transaction.
Thus the COMMIT TRAN A stement simply decrements the trancount to close the innermost the innermost open transaction since there are still two transactions open when it is encountered: the transaction named A and the transaction named B, since ROLLBACK TRAN B rolled back to the savepoint labelled B, and that savepoint includes some work done in teh transaction named B. The transaction named A, which is the outermost transaction, remains uncommitted.
Will dropping the table remove the open transaction on the table?

Even if you drop the table, there will still be an open transaction. If you want, you can try running the query from the QOTD up to the select statment. Then, open up a new query window and try to select from TranTable. The table will be locked, so you won't see any results. Then, go back to the first window and run the Drop Table TranTable line. Go back to the second window, where you were trying to select from TranTable, and you will notice that the process was killed due to a deadlock.