|
我正在开发一个Web应用程序,目前正在使用sql server 2008.但是,我正在考虑转移到另一个数据库(simpledb)以提高性能.
我有一个后台进程,每小时最多10000行插入一个特定的表.该表也被读取以显示Web应用程序中的数据.后台进程运行时,Web应用程序无法使用,因为数据库连接超时.
因此,我正在考虑转向亚马逊的simpledb来提高性能.亚马逊的SimpleDB是否针对此用例进行了优化？如果没有,我可以使用另一种解决方案吗？
解决方法
您的问题是您正在使用的隔离级别.除非您更改它,否则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次插入),因为如果它从未提交,则选择将永久阻止(在默认设置下). （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
