sql - 高写入(10000+ 插入/小时)、低读取(10 次读取/秒)的最佳数据库？

Question

我正在开发一个网络应用程序，目前使用的是 sql server 2008。但是，我正在考虑迁移到另一个数据库 (simpledb) 以提高性能。

我有一个后台进程，每小时最多向一个特定表中插入 10000 行。该表也被读取以在 Web 应用程序中显示数据。当后台进程运行时，Web 应用程序无法使用，因为数据库连接超时。

因此，我正在考虑转向亚马逊的 simpledb 以提高性能。亚马逊的 SimpleDB 是否针对此用例进行了优化？如果没有，我可以使用其他解决方案吗？

Answer 1

您的问题是您使用的隔离级别。除非您更改它，否则 SQL Server(和许多其他数据库)在选择将阻塞未提交读取的模式下运行。您想要更改 SQL Server，使其使用 MVCC相反(Oracle 的默认设置；MySQL 和 SQL Server 也都有)，您的问题就会消失。

来自 SET TRANSACTION ISOLATION LEVEL (Transact-SQL) :

READ COMMITTED

Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.

The behavior of READ COMMITTED depends on the setting of the READ_COMMITTED_SNAPSHOT database option:

• If READ_COMMITTED_SNAPSHOT is set to OFF (the default), the Database Engine uses shared locks to prevent other transactions from modifying rows while the current transaction is running a read operation. The shared locks also block the statement from reading rows modified by other transactions until the other transaction is completed. The shared lock type determines when it will be released. Row locks are released before the next row is processed. Page locks are released when the next page is read, and table locks are released when the statement finishes.
• If READ_COMMITTED_SNAPSHOT is set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

When the READ_COMMITTED_SNAPSHOT database option is ON, you can use the READCOMMITTEDLOCK table hint to request shared locking instead of row versioning for individual statements in transactions running at the READ COMMITTED isolation level.

(强调)

更改数据库配置以将 READ_COMMITTED_SNAPSHOT 设置为 ON。

此外，尽量让您的事务尽可能短，并确保您在后台进程中提交事务(即每小时执行 10,000 次插入)，因为如果它从不提交，则选择将永远阻塞(默认情况下)设置)。