为所有查询设置快照隔离级别 [英] Set Snapshot isolation level for all queries

问题描述

我们目前遇到与SQL Server默认隔离级别相关的性能问题，因此我们执行以下操作

- 在数据库级别  ALTER DATABASE  SET ALLOW_SNAPSHOT_ISOLATION ON（没关系）

- 在hibernate的配置文件中，我们使用以下标记  < < span style ="color：＃800000"> property
name ="hibernate。 connection.isolation"> 4096< / property>

但是当我想看哪个 隔离级别使用以下查询（ https://blog.sqlauthority.com/2018/06/07/sql-server-how-to-know-transaction-isolation-level-for-each-session/
） 

SELECT session_id，start_time，status，

total_elapsed_time，

CASE transaction_isolation_level

WHEN 1 THEN 'ReadUncomitted'

WHEN 2那么'ReadCommitted'

WHEN 3那么'可重复'

当4那么'Serializable'¥ b $ b WHEN 5那么'快照'

ELSE'未指定'END AS transaction_isolation_level，

sh.text，ph.query_plan

FROM sys .dm_exec_requests

CROSS APPLY sys.dm_exec_sql_text（sql_handle）sh

CROSS APPLY sys.dm_exec_query_plan（plan_handle）ph

它显示我  ; ReadCommitted。

任何帮助设置hibernate使用snapsho t隔离级别？

问候，

---

解决方案

这是正确的。你必须明确地在查询开头设置它，你应该这样做

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

你可以在你的ORM。

https://blog.grogscave.net/2010/10/using-snapshot-isolation-with-sql.html

Hi,

We presently have performances issues related to the default isolation level for SQL Server, So we do the following

- At database level  ALTER DATABASE  SET ALLOW_SNAPSHOT_ISOLATION ON (that is ok)

- in the config file of hibernate we use the following tag <property name="hibernate.connection.isolation">4096</property>

But When I want to see which  isolation level using the following query (https://blog.sqlauthority.com/2018/06/07/sql-server-how-to-know-transaction-isolation-level-for-each-session/ ) 

SELECT session_id, start_time, status,
total_elapsed_time,
CASE transaction_isolation_level
WHEN 1 THEN 'ReadUncomitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
ELSE 'Unspecified' END AS transaction_isolation_level,
sh.text, ph.query_plan
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) sh
CROSS APPLY sys.dm_exec_query_plan(plan_handle) ph

It shows me ReadCommitted.

Any help to set the hibernate to use the snapshot isolation level?

Regards,

---

解决方案

That is correct. you must explicity set it on at the beginning of your query, you should do this

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

You can do this in your ORM.

https://blog.grogscave.net/2010/10/using-snapshot-isolation-with-sql.html

这篇关于为所有查询设置快照隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！