Re: Thoughts on implicit/auto COMMITs

As Nenad mentioned , these options are ALLOW_SNAPSHOT_ISOLATION ON , READ_COMMITTED_SNAPSHOT
ON .

With these options , we can simulate the "locking behavior" in SQL Server to simulate "Oracle's Locking behavior " in the recent versions

I have explained in the my blog at
http://mfzahirdba.blogspot.com/2013/12/nolock-think-twice.html

When switching from DBMS to DBMS , it is better to unlearn the old habits and learn the new habits suitable for that particular DBMS.

Zahir

Zahir Mohideen
http://mfzahirdba.blogspot.com/

*Nothing so GREAT was achieved without enthusiasm*

On Thu, Mar 15, 2018 at 11:09 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> My recommendation for any database with even minimal capabilities is to
> bundle logical units of work (LUWs) as procedures with proper error traps
> and flow for each branch, with an official commit or rollback as soon as
> possible.
>
>
>
> I wrote this up in a paper I “Minimizing the Concurrency Footprint of
> Transactions” that should be available on both IOUG and RMOUG sites.
>
>
>
> Leaving autocommit in the wind should be considered an emergency
> work-around, IMHO, which seems to be exactly the case in point (but one
> hopes additional real remediation is pending).
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_
> freelists.org] *On Behalf Of *Noveljic Nenad
> *Sent:* Thursday, March 15, 2018 10:01 AM
> *To:* 'rjoralist3_at_society.servebeer.com'; oracle-l_at_freelists.org
> *Subject:* RE: Thoughts on implicit/auto COMMITs
>
>
>
> Perhaps it’s worth noting that it’s possible to override the autocommit
> setting on the session level in the case that you’re doing some ad-hoc
> DMLs. In fact, there is an option in SQL Server Management Studio that can
> automatically do that for you.
>
>
>
> What you can also do, irrespective of the autocommit database setting, is
> to explicitly start the transaction, group all of the DMLs in there and
> finally commit the transaction.
>
>
>
> With regard to the locking issues caused by the isolation level,
> read_committed_snapshot still might be a good option to consider. Of
> course, after thoroughly clarifying it with the application vendor (that’s
> exactly what I’ve meant with “Some applications might even rely on this
> behavior.“).
>
>
>
> As a matter of fact, some software vendors, like Atlassian (see
> https://confluence.atlassian.com/jirakb/health-check-
> database-isolation-834225506.html ) have already recognized the benefits
> of row versioning and therefore started to develop the application based on
> read_committed_snapshot=ON. Also, we’ve massively reduced the locking
> issues after activating the setting for some of our databases. Again, it
> has to be a joint effort of the application team and the DBAs.
>
>
>
> Nenad
>
>
>
> http://nenadnoveljic.com/blog/
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_
> freelists.org <oracle-l-bounce_at_freelists.org>] *On Behalf Of *Rich J
> *Sent:* Donnerstag, 15. März 2018 14:24
> *To:* oracle-l_at_freelists.org
> *Subject:* Re: Thoughts on implicit/auto COMMITs
>
>
>
> On 2018/03/15 07:34, Jeff Smith wrote:
>
> Brent is a friend and an ex-coworker. He wanted to share the background of
> this customer's scenario, in case it would help you with yours.
>
> I let Brent know some folks were having...fun...with his take on
> autocommit.
>
> Jeff
>
>
>
> *Heh heh heh, I can only imagine. The difference on optimistic vs
> pessimistic concurrency nailed it though - the default combo of optimistic
> & implicit transactions makes sense in Oracle, and the default of
> pessimistic and automatic transactions makes sense in SQL Server. It's when
> you change only one of those two settings that you're screwed.*
>
> *The blog post stemmed from an app that had been written by SQL Server
> people, and then an Oracle guy came in and made a few changes. He switched
> to implicit transactions without understanding that everybody was doing
> single-line inserts/updates all over the place in code, not bothering to
> set transactions. He didn't understand the impact of what he was doing.
> (Not an Oracle jab by any means - the guy was well-meaning but just not
> prepared.)*
>
> *We got called in because performance went straight into the toilet. Even
> worse, rollbacks were rolling back completely unrelated transactions, and
> nobody knew why, hahaha*.
>
> Ah, that context adds a lot to the assertion. I still disagree that
> autocommit is a good practice for applications, whether it's Oracle or SQL
> Server, but I understand where Brent's coming from.
>
> And my intent wasn't to have "fun", but a sanity check for myself. IT
> changes constantly outside of my narrow focus, and as I've been following
> Brent's blog for years, that entry offers an opinion that is completely
> backwards of my understanding of how any modern RDBMS should work.
>
> So, of course, I ask *Oracle* people about it. :)
>
> Thanks all for the sanity check!
> Rich
>
>
>
> ____________________________________________________
>
> Please consider the environment before printing this e-mail.
>
> Bitte denken Sie an die Umwelt, bevor Sie dieses E-Mail drucken.
>
>
> Important Notice
> This message is intended only for the individual named. It may contain
> confidential or privileged information. If you are not the named addressee
> you should in particular not disseminate, distribute, modify or copy this
> e-mail. Please notify the sender immediately by e-mail, if you have
> received this message by mistake and delete it from your system.
> E-mail transmission may not be secure or error-free as information could
> be intercepted, corrupted, lost, destroyed, arrive late or incomplete. Also
> processing of incoming e-mails cannot be guaranteed. All liability of the
> Vontobel Group and its affiliates for any damages resulting from e-mail use
> is excluded. You are advised that urgent and time sensitive messages should
> not be sent by e-mail and if verification is required please request a
> printed version.
>

--
http://www.freelists.org/webpage/oracle-l