Session Settings

Question

Session Settings

Viewing 6 posts - 16 through 20 (of 20 total)

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

KevinC. SSCommitted Points: 1570 More actions · Answer 1

Once again, I learned something new. I had assumed that you needed a 'begin transaction' before you could rollback a transaction. I didn't know about ANSI_DEFAULTS also activating the IMPLICIT_TRANSACTIONS.:-D

Thanks bitbucket.

--

KevinC.

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

learnt something new , but i think this is not used regularly or am i wrong?

"Keep Trying"

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

ChiragNS
learnt something new , but i think this is not used regularly or am i wrong?

My off the cuff answer is that universal "It all depends"

Read this page to obtain a clearer answer to your question:

http://msdn.microsoft.com/en-us/library/ms188340.aspx

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]

Saurabh Dwivedy SSCommitted Points: 1887 More actions · Answer 4

I have one confusion with regards to the following comment made in the answer ...

Msg 3903 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

If I understand correctly, since the ANSI_DEFAULTS ON setting ensures IMPLICIT TRANSACTIONS... a SELECT following the SET ANSI_DEFAULTS ON will automatically trigger a BEGIN TRANSACTION. So why would we get the message Msg 3903: ROLLBACK TRANSACTION request has no corresponding BEGIN TRX.

Isn't the purpose of the question to test the understanding that an explicit BEGIN TRANSACTION is not actually required in case ANSI_DEFAULTS are on?

Or am I misreading something here?

Thanks

Saurabh Dwivedy
___________________________________________________________

My Blog: http://tinyurl.com/dwivedys

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537

Be Happy!

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

Saurabh Dwivedy
Isn't the purpose of the question to test the understanding that an explicit BEGIN TRANSACTION is not actually required in case ANSI_DEFAULTS are on?

Yes that was the purpose of the question.

But for those that do not fully understand the effect of setting ASNI_DEFAULTS ON ... it would appear to be the obvious answer, if that answer was not part of the question then individuals answering had a 50 percent chance of selecting the correct answer and if they did so may not have learned anything from the question, other than they have some good luck.

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]

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 6

This is an excellent question, thank you bitbucket. I did not know about the side effect of the ansi_defaults settings in 2008, so I answered incorrectly, but learned something. As far as the differences between the 2005 and 2008 versions go, it is easy to check the expected behavior in 2005 by setting implisit_transactions instead of the ansi_defaults to mimic the 2008 behavior. The implisit_transactions settings should be used with caution though. For example, consider the following scenario when the option is already set by default: Dev1 issues the faux update on the table (every record) to check for example, trigger performance. Something like update the_table set some_column = some_column. Dev1 does not see that the @@trancount actually stands at 1 because the begin tran was implicitly called when the update was encountered by the engine. He now goes out for a smoke break leaving the SSMS editor window open. Before he comes back and issues commit or rollback explicitly, Dev2 cannot access any records in the same table because the exclusive lock caused by the update has been placed on every record despite the fact that it was a faux update not actually changing anything. Thus, if the ansi_defaults in 2008 or implicit_transactions in 2005 are set then it is always a good idea to issue if @@trancount > 0 rollback /*(or commit)*/ tran to avoid the problem described above.

Oleg