September 14, 2010 at 8:32 am
Very nice question. I was unaware of savepoints for transactions either.
September 14, 2010 at 9:14 am
A pleasant question.
September 14, 2010 at 9:15 am
LUCAB (9/14/2010)
I was wrong answer: "Outer transaction is still open....rolling back... ".I learned that my wrong answer can be obtained with a SAVEPOINT:
CREATE TABLE MyTable (MyId INT IDENTITY (1,1),
MyCity NVARCHAR(50))
BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')
BEGIN TRANSACTION InnerTran
SAVE TRAN InnerTranSavePoint -- NEW CODE: SAVEPOINT
INSERT INTO MyTable VALUES ('London')
ROLLBACK TRAN InnerTranSavePoint -- MODIFIED CODE: RETURN TO SAVE TRAN
IF (@@TRANCOUNT = 0)
BEGIN
PRINT 'All transactions were rolled back'
END
ELSE
BEGIN
PRINT 'Outer transaction is still open....rolling back...'
ROLLBACK TRANSACTION OuterTran
END
DROP TABLE MyTable
In the above code, the line
BEGIN TRANSACTION InnerTran
Does not appear to be required (at least to effect the same result).
September 14, 2010 at 10:27 am
You cannot simply rollback a named nested transaction you can only rollback to a savepoint.
"Naming multiple transactions in a series of nested transactions with a transaction name has little effect on the transaction. Only the first (outermost) transaction name is registered with the system. A rollback to any other name (other than a valid savepoint name) generates an error. None of the statements executed before the rollback is, in fact, rolled back at the time this error occurs. The statements are rolled back only when the outer transaction is rolled back".
http://msdn.microsoft.com/en-us/library/ms188929.aspx
This example uses a savepoint to rollback a nested transaction:
CREATE TABLE MyTable (MyId INT IDENTITY (1,1),
MyCity NVARCHAR(50))
BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')
Save Transaction SavePoint1
BEGIN TRANSACTION InnerTran
INSERT INTO MyTable VALUES ('London')
ROLLBACK TRAN SavePoint1
IF (@@TRANCOUNT = 0)
BEGIN
PRINT 'All transactions were rolled back'
END
ELSE
BEGIN
PRINT 'Outer transaction is still open....rolling back...'
ROLLBACK TRANSACTION OuterTran
END
DROP TABLE MyTable
go
September 14, 2010 at 11:09 am
try this:
CREATE TABLE MyTable (MyId INT IDENTITY (1,1),
MyCity NVARCHAR(50))
BEGIN TRANSACTION OuterTran
INSERT INTO MyTable VALUES ('Boston')
BEGIN TRANSACTION InnerTran
INSERT INTO MyTable VALUES ('London')
ROLLBACK TRAN
BEGIN TRANSACTION InnerTran
INSERT INTO MyTable VALUES ('paris')
IF (@@TRANCOUNT = 0)
BEGIN
PRINT 'All transactions were rolled back'
END
ELSE
BEGIN
PRINT 'Outer transaction is still open....rolling back...'
ROLLBACK TRANSACTION OuterTran
END
Message:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Outer transaction is still open....rolling back...
Msg 6401, Level 16, State 1, Line 23
Cannot roll back OuterTran. No transaction or savepoint of that name was found.
It will be the else loop case.
September 14, 2010 at 12:17 pm
Good question, thanks.
September 14, 2010 at 11:30 pm
Nice question and discussion, thanks!
September 15, 2010 at 7:20 am
QoTD keeping us on our toes. Nice question, thanks.
September 15, 2010 at 8:38 am
I don't even know how excitingly appropriate this is.
Yesterday I learned about savepoints. Today a trigger that was invoking a stored procedure that created an inner transaction and rolled it back if a test case was pushed into production.
Nobody could figure out why the table with the tirgger was never getting a record.
Stuck in a transaction save point right after the BEGIN TRANSACTION in the stored proc and set the ROLLBACK to rollback to the savepoint and made the problem go away.
Awesome
September 15, 2010 at 8:40 am
mtassin (9/15/2010)
I don't even know how excitingly appropriate this is.Yesterday I learned about savepoints. Today a trigger that was invoking a stored procedure that created an inner transaction and rolled it back if a test case was pushed into production.
Nobody could figure out why the table with the tirgger was never getting a record.
Stuck in a transaction save point right after the BEGIN TRANSACTION in the stored proc and set the ROLLBACK to rollback to the savepoint and made the problem go away.
Awesome
Hey, Mark!
That's really good to know!
Coincidence is a great phenomenon - I wonder how they happen?
Have a wonderful rest-of-the-week ahead!
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
September 15, 2010 at 9:07 am
Nakul Vachhrajani (9/15/2010)
Hey, Mark!
That's really good to know!
Coincidence is a great phenomenon - I wonder how they happen?
Have a wonderful rest-of-the-week ahead!
For me,
around here this happens about once every 3 months.
But usually I learn about it 2-3 weeks before I need to use it.. not 24 hours 🙂
Tally Tables, Dynamic Paramterized Queries and several other topics typically hit here about 3 weeks before I need them. This time it was in a QOTD.
As I said, awesome.
September 18, 2010 at 11:40 am
da-zero (9/14/2010)
Nesting does work with transactions. It's just that if you use rollback transaction, then all open transactions are rolled back. If you commit a transaction, then only the innermost transaction is committed.
No, you can't commit an inner transaction - what happens when you issue a commit command is that the transaction count gets decremented, and if the result is zero, the transaction (which is the outermost transaction) is committed (and the data modifications affected by this commit include all made by any so-called nested transactions); if the result is non-zero, nothing is committed. Neither can you roll back a nested transaction - you can only roll back the whole nest (including the outermost transaction).
Tom
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply