Nested Transactions

Question

Post reply

Nested Transactions

timwell

SSCertifiable

Points: 5315
More actions
October 26, 2013 at 2:45 pm

#281710

Comments posted to this topic are about the item Nested Transactions

Viewing 15 posts - 1 through 15 (of 25 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Nice question on the fundamentals ..

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 2

Nice start to the week.

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Bangla Hall of Fame Points: 3137 More actions · Answer 3

Bangla

Hall of Fame

Points: 3137

October 27, 2013 at 10:58 pm

#1661845

Nice question on basics...

Anipaul SSC-Insane Points: 24681 More actions · Answer 4

Anipaul

SSC-Insane

Points: 24681

October 27, 2013 at 11:00 pm

#1661846

Good one....

free_mascot One Orange Chip Points: 27169 More actions · Answer 5

Like the basic question.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

sqlnaive SSCoach Points: 17437 More actions · Answer 6

sqlnaive

SSCoach

Points: 17437

October 28, 2013 at 1:18 am

#1661858

Very good QOTD Tim. 🙂

Umer Akram SSC-Insane Points: 22209 More actions · Answer 7

Very good question ...

very handy to understand the working nested transaction and SAVE point

TomThomson SSC Guru Points: 104773 More actions · Answer 8

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.

Tom

marlon.seton SSCrazy Points: 2623 More actions · Answer 9

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?

sqlnaive SSCoach Points: 17437 More actions · Answer 10

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.

Eagle eye L'eomot.

I checked the transaction level one by one and here is what i found:

BEGIN TRAN A

INSERT INTO TranTable (col) Values ('abc')

select @@TRANCOUNT as 'trancount 1'-- 1

BEGIN TRAN B

INSERT INTO TranTable (col) Values ('def')

select @@TRANCOUNT as 'trancount 2'-- 2

SAVE TRAN B

select @@TRANCOUNT as 'trancount 3'-- 2

BEGIN TRAN C

INSERT INTO TranTable (col) Values ('ghi')

select @@TRANCOUNT as 'trancount 4'-- 3

COMMIT TRAN C

select @@TRANCOUNT as 'trancount 5'-- 2

ROLLBACK TRAN B

select @@TRANCOUNT as 'trancount 6'-- 2

INSERT INTO TranTable (col) Values ('xyz')

COMMIT TRAN A

select @@TRANCOUNT as 'trancount 7'-- 1

GO

The following link states that "ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT."

http://technet.microsoft.com/en-us/library/ms181299(v=sql.105).aspx

Really important point L'eomot. Cheers 🙂

TomThomson SSC Guru Points: 104773 More actions · Answer 11

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).

Tom

marlon.seton SSCrazy Points: 2623 More actions · Answer 12

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).

Thanks for the info.

steve.jacobs SSCommitted Points: 1830 More actions · Answer 13

Nice question geared to the fundamentals. I almost got it wrong (reading it over too quickly). Thanks. 🙂

sestell1 SSChampion Points: 10230 More actions · Answer 14

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.

Nice catch Tom!

"When a transaction begins, resources used during the transaction are held until the completion of the transaction (namely, locks). When part of a transaction is rolled back to a savepoint, resources continue to be held until the completion of the transaction or a rollback of the complete transaction."