Transactions 2

Question

Transactions 2

Viewing 12 posts - 46 through 56 (of 56 total)

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

ralph.bacon SSC Journeyman Points: 99 More actions · Answer 1

rmechaber (5/23/2012)
...I found this write-up useful in explaining the benefits -- and issues -- related to XACT_ABORT and TRY..CATCH: http://www.simple-talk.com/sql/t-sql-programming/defensive-error-handling/[/url].
For example, there's a nice demonstration of how XACT_STATE() is needed to catch uncommittable transactions, following something as simple as a failed type conversion from string to integer [SELECT CAST('abc' AS INTEGER)].
Rich

An excellent article, that should be compulsory reading for all us SQL developers!

Thanks for posting Rich.

Michael G Ten Centuries Points: 1323 More actions · Answer 2

Nasty, but great question. Tricked me! 😎

Michael Gilchrist
Database Specialist
There are 10 types of people in the world, those who understand binary and those that don't. 😀

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 3

Nice question and nice conversation as always 🙂

Thanks

call.copse SSCoach Points: 18043 More actions · Answer 4

I have to say that I am astonished that I was not crystal clear on this(I got it wrong), after the years I have programmed against SQL Server. In mitigation I am an application developer and thus less likely to work directly in the way this question operates (i.e. I would control transactions externally in most cases). Still, this is why I keep doing the QOTD to be reminded of my limitations - great discussion which has really clarified the whole tx thing and the reasons behind how it works the way it does, thanks.

Toreador SSChampion Points: 11365 More actions · Answer 5

Hugo Kornelis (5/23/2012)
If you want the application to be outside SQL Server, you should ... even control the transactions from the application.

That's what we do (MTS in our case). Has its down sides, but allows all the error handling and logging to be done in the application.

kapfundestanley SSCertifiable Points: 5637 More actions · Answer 6

Nice question i got it correct,i encountered a similar experience.I liked the explanation learned something.thank you.

“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris

Rich Mechaber SSChampion Points: 11130 More actions · Answer 7

ralph.bacon (5/24/2012)
rmechaber (5/23/2012)
...I found this write-up useful in explaining the benefits -- and issues -- related to XACT_ABORT and TRY..CATCH: http://www.simple-talk.com/sql/t-sql-programming/defensive-error-handling/[/url].
For example, there's a nice demonstration of how XACT_STATE() is needed to catch uncommittable transactions, following something as simple as a failed type conversion from string to integer [SELECT CAST('abc' AS INTEGER)].
Rich
An excellent article, that should be compulsory reading for all us SQL developers!
Thanks for posting Rich.

YW, thanks for letting me know you found it useful!

Rich

Doug 53730 SSC-Addicted Points: 480 More actions · Answer 8

Great question! You'd be surprised to know how many SQL developers think if any error is encountered within a transaction it automatically gets rolled back like when XACT_ABORT is on.

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 9

I got it right for the wrong reason. Learned something. Thanks for submitting.

http://brittcluff.blogspot.com/

Neha05 Default port Points: 1494 More actions · Answer 10

Neha05

Default port

Points: 1494

August 9, 2012 at 9:00 am

#1523859

Good question.

kapil_kk SSC-Insane Points: 21316 More actions · Answer 11

wrong answer 🙁

I learned a lot from transaction questions 🙂

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 12

I answered 1 row.

Assuming violation is at second row so first row will be xcuted succesffully. One more thing i didn't notice that all three insert statements are inside a transaction.

But if it is implict transaction and set xact_abort is ON

Then first row will be inserted sucessfully..

--
Dineshbabu
Desire to learn new things..