设置ALLOW_SNAPSHOT_ISOLATION ON的意义是什么?[英] What are implications of SET-ting ALLOW_SNAPSHOT_ISOLATION ON?

本文是小编为大家收集整理的关于设置ALLOW_SNAPSHOT_ISOLATION ON的意义是什么?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我应该运行

ALTER DATABASE DbName SET ALLOW_SNAPSHOT_ISOLATION OFF

如果不临时使用快照事务(TX)隔离(ISO)?
换句话说,

  • 为什么首先要启用它?
  • 为什么默认不启用它?

在SQL Server中启用(但未暂时使用)的成本是多少?


- 更新:
数据库上启用快照TX ISO级别不会更改读取的tx ISO默认值.
您可以通过运行:

检查它
use someDbName;
--( 1 )
alter database someDbName set allow_snapshot_isolation ON;
dbcc useroptions;

最后一行显示当前会话的TX ISO级别(读取合作).

因此,启用快照tx ISO级别而不更改为它不使用它,等等 为了使用它,应该发行

--( 2 )
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

update2:
我重复了[1]的脚本,但是启用了快照(但未打开),但不启用read_committed_snapshot

--with enabling allow_snapshot_isolation
alter database snapshottest set allow_snapshot_isolation ON

-- but without enabling read_committed_snapshot
--alter database snapshottest set read_committed_snapshot ON
-- OR with OFF
alter database snapshottest set read_committed_snapshot OFF 
go

没有执行的结果/行

select * from sys.dm_tran_version_store

执行插入后,删除或更新

您能为我提供脚本,说明启用快照TX ISO级别以(1)的范围,但不开启(2)在tempdb中产生任何版本和/或增加数据大小,每行14个字节?
真的,我不明白版本控制是否由(1)启用但未使用(未使用(2)设置)?

[1]
在SQL Server中管理tempdb:tempdb基础知识(版本存储:简单示例)
链接

推荐答案

在数据库中启用了行版本(又称快照)后,所有写入都必须版本.在写入的隔离水平下,隔离级别始终影响仅读取,这并不重要.一旦启用了数据库行版本控制,任何插入/update/delete都会:

  • 每行14个字节增加数据大小
  • 可能在版本存储(tempdb)中的更新之前创建数据图像

再次,完全不相关地使用了什么隔离水平.请注意,如果以下任何一个是正确的,则行版本也会发生:

  • 表有触发器
  • 在连接上启用了火星
  • 在线索引操作在表上运行

所有这些都在行版本resource resource usage :

每个数据库行最多可以使用14 行末尾的字节 版本控制信息.行 版本控制信息包含 交易序列编号 承诺版本的交易 和指向版本的行的指针. 这14个字节添加了第一个字节 时间修改时间,或者 在 下插入新行 这些条件:

  • READ_COMMENTED_SNAPSHOT或ally_snapshot_isolation选项是
  • 表有一个触发因素.
  • 使用多个活动结果集(火星).
  • 在线索引构建操作当前在表上运行.

...

行版本必须存储为 只要主动交易需要 访问它. ...如果遇到 以下条件:

  • 它使用基于行版本的隔离.
  • 它使用触发器,火星或在线索引构建操作.
  • 它生成行版本.

更新

:setvar dbname testsnapshot

use master;

if db_id('$(dbname)') is not null
begin
    alter database [$(dbname)] set single_user with rollback immediate;
    drop database [$(dbname)];
end
go

create database [$(dbname)];
go

use [$(dbname)];
go


-- create a table before row versioning is enabled
--
create table t1 (i int not null);
go
insert into t1(i) values (1);
go

-- this check will show that the records do not contain a version number
--
select avg_record_size_in_bytes 
from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
-- record size: 11 (lacks version info that is at least 14 bytes)


-- enable row versioning and and create an identical table
--
alter database [$(dbname)] set allow_snapshot_isolation on;
go

create table t2 (i int not null);
go

set transaction isolation level read committed;
go

insert into t2(i) values (1);
go

-- This check shows that the rows in t2 have version number
--
select avg_record_size_in_bytes
     from sys.dm_db_index_physical_stats (db_id(), object_id('t2'), NULL, NULL, 'DETAILED')
-- record size: 25 (11+14)

-- this update will show that the version store has records
-- even though the isolation level is read commited
--
begin transaction;
update t1 
    set i += 1; 
select * from sys.dm_tran_version_store;
commit;
go

-- And if we check again the row size of t1, its rows now have a version number
select avg_record_size_in_bytes
from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
-- record size: 25

其他推荐答案

默认情况下,您可以关闭快照隔离,如果将其打开,SQL将维护数据快照以进行运行交易. 示例:在连接1上,您正在运行大型选择.在Connection 2上,您更新了一些将由首先选择返回的记录.

在快照隔离中,SQL将制作数据的临时副本,受到更新的影响,因此SELECT将返回原始数据.

任何其他数据操作都会影响性能.这就是为什么默认情况下此设置的原因.

