Another bug in SS?

Question

Post reply

Another bug in SS?

Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Ninja's_RGR'us (9/9/2011)
Any reason why Begin tran <name>
doesn't require rollback tran <name> for the undo?

Because a transaction's name is purely and only for documentation purposes and serves no syntactical purpose.

A begin tran starts a transaction if there is none or bumps the tran count (and does nothing else) if there is already one started. A commit decrements the tran count and, if after decrementing it's 0, commits the transaction. A rollback always rolls everything back no matter what.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

p.s. All of these are valid

BEGIN TRANSACTION SomeTran

INSERT INTO dbo.TestingCleanup (Description)

VALUES ('Hi')

ROLLBACK TRANSACTION

BEGIN TRANSACTION SomeTran

INSERT INTO dbo.TestingCleanup (Description)

VALUES ('Hi')

ROLLBACK TRANSACTION SomeTran

BEGIN TRANSACTION

INSERT INTO dbo.TestingCleanup (Description)

VALUES ('Hi')

COMMIT TRANSACTION SomeTran

BEGIN TRANSACTION SomeTran

INSERT INTO dbo.TestingCleanup (Description)

VALUES ('Hi')

COMMIT TRANSACTION

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Nice one Gail, you should turn that into a QODT.

Like select on the options that will error out or something like that, with one that actually does fail.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

The only one that fails is a rollback that references a name that doesn't exist, and that's a side-effect of savepoints.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

GilaMonster (9/9/2011)
The only one that fails is a rollback that references a name that doesn't exist, and that's a side-effect of savepoints.

So yay / nay on the qotd. This is your code so I don't want to take it away from ya.

If you don't want to do it, do you allow me to post one?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Ninja's_RGR'us (9/9/2011)
GilaMonster (9/9/2011)
The only one that fails is a rollback that references a name that doesn't exist, and that's a side-effect of savepoints.
So yay / nay on the qotd. This is your code so I don't want to take it away from ya.
If you don't want to do it, do you allow me to post one?

Go wild. I'm not keen on submitting QotD due to all the whiny comments you get.

The one that fails is

BEGIN TRANSACTION

INSERT INTO dbo.TestingCleanup (Description)

VALUES ('Hi')

ROLLBACK TRANSACTION SomeTran

Msg 6401, Level 16, State 1, Line 4

Cannot roll back SomeTran. No transaction or savepoint of that name was found.

It's a misleading error because if there were a transaction named SomeTran, the rollback would not be rolling back that transaction, it'd be rolling back ALL open transactions.

Oh, this fails too, but the complexities of nested (not) transactions is maybe overkill for a QotD (unless you're doing a 'what does this return' question)

BEGIN TRANSACTION Tran1

INSERT INTO dbo.TestingCleanup (Description)

VALUES ('Hi')

BEGIN TRANSACTION Tran2

INSERT INTO dbo.TestingCleanup (Description)

VALUES ('2')

BEGIN TRANSACTION Tran3

INSERT INTO dbo.TestingCleanup (Description)

VALUES ('3')

ROLLBACK TRANSACTION Tran3

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TomThomson SSC Guru Points: 104773 More actions · Answer 7

GilaMonster (9/9/2011)
Oh, this fails too, but the complexities of nested (not) transactions is maybe overkill for a QotD (unless you're doing a 'what does this return' question)
BEGIN TRANSACTION Tran1
INSERT INTO dbo.TestingCleanup (Description)
VALUES ('Hi')
BEGIN TRANSACTION Tran2
INSERT INTO dbo.TestingCleanup (Description)
VALUES ('2')
BEGIN TRANSACTION Tran3
INSERT INTO dbo.TestingCleanup (Description)
VALUES ('3')
ROLLBACK TRANSACTION Tran3

Not overkill, probablt too easy for a "what does this return" if it's laid out like that; if you indented ROLLBACK TRANSACTION Tran3 do that it was indented exactly the same as BEGIN TRANSACTION Tran3 that would encourage them to get it wrong.

Tom

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Want to be really evil? (now we are getting into the mess that is nested transactions, named transactions and error handling)

How many rows does that select return? 0, 1, 2 or 3?

CREATE TABLE Testing (

SomeCol INT

);

GO

BEGIN TRANSACTION Tran1;

INSERT INTO dbo.Testing (SomeCol)

VALUES (1)

BEGIN TRANSACTION Tran2

INSERT INTO dbo.Testing (SomeCol)

VALUES (2)

BEGIN TRANSACTION Tran3

INSERT INTO dbo.Testing (SomeCol)

VALUES (3)

ROLLBACK TRANSACTION Tran3

ROLLBACK TRANSACTION Tran2

COMMIT TRANSACTION Tran1

SELECT * FROM dbo.Testing

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

GilaMonster (9/9/2011)
Want to be really evil? (now we are getting into the mess that is nested transactions, named transactions and error handling)
How many rows does that select return? 0, 1, 2 or 3?
CREATE TABLE Testing (
SomeCol INT
);
GO
BEGIN TRANSACTION Tran1;
INSERT INTO dbo.Testing (SomeCol)
VALUES (1)
BEGIN TRANSACTION Tran2
INSERT INTO dbo.Testing (SomeCol)
VALUES (2)
BEGIN TRANSACTION Tran3
INSERT INTO dbo.Testing (SomeCol)
VALUES (3)
ROLLBACK TRANSACTION Tran3
ROLLBACK TRANSACTION Tran2
COMMIT TRANSACTION Tran1
SELECT * FROM dbo.Testing

Interesting. I would have gotten it wrong. Of course it depends on if SET XACT_ABORT is ON or OFF. At least based on what I just ran.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

TomThomson SSC Guru Points: 104773 More actions · Answer 10

GilaMonster (9/9/2011)
Want to be really evil? (now we are getting into the mess that is nested transactions, named transactions and error handling)
How many rows does that select return? 0, 1, 2 or 3?

Maybe instead "Tick which messages are received:

1) 3 X 1 row(s) selected message

2) 4 X 1 row(s) selected message

