Better concurrency in Oracle than SQL Server? - OracleConnections2024-03-29T13:52:31Zhttp://www.oracleconnections.com/forum/topics/better-concurrency-in-oracle-than-sql-server-1?commentId=6612035%3AComment%3A37171&feed=yes&xn_auth=noStarting with SQL 2005 this i…tag:www.oracleconnections.com,2013-09-18:6612035:Comment:370802013-09-18T11:13:05.900ZSean Perryhttp://www.oracleconnections.com/profile/SeanPerry
<p>Starting with SQL 2005 this is no longer true - you can enable snapshot isolation and your writers will not block your readers, just like in Oracle.</p>
<p>Starting with SQL 2005 this is no longer true - you can enable snapshot isolation and your writers will not block your readers, just like in Oracle.</p> Sql Server has row locking, s…tag:www.oracleconnections.com,2013-09-18:6612035:Comment:370742013-09-18T10:30:32.333ZJames Scotthttp://www.oracleconnections.com/profile/JamesScott
<div class="post-text"><p>Sql Server has row locking, several different transaction isolation levels, and a transaction log that can be replayed.</p>
<p>Maybe he's referring to Access, which does not have these.</p>
<p>Or maybe he believes Oracle uses better defaults. He might have a better argument there, but with either DBMS if you're talking ERP you better have a DBA who knows enough about the system to keep it tuned properly.</p>
</div>
<div class="post-text"><p>Sql Server has row locking, several different transaction isolation levels, and a transaction log that can be replayed.</p>
<p>Maybe he's referring to Access, which does not have these.</p>
<p>Or maybe he believes Oracle uses better defaults. He might have a better argument there, but with either DBMS if you're talking ERP you better have a DBA who knows enough about the system to keep it tuned properly.</p>
</div> She was probably referring to…tag:www.oracleconnections.com,2013-09-18:6612035:Comment:371712013-09-18T10:29:34.570ZDavid Karplinhttp://www.oracleconnections.com/profile/DavidKarplin
<div class="post-text"><p>She was probably referring to the facts that:</p>
<ul>
<li>In Oracle readers do not block writers and writers do not block readers</li>
<li>Oracle does not maintain a list of row locks so there is no significant overhead in locking and locks never escalate to the table level.</li>
</ul>
</div>
<div class="post-text"><p>She was probably referring to the facts that:</p>
<ul>
<li>In Oracle readers do not block writers and writers do not block readers</li>
<li>Oracle does not maintain a list of row locks so there is no significant overhead in locking and locks never escalate to the table level.</li>
</ul>
</div> Out of the box, Oracle will h…tag:www.oracleconnections.com,2013-09-18:6612035:Comment:370712013-09-18T10:26:10.204ZLinda Woodheadhttp://www.oracleconnections.com/profile/LindaWoodhead
<div class="post-text"><p>Out of the box, Oracle will have a higher transaction throughput but this is because it defaults to <a href="http://en.wikipedia.org/wiki/Multiversion%5Fconcurrency%5Fcontrol" rel="nofollow">MVCC</a>. SQL Server defaults to blocking selects on uncommitted updates but it can be changed to MVCC as well so that difference should basically go away. See <a href="http://blogs.msdn.com/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx" rel="nofollow">Read…</a></p>
</div>
<div class="post-text"><p>Out of the box, Oracle will have a higher transaction throughput but this is because it defaults to <a href="http://en.wikipedia.org/wiki/Multiversion%5Fconcurrency%5Fcontrol" rel="nofollow">MVCC</a>. SQL Server defaults to blocking selects on uncommitted updates but it can be changed to MVCC as well so that difference should basically go away. See <a href="http://blogs.msdn.com/craigfr/archive/2007/04/25/read-committed-isolation-level.aspx" rel="nofollow">Read Committed Isolation Level</a>.</p>
<p>See <a href="http://msdn.microsoft.com/en-us/library/ms175095.aspx" rel="nofollow">Enabling Row Versioning-Based Isolation Levels</a>.</p>
<blockquote><p>When the ALLOW_SNAPSHOT_ISOLATION database option is set ON, the instance of the Microsoft SQL Server Database Engine does not generate row versions for modified data until all active transactions that have modified data in the database complete. If there are active modification transactions, SQL Server sets the state of the option to PENDING_ON. After all of the modification transactions complete, the state of the option is changed to ON. Users cannot start a snapshot transaction in that database until the option is fully ON. The database passes through a PENDING_OFF state when the database administrator sets the ALLOW_SNAPSHOT_ISOLATION option to OFF.</p>
</blockquote>
</div>