本文地址:https://www.itbaoku.cn/post/597556.html

问题描述

Should I run

ALTER DATABASE DbName SET ALLOW_SNAPSHOT_ISOLATION OFF

if snapshot transaction (TX) isolation (iso) is not temporarily used?
In other words,

  • why should it be enabled, in first place?
  • Why isn't it enabled by default?

What is the cost of having it enabled (but temporarily not used) in SQL Server?


--Update:
enabling of snapshot TX iso level on database does not change READ COMMITTED tx iso to be default.
You may check it by running:

use someDbName;
--( 1 )
alter database someDbName set allow_snapshot_isolation ON;
dbcc useroptions;

the last row shows that tx iso level of current session is (read committed).

So, enabling snapshot tx iso level without changing to it does not use it, etc In order to use it one should issue

--( 2 )
SET TRANSACTION ISOLATION LEVEL SNAPSHOT

Update2:
I repeat the scripts from [1] but with SNAPSHOT enabled (but not switched on) but without enabling READ_COMMITTED_SNAPSHOT

--with enabling allow_snapshot_isolation
alter database snapshottest set allow_snapshot_isolation ON

-- but without enabling read_committed_snapshot
--alter database snapshottest set read_committed_snapshot ON
-- OR with OFF
alter database snapshottest set read_committed_snapshot OFF 
go

There no results/rows from from executing

select * from sys.dm_tran_version_store

after executing INSERT, DELETE or UPDATE

Can you provide me with scripts illustrating that enabled SNAPSHOT tx iso level by ( 1 ) but not switched on by ( 2 ) produces any versions in tempdb and/or increase the size of data with 14 bytes per row?
Really I do not understand what is the point in versioning if it is enabled by ( 1 ) but not used (not set on by ( 2))?

[1]
Managing TempDB in SQL Server: TempDB Basics (Version Store: Simple Example)
Link

推荐答案

As soon as row versioning (aka. snapshot) is enabled in the database all writes have to be versioned. It doesn't matter under what isolation level the write occurred, since isolation levels always affect only reads. As soon the database row versioning is enabled, any insert/update/delete will:

  • increase the size of data with 14 bytes per row
  • possibly create an image of the data before the update in the version store (tempdb)

Again, it is completely irrelevant what isolation level is used. Note that row versioning occurs also if any of the following is true:

  • table has a trigger
  • MARS is enabled on the connection
  • Online index operation is running on the table

All this is explained in Row Versioning Resource Usage:

Each database row may use up to 14 bytes at the end of the row for row versioning information. The row versioning information contains the transaction sequence number of the transaction that committed the version and the pointer to the versioned row. These 14 bytes are added the first time the row is modified, or when a new row is inserted, under any of these conditions:

  • READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION options are ON.
  • The table has a trigger.
  • Multiple Active Results Sets (MARS) is being used.
  • Online index build operations are currently running on the table.

...

Row versions must be stored for as long as an active transaction needs to access it. ... if it meets any of the following conditions:

  • It uses row versioning-based isolation.
  • It uses triggers, MARS, or online index build operations.
  • It generates row versions.

Update

:setvar dbname testsnapshot

use master;

if db_id('$(dbname)') is not null
begin
    alter database [$(dbname)] set single_user with rollback immediate;
    drop database [$(dbname)];
end
go

create database [$(dbname)];
go

use [$(dbname)];
go


-- create a table before row versioning is enabled
--
create table t1 (i int not null);
go
insert into t1(i) values (1);
go

-- this check will show that the records do not contain a version number
--
select avg_record_size_in_bytes 
from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
-- record size: 11 (lacks version info that is at least 14 bytes)


-- enable row versioning and and create an identical table
--
alter database [$(dbname)] set allow_snapshot_isolation on;
go

create table t2 (i int not null);
go

set transaction isolation level read committed;
go

insert into t2(i) values (1);
go

-- This check shows that the rows in t2 have version number
--
select avg_record_size_in_bytes
     from sys.dm_db_index_physical_stats (db_id(), object_id('t2'), NULL, NULL, 'DETAILED')
-- record size: 25 (11+14)

-- this update will show that the version store has records
-- even though the isolation level is read commited
--
begin transaction;
update t1 
    set i += 1; 
select * from sys.dm_tran_version_store;
commit;
go

-- And if we check again the row size of t1, its rows now have a version number
select avg_record_size_in_bytes
from sys.dm_db_index_physical_stats (db_id(), object_id('t1'), NULL, NULL, 'DETAILED')
-- record size: 25

其他推荐答案

By default, you have snapshot isolation OFF, If you turn it ON, SQL will maintain snapshots of data for running transactions. Example: On connection 1, you are running big select. On connection 2, you update some of the records that are going to be returned by first select.

In snapshot isolation ON, SQL will make a temporary copy of the data, affected by update, so SELECT will return original data.

Any additional data manipulation will affect performance. That's why this setting is OFF by default.