3) 1 X 3 row(s) selected message

4) 1 X error message (can't roll back non-existent transaction)

5) 1 X error message (can't commit non-existent transaction)

6) 2 X error message (can't roll back non-existent transaction)

with the right answer being tick 1, 3, and 6 (needs a bit of rephrasing but you can see what I'm suggesting).

Or would that just tempt people to run it instead of thinking?

Tom

mister.magoo SSC-Forever Points: 47174 More actions · Answer 11

GilaMonster (9/9/2011)
... A rollback always rolls everything back no matter what.

I can't believe I am questioning you Gail, but doesn't that statement need an exception clause for saved transactions?

For example, the following code does not "roll everything back no matter what."

BEGIN TRANSACTION;

INSERT SomeTable VALUES(1);

SAVE TRANSACTION MySavedTransaction;

INSERT SomeTable VALUES(2);

ROLLBACK TRANSACTION MySavedTransaction;

COMMIT TRANSACTION;

...or am I missing your point?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

You're missing my point. We were talking nested transactions, not savepoints

No matter how many open transactions there are, a ROLLBACK TRANSACTION always rolls back to the outermost transaction. If it's given a transaction name (and I did not say a savepoint name) the rollback rolls all open transactions back, not the one that's named in the rollback.

This does not roll back Tran1 and leave Tran2 and Tran3 open and uncommitted

BEGIN TRANSACTION Tran1

INSERT INTO dbo.TestingCleanup (Description)

VALUES ('Hi')

BEGIN TRANSACTION Tran2

INSERT INTO dbo.TestingCleanup (Description)

VALUES ('2')

BEGIN TRANSACTION Tran3

INSERT INTO dbo.TestingCleanup (Description)

VALUES ('3')

ROLLBACK TRANSACTION Tran1

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mister.magoo SSC-Forever Points: 47174 More actions · Answer 13

GilaMonster (9/10/2011)
You're missing my point. We were talking nested transactions, not savepoints
No matter how many open transactions there are, a ROLLBACK TRANSACTION always rolls back to the outermost transaction. If it's given a transaction name (and I did not say a savepoint name) the rollback rolls all open transactions back, not the one that's named in the rollback.
This does not roll back Tran1 and leave Tran2 and Tran3 open and uncommitted
BEGIN TRANSACTION Tran1
INSERT INTO dbo.TestingCleanup (Description)
VALUES ('Hi')
BEGIN TRANSACTION Tran2
INSERT INTO dbo.TestingCleanup (Description)
VALUES ('2')
BEGIN TRANSACTION Tran3
INSERT INTO dbo.TestingCleanup (Description)
VALUES ('3')
ROLLBACK TRANSACTION Tran1

Thanks Gail, I did understand that - I am quite happy about that, but the statement you made was

A rollback always rolls everything back no matter what.

...and it was that statement that bothered me as someone reading that may be lead to believe that save points did not work, especially coming from someone as knowledgeable as yourself.

I just wanted to check that you were not meaning to give that impression (and also to sanity check myself as I knew that save points did work - but the way I read your post, it seemed to contradict that knowledge and I do take seriously any of your posts.)

Thanks.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);