默认SQL Server隔离级别更改
我们有一位客户遇到了我们的数据库应用程序出现的一些阻塞问题。我们让他们运行阻塞进程报告跟踪,他们给我们的跟踪显示阻塞发生在SELECT和UPDATE操作之间。跟踪文件显示如下:默认SQL Server隔离级别更改
- 相同的SELECT查询正在不同的隔离级别执行。一条曲线显示一个Serializable IsolationLevel,而后面的曲线显示一个RepeatableRead IsolationLevel。执行查询时我们不使用显式事务。
- 正在使用RepeatableRead隔离级别执行UPDATE查询,但被SELECT查询阻止。这是预期的,因为我们的更新包含在一个明确的事务中,并且带有IsolationLevel的RepeatableRead。
所以基本上我们是亏本,为什么SELECT查询的隔离级别不会是默认的READCOMMITTED的IsolationLevel但是,更令人困惑的是,为什么查询的IsolationLevel会随时间而改变?只有一位客户看到这种行为,所以我们怀疑它可能是数据库配置问题。
任何想法?
由于提前,
格雷厄姆
在你的情况,我建议明确设置隔离级别快照 - 这将阻止通过防止锁获得在写入(插入和更新)的方式读,但那些读取仍然是“好”的读取(即不是脏数据 - 它不是一样的NOLOCK)
通常我发现,我有我的查询锁定问题,我手动控制应用的锁。例如我会使用行级锁执行更新以避免页/表级锁定,并将我的读取设置为readpast(接受我可能会错过某些数据,在某些情况下可能会好) 链接|编辑|删除|标记
EDIT--综合所有的评论到答案
作为优化过程的一部分,SQL服务器避免越来越COMMITED读取它知道没有改变一个页面上,并自动回落到较低的锁定策略。在你的情况下,sql server从可串行读取下降到可重复读取。
问:感谢有关降低隔离级别的有用信息。你能想到它首先会使用Serializable IsolationLevel的任何理由,因为我们不使用SELECT的显式事务 - 我们知道隐式事务将使用ReadCommitted?
答:默认情况下,SQL Server将使用Read Commmited(如果这是您的默认隔离级别),但如果您没有在查询中额外指定锁定策略,则基本上是对sql服务器说:“做你认为最好的,但我的首选是Read Commited“。由于SQL Server可以*选择,因此它可以优化查询。 (sql server中的优化算法非常复杂,我自己也不完全理解它)。在事务中不显式执行不会影响sql server使用的隔离级别。
问:最后一件事,SQL Server是否会增加隔离级别(可能需要锁的数量)以优化查询似乎是合理的?我也想知道,如果它继承了上次使用的隔离级别,重用连接池是否会影响这一点?
答:Sql服务器将会这样做,称为“锁定升级”的过程的一部分。从http://support.microsoft.com/kb/323630,我引用:“Microsoft SQL Server动态地决定何时执行锁定升级。当做出这个决定时,SQL Server会考虑在特定扫描中保留的锁的数量,整个锁的数量事务,以及整个系统中用于锁定的内存。通常,SQL Server的默认行为会导致锁定升级,这些锁定升级仅发生在可以提高性能的那些点上,或者必须将过多的系统锁内存减少到更多但是,某些应用程序或查询设计可能会在不需要的时候触发锁升级,并且升级的表锁可能会阻止其他用户“。
尽管锁升级与改变隔离级别并不完全相同,但是查询运行得很好,这让我感到惊讶,因为我不希望sql server比默认隔离级别允许的更多的锁。
有关SQL为什么会通过逐步升级需要更多锁的更多信息:这是不正确的,升级会减少(而不是增加)所需锁的数量。表锁是一个单一的锁,而所有的页锁或行锁都需要从较低的级别进行。锁定升级始终是由于一个原因:取得更高级别锁定比锁定所有更低级别对象更有效
例如,可能没有可用的索引来有效锁定。即如果您在字段中的2010年所有记录上使用UPDLOCK进行计数,并且该日期字段中没有索引,则这将需要2010年每条记录上的行锁定,如果记录中有多条记录,则该锁定效率不高,并且页面锁定也无济于事,因为它们大概是在页面中随机分布的,所以SQL需要一个表锁。此外,SQL还必须锁定其他记录,以便在UPDLOCK保持不变的情况下更改为2010年,并且此字段上没有索引来执行范围锁定,SQL没有选择,只能采取表锁来防止发生这种情况。最后一点是那些经常被优化的人忽略的一点:认识到SQL还必须“保护”已经在事务中执行的查询的完整性。
没问题灰。感谢有关降低隔离级别的有用信息。你能想到它首先会使用Serializable IsolationLevel的任何理由,因为我们不使用SELECT的显式事务 - 我们知道隐式事务将使用ReadCommitted? – Graham 2009-08-27 07:38:03
谢谢阿什。如果您将您的评论与答案结合起来,我会将其标记为答案并为您投票。 – Graham 2009-08-27 13:34:40
最后一件事,SQL Server是否会增加隔离级别(并且可能是所需的锁的数量)来优化查询似乎是合理的?我也想知道,如果它继承了上次使用的隔离级别,重用连接池是否会影响这一点? – Graham 2009-08-27 13:48:22