微信关注
在线联系

关于SQLServer事务隔离级别控制知识探讨

read_commit_快照是数据库级别的设置,如果打开它并提交事务隔离级别,那么它将使用行版本控制来呈现事务一致性,并且在语句启动时查看数据。

快照隔离级别还使用行版本控制在被打开时显示数据的事务一致性视图。这需要打开allow_shopasth_isolation数据库设置,并要求查询发出set事务隔离级别快照语句。

在两种快照的隔离级别里,其效果是读者不锁定制作人,制作人也不锁定读者。此外,读取器将无法从其他读取任何在轨数据或修改。

正如我已经提到的,这两种方法都利用行版本控制。当使用行版本控制时,sqlserver中的数据库引擎将维护受处理结果的影响的行的版本。利用行版本控制将:

1.消除读取事务上的共享锁。

2.减少阻塞。

3.增加数据修改所需的资源。增加temdb中的活动。

a.所有的数据库数据修改都有行版本控制

4.每个数据记录都会附加一个14字节的记录后缀。

上面的图表提到了几个不同的并发副作用,所以让我们来解释其中的每一个。这些影响在bol中被定义为并发效应:

脏读。 对同一行进行多次读取,不同读取之间的结果不同。当另一个事务修改并提交对行的更改时,就会发生这种情况。虽然类似于脏读,但不同在于,在不可重复读取中,写入事务已成功提交事务,而在脏读中,写入事务被回滚。这种效应 在数据读取完成之前,要防止数据的更改。幻读当正在读取数据的事务正在读取一系列数据和另一个数据处理时,就会发生幻取。在数据里插入或删除行。如果将再次发布读取事务的语句,则将返回额外的行,或更少的行返回。可以通过在读取数据时阻止事务插入或删除数据来避免这种影响。

误读、重复读出现在

索引扫描操作中读取一系列行,并在读取期间由第二事务更新一行,从而更改索引键列及其在扫描中的位置。如果需要更新,将一行从扫描结束移动到开始,读取事务可能错过读取该行;

反之,如果更新将行从扫描开始移动到末尾,如果读取未提交隔离级别中的读取事务执行分配顺序扫描,而另一个事务导致page split,则可以读取该行两次。

当这些效果时,应该能够看到,为了努力防止任何这些并发效果时,就会在在数据库创造更多锁。

让我们运行一些例子,看看这些不同的并发效应如何在不同的事务隔离级别中表现出来。所有这些示例都是通过使用两个查询窗口来工作的:一个将用于在运行时读事务,而另一个运行写事务。查询利用 waitfordere 来给一点时间来启动一个事务,然后切换到另一个查询窗口来运行另一个窗口。

首先是数据库初始化代码。此代码需要在运行每个测试之前运行。它被放入一个存储过程中,以便它可以在必要的时候运行。

ifdb_idisnotnullbegin

useisolationleveltest;

alterdatabaseisolationleveltest setsingle_user withrollbackimmediate;

usemaster;

dropdatabaseisolationleveltest;end;createdatabaseisolationleveltest;

gouseisolationleveltest;

go

createproceduredbo.db_reset asifobject_idisnotnulldroptabledbo.isolationtests;createtabledbo.isolationtests );insertintodbo.isolationtestsselect a unionallselect a unionallselect a unionallselect a unionallselect a unionallselect a unionallselect a

select*fromdbo.isolationtests;

ifexistsandsnapshot_isolation_state =1)

alterdatabaseisolationleveltest setallow_snapshot_isolation off;

goexecutedbo.db_reset;

go

在读取未提交的隔离级别中,我们将研究如何允许脏读。这将通过在执行updat的一个查询窗口中启动事务来执行,并同时在第二个查询窗口中运行未提交读事务隔离级别上的select语句,以便查询将读取正在修改的数据。一段时间后 ,第一个查询窗口中的事务将回滚。将看到第二个查询窗口返回了从未提交到表的数据。

在第一个查询窗口中,我们运行以下语句:

useisolationleveltest;

goexecutedbo.db_reset;

go

