SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transactions 3


Transactions 3

Author
Message
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15699 Visits: 25280
L' Eomot Inversé (5/30/2012)
Until I saw the counts of answers so far I thought that maybe this repetition with tiny variations (this is the third version of this question in a couple of weeks) was a bit much, but then I saw that 29% (77 out of 270) so far have got this wrong, so now I reckon the repetition is useful after all.


Agreed it is somewhat of a repeat (with a slight, ever so slight variation), but going back to those prior questions on May 16th and 23rd) my last look at those answers:
a. 16th 53% correct
b. 23rd 47% correct

Seems like there is still a great deal of learning that needs to be driven home.

Now fore warned is fore armed, be prepared, the last in the series is scheduled for June 8th. If that is not answered 100% correctly, I guess I'll give up, no sense flogging a dead horse.

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

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Bryant McClellan
Bryant McClellan
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1447 Visits: 546
The repetition is a good idea but really depends on the audience. In high school Honors Calculus we has one basic proof show up on EVERY weekly quiz until everyone got it right. The rationale was that the proof was so basic to calculus in general that if you didn't get it, you were doomed. Eventually everyone got it right. I managed to get it from the first attempt onward although, for the life of me, I cannot remember that proof now.

Point being that transaction control is so basic to developing robust transactional systems that a little repetition early can save some serious data headaches later.

------------
Buy the ticket, take the ride. -- Hunter S. Thompson
Thomas Abraham
Thomas Abraham
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3827 Visits: 2256
Just from the first 2 lines of code, I knew this was a bitbucket question. :-)

Thanks for the easy (now that I've researched the topic) point.

I doubt I will be missed, not being a major contributor here. But, I want to wish you all well, as I'll be on vacation for a few weeks. (My wife thinks the purpose of our trip is to revive the Greek economy. She'll try.)

Αντίο, και καλή τύχη με το ζήτημα της ημέρας.

(Goodbye, and good luck with the question of the day. Either that, or I just ordered 2 ouzos and a squid.)


Edited to correct spelling of "question" - "quest" made it by spell check. Maybe I'll try German instead.

Please don't go. The drones need you. They look up to you.
Connect to me on LinkedIn
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62308 Visits: 13298
Thomas, have fun on your vacation!


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Sherwin Anderson
Sherwin Anderson
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2432 Visits: 1419
Thanks Ron, easy one but worth remembering.
roger.plowman
roger.plowman
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2421 Visits: 1380
You know, if you took out the transaction the answer would be exactly the same? How then is this a transaction? Smile

Still say having SET XACT_ABORT OFF as the default is broken, buggy behavior. Because it means *it is not a transaction*.

Growl.

And please don't quibble that "it acts like a transaction sometimes and sometimes it doesn't". That's not a transaction, folks.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25941 Visits: 12494
roger.plowman (5/30/2012)
You know, if you took out the transaction the answer would be exactly the same? How then is this a transaction? Smile

Still say having SET XACT_ABORT OFF as the default is broken, buggy behavior. Because it means *it is not a transaction*.

Growl.

And please don't quibble that "it acts like a transaction sometimes and sometimes it doesn't". That's not a transaction, folks.


Well, I'll growl "it acts like a transaction at all times - at least to the extent that the isolation level you've chosen supports transactions" (which, for the default isolation level READ COMMITTED, is pretty close to not at all).
Something is a transaction if it has the ACID properties, which are nothing to do with handling errors internal to the transaction; it isn't a transaction if it doesn't have those properties. End of story.

Tom

EL Jerry
EL Jerry
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4352 Visits: 1345
Ron, thank you for the easy question.

roger.plowman (5/30/2012)
You know, if you took out the transaction the answer would be exactly the same? How then is this a transaction? Smile

Still say having SET XACT_ABORT OFF as the default is broken, buggy behavior. Because it means *it is not a transaction*.

Growl.

And please don't quibble that "it acts like a transaction sometimes and sometimes it doesn't". That's not a transaction, folks.

This happens in this specific example, it doesn't mean it always happens with any transaction.

L' Eomot Inversé (5/30/2012)
Well, I'll growl "it acts like a transaction at all times - at least to the extent that the isolation level you've chosen supports transactions" (which, for the default isolation level READ COMMITTED, is pretty close to not at all).
Something is a transaction if it has the ACID properties, which are nothing to do with handling errors internal to the transaction; it isn't a transaction if it doesn't have those properties. End of story.

+1

"El" Jerry.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.
Cliff Jones
Cliff Jones
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5203 Visits: 3648
I am so accustomed to seeing XACT_ABORT set ON that I almost missed the fact that it was being set OFF in this case. Could explain some of the wrong answers.
John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34574 Visits: 16654
I must admit I'm surprised that having SET XACT_ABORT OFF is the default. I take the point that READ COMMITTED breaks the rules of isolation anyway, but I find this one even more alarming. This from the Transactions topic in Books Online:

It is the responsibility of an enterprise database system, such as an instance of the Database Engine, to provide mechanisms ensuring the physical integrity of each transaction. The Database Engine provides:

- Locking facilities that preserve transaction isolation.

- Logging facilities that ensure transaction durability. Even if the server hardware, operating system, or the instance of the Database Engine itself fails, the instance uses the transaction logs upon restart to automatically roll back any uncompleted transactions to the point of the system failure.

- Transaction management features that enforce transaction atomicity and consistency. After a transaction has started, it must be successfully completed, or the instance of the Database Engine undoes all of the data modifications made since the transaction started.


What it should really point out is that the facilities described in the first and third points aren't turned on by default.

John
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search