The setting is called the read_committed_snapshot which is set to off by
default. This means that readers are blocking writers. Some applications might
even rely on this behavior.
After setting read_committed_snapshot to on SQL Server will start to behave
like Oracle in this respect. Row versions will be stored in the tempdb in such
case.
Nenad
http://nenadnoveljic.com/blog/
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Andy Sayer
Sent: Donnerstag, 15. März 2018 12:19
To: arian@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Thoughts on implicit/auto COMMITs
In ye olde regions of SQL Server land, writers block readers. This has spawned
many silly workarounds like
Commit constantly
If a transaction is open for more than a second it must be killed
I believe there is some setting in more recent versions to prevent this. The
workarounds are so blindly followed as best practise that the greener grass is
a mystery to some.
Obviously this just doesn’t apply in the sane land of Oracle, writers don’t
block readers.
Regards,
Andrew
On Thu, 15 Mar 2018 at 09:13, Arian Stijf
<arian@xxxxxxxxx<mailto:arian@xxxxxxxxx>> wrote:
Hi,
in my opinion this breaks the A(tomicity) of ACID.
E.g. a transaction consisting of two dependent inserts (Parent/child),
and the first insert is commited before the second, then the database
crashes.
Regards,
Arian
On 14-Mar-18 16:57, Rich J wrote:
Hey all,
As a solo DBA responsible for a number of SQL Servers in addition to
Oracle, I try to read up on both. One of the (more respected) SQL
Server team blogs had this entry:
https://www.brentozar.com/archive/2018/02/set-implicit_transactions-one-hell-bad-idea/
..where they advocate the default auto-commit because otherwise the
row (or page, or table) is locked should someone forget to COMMIT.
This seems like an extraordinarily bad idea for anything but ad-hoc or
one-off DML (without getting into a sidebar on that particular
practice), whether Oracle or SQL Server or whatever.
Or is it just me and some old-fashioned narrow RDBMS thinking?
Rich
