Better concurrency in Oracle than SQL Server?

Question

Is it true that better concurrency can be achieved in Oracle databases than in MS SQL Server databases? In particular, in an OLTP scenario such as an ERP system?

I overheard an SAP consultant making this expression, citing Oracle locking methods such as string locking and consistency in reading multiple versions and a redo log.

Answer 1

Out of the box, Oracle will have higher transaction throughput, but this is due to the fact that MVCC is used by default. SQL Server by default blocks choices in uncommitted updates, but you can also change it to MVCC so that the difference basically disappears. See Reading Insulated Insulation Level .

See Enabling row-based version-based isolation levels .

When the ALLOW_SNAPSHOT_ISOLATION database is turned on, the instance of the Microsoft SQL Server Database Engine does not generate a version string for the changed data until all active transactions that have changed the data in the database are completed. If there is an active transaction modification, SQL Server sets the status of the PENDING_ON option. After all transaction changes are completed, the state of the option changes to ON. Users cannot start a snapshot transaction in this database until it is fully ON. The database goes through the PENDING_OFF state when the database administrator sets the ALLOW_SNAPSHOT_ISOLATION Option to OFF.

Answer 2

He / she probably referred to facts that:

• In Oracle, readers do not block writers, and authors do not block readers.
• Oracle does not maintain a list of row locks, so the significant overhead of locking and locking never rises to the table level.

Answer 3

Starting with SQL 2005, this is no longer the case - you can enable snapshot isolation, and your authors will not block your readers, as in Oracle.

Answer 4

The Sql server has row locking, several different levels of transaction isolation, and a transaction log that can be reproduced.

Maybe it refers to Access, which does not have them.

Or maybe he believes that Oracle uses the best defaults. It may have a better argument, but with any DBMS, if you are talking about ERP, you better have a database administrator who knows the system well enough to configure it correctly.