begintransaction;updatedbo.isolationtestssetcola = y --simulate having some intensive processing here with a waitwaitfordelay 00:00:10 rollback;

在第二个查询窗口中,我们运行:

useisolationleveltest;

go-- read committed-- run this in query window 2 while the 1st query is runningsettransactionisolation level readcommitted;select*fromdbo.isolationtests;

如此所见,查询窗口2中的语句必须等待查询窗口1中的事务完成后才能运行,查询窗口2在查询窗口后返回表中的值。其中查询窗口1是完整的,包含全部内容的.

对于下一个隔离级别,可重复读取,我们将展示这个隔离级别中的事务是如何两次从表中读取数据的,其中有一段是在两次读取之间进行的,并将需要返回相同的数据。在这个隔离级别下,它必须读取与所读取的行完全相同的数据,因此它将阻塞试图执行更新的第二个事务。 对其中一些行进行分页。然后,我们将从可重复读取更改为已读提交,以显示允许运行更新的效果。在第一个查询窗口中,运行以下语句:

useisolationleveltest;

goexecutedbo.db_reset;

go

settransactionisolation level repeatable read;begintransaction;select*fromdbo.isolationtests;waitfordelay 00:00:10 select*fromisolationtests;rollback;

在第二个窗口,我们运行:

useisolationleveltest;

goupdatedbo.isolationtests setcol1 =-1;

请注意,查询窗口2等待查询窗口1完成,因为查询窗口1处于可重复读取状态。

运行代码。在查询窗口2中运行代码。

注意,查询窗口2立即完成,在查询窗口1中,第二个select语句返回与第一个选择不同的结果声明。

在刚才执行的可重复读取测试中,我们看到了如何防止对数据的更新。可序列化的隔离级别更进一步,还可以防止插入或删除。 发生在这张桌子上。为了测试这一点,我们将从可重复读取中重新运行测试,并将隔离级别更改为可序列化,并尝试执行插入而不是 最新消息。然后,我们将在可重复读取隔离级别上运行此测试,展示如何允许insert运行。

在第一个查询窗口中,运行以下语句:

useisolationleveltest;

goexecutedbo.db_reset;

go

-- serializable-- run this in query window 1settransactionisolation level serializable;--set transaction isolation level repeatable read; -- what will happen if this is set insteadbegintransaction;select*fromdbo.isolationtests;waitfordelay 00:00:10 select*fromdbo.isolationtests;rollback;

在第二个查询窗口中,我们运行:

useisolationleveltest;

goinsertintodbo.isolationtestsvalues也这样做了。在这个测试中,我们将首先显示快照隔离级别中没有锁定,然后显示缺失。现在,在使用可序列化隔离级别时,这些语句将被锁定。

在第一个查询窗口中,运行以下语句:

useisolationleveltest;

goexecutedbo.db_reset;

go

-- snapshotalterdatabaseisolationleveltest setallow_snapshot_isolation on;

go-- run this in query window 1useisolationleveltest;

gosettransactionisolation level snapshot;begintransaction;select*fromdbo.isolationtests;waitfordelay 00:00:10 select*fromdbo.isolationtests;rollback;

在第二个查询窗口中,我们运行:

useisolationleveltest;

goinsertintodbo.isolationtestsvalues与指定select事务级别的readuncommitted相同。通过运行未提交的读代码可以看到这一点,对于查询窗口2,运行以下代码,而不是:

select*fromdbo.isolationtests with;

如果您决定实现快照隔离,并且您的当前代码正在使用nolock表提示,则这些指定的提示将具有优先级-您将需要更改代码,以获得利用快照隔离级别的优势。

在iso隔离级别下,当我们更改查询运行的隔离级别时,除了sql server默认的read提交之外,我们还可以减少锁时,应该切换到使用读取提交的快照隔离级别,以实现为该查询实现的无阻塞。


HOME |  ABOUT US |  PORTFOLIO |  NEWS |  CONTACT US

Copyright © 2018 918博天堂网址918博天堂网址-918博天堂官网 All Rights Reserved Power by DedeCms


CONTACT US
+86 02888888888