SQL Server误区30日谈 第26天 SQL Server中存在真正的“事务嵌套”

误区 #26: SQL Server中存在真正的“事务嵌套”
错误

嵌套事务可不会像其语法表现的那样看起来允许事务嵌套。我真不知道为什么有人会这样写代码，我唯一能够想到的就是某个哥们对SQL Server社区嗤之以鼻然后写了这样的代码说：“玩玩你们”。
    让我更详细的解释一下，SQL Server允许你在一个事务中开启嵌套另一个事务,SQL Server允许你提交这个嵌套事务，也允许你回滚这个事务。
    但是，嵌套事务并不是真正的“嵌套”，对于嵌套事务来说SQL Server仅仅能够识别外层的事务。嵌套事务是日志不正常增长的罪魁祸首之一因为开发人员以为回滚了内层事务，仅仅是回滚内层事务。
    但实际上当回滚内层事务时，会回滚整个事务，而不是仅仅是内层。这也是为什么我说嵌套事务并不存在。
    所以作为开发人员来讲，永远不要对事务进行嵌套。事务嵌套是邪恶的。
    如果你不相信我说的，那么通过下面的例子就就会相信。创建完数据库和表之后，每一条记录都会导致日志增加8K。

代码如下:

CREATE DATABASE NestedXactsAreNotReal;
GO
USE NestedXactsAreNotReal;
GO
ALTER DATABASE NestedXactsAreNotReal SET RECOVERY SIMPLE;
GO
CREATE TABLE t1 (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');
CREATE CLUSTERED INDEX t1c1 ON t1 (c1);
GO
SET NOCOUNT ON;
GO

测试 #1：回滚内部事务时仅仅回滚内部事务?


代码如下:

BEGIN TRAN OuterTran;
GO
INSERT INTO t1 DEFAULT Values;
GO 1000
BEGIN TRAN InnerTran;
GO
INSERT INTO t1 DEFAULT Values;
GO 1000
SELECT @@TRANCOUNT, COUNT (*) FROM t1;
GO

你可以看到得出的结果是2和2000，下面我来回滚内部的事务，按照我们的猜想应该只回滚1000条吧，但事实上你会得到如下结果:


代码如下:

ROLLBACK TRAN InnerTran;
GO

代码如下:

消息 6401，级别 16，状态 1，第 2 行
无法回滚 InnerTran。找不到该名称的事务或保存点。

好吧，由Books Online来看,我只能使用外部事务的名称或是将事务名称留空来进行回滚，代码如下:


代码如下:

ROLLBACK TRAN;
GO
SELECT @@TRANCOUNT, COUNT (*) FROM t1;
GO

现在我得到结果是0和0。正如Books Online所言,这个回滚操作将外部事务进行了回滚并将全局变量@@TRANCOUNT设置为0。事务中所有的修改都被回滚，如果想部分回滚的话只能使用SAVE TRAN 和ROLLBACK TRAN。
测试 #2：嵌套事务中内部事务提交后会保存内部事务的修改吗？


代码如下:

BEGIN TRAN OuterTran;
GO
BEGIN TRAN InnerTran;
GO
INSERT INTO t1 DEFAULT Values;
GO 1000
COMMIT TRAN InnerTran;
GO
SELECT COUNT (*) FROM t1;
GO

正如我所期待，得到的结果是1000。这说明内部事务提交是会修改到磁盘的。但是如果这时外部事务回滚的话，那么不应该回滚内部事务…


代码如下:

ROLLBACK TRAN OuterTran;
GO
SELECT COUNT (*) FROM t1;
GO

但运行上面查询后结果是0，这说明外部事务的回滚会影响内部事务。

测试 #3：提交嵌套的事务的内部事务至少可以让我清除日志吧。
在开始这个测试之前我首先清除了日志，然后运行如下代码：


代码如下:

BEGIN TRAN OuterTran;
GO
BEGIN TRAN InnerTran;
GO
INSERT INTO t1 DEFAULT Values;
GO 1000
DBCC SQLPERF ('LOGSPACE');
GO

得到结果:

下面我将事务提交后运行CheckPoint(对于简单恢复模式的数据库将会截断日志),得到的结果:


代码如下:

COMMIT TRAN InnerTran;
GO
CHECKPOINT;
GO
DBCC SQLPERF ('LOGSPACE');
GO

我们发现日志的使用不减反赠，这是由于日志写入了CheckPoint记录（详情请看:How do checkpoints work and what gets logged）。提交内部事务不会导致日志被清除，这是由于外部事务回滚时也会连同内部事务一起回滚（译者注：所以这部分VLF在外部事务提交之前永远不会被标记位reusable）。所以这部分日志在外部事务提交之前永远不会被截断。为了证明这一点，我提交外部事务，然后再来看日志：


代码如下:

COMMIT TRAN OuterTran;
GO
CHECKPOINT;
GO
DBCC SQLPERF ('LOGSPACE');
GO


么样，日志使用百分比大幅下降了吧。
对于嵌套事务来说---Just Say no。(这句话你可以当作来自SQLSkill.com的一个热心的家伙给的福利:-)

赞 (0)

相关推荐

  • SQL Server误区30日谈 第4天 DDL触发器就是INSTEAD OF触发器

    误区 #4: DDL触发器(SQL Server 2005之后被引入)就是INSTEAD OF触发器 这是错误的     DDL触发器的实现原理其实就是一个AFTER触发器.这个意思是先发生DDL操作,然后触发器再捕捉操作(当然如果你在触发器内写了Rollback,则也可能回滚). 存在Rollback也意味着这个触发器并不像你想象的那么轻量,来看下面的例子: ALTER TABLE MyBigTable ADD MyNewNonNullColumn VARCHAR (20) DEFAULT '

  • SQL Server误区30日谈 第22天 资源调控器可以调控IO

    误区 #22: 资源调控器可以调控IO错误    资源调控器无法调控IO,希望下一个版本的SQL Server支持调控IO,调控IO对于对于减少对于大表的scan操作带来的性能影响很有帮助.    下面列表中的功能资源调控器同样也无法完成: 调控Buffer Pool的内存,内存调控器仅仅可以调控执行计划所占的内存,但对于Buffer Pool中缓存的数据页是无法调控的 可以对多个实例进行当作一个逻辑实体进行资源调控.这是不能的,对于多实例的资源调控只能通过Windows Server资源调控器

  • SQL Server误区30日谈 第18天 有关FileStream的存储，垃圾回收以及其它

    误区 #18:如下多个有关FileStream的误区 全部错误 18 a)FileStream数据可以在远程存储 不能,由于FileStream数据容器(指的是存放FileStream文件的NTFS文件夹,杜撰出来的术语)必须像数据文件或日志文件那样符合本地存储策略-也就是说,这个数据容器必须放在对于运行SQL Server的Windows Server是本地存储(译者注:也就是在'计算机'里能看到的存储,DAC当然是了,其实SAN这类不直接连接服务器的也算是)访问FileStream数据只要客

  • SQL Server误区30日谈 第19天 Truncate表的操作不会被记录到日志

    SQL Server误区30日谈 第19天 Truncate表的操作不会被记录到日志

    误区 #19:Truncate表的操作不会被记录到日志 错误 在用户表中的操作都会被记录到日志.在SQL Server中唯一不会被记录到日志的操作是TempDB中的行版本控制. Truncate Table语句会将整个表中的所有数据删除.但删除的方式并不是一行一行的删除,而是将组成表的数据页释放,将组成表的相关页释放的操作交给一个后台的线程进行队列处理的过程被称为deferred-drop.使用后台线程处理deferred-drop的好处是这个操作不会使得其所在的事务需要执行很长时间,因此也就不

  • SQL Server误区30日谈 第25天 有关填充因子的误区

    误区 #25:多个有关填充因子的误区    都是错误的 25a) 填充因子是一直存在的    不是的,通过Books Online可以看到(译者:我在新版的BOL没有找到这句话):重要: 填充因子仅仅在索引创建或重建时生效,SQL Server存储引擎并不会一直保证页内的空闲值和填充因子保持一致.如果为了保证页内的空余值和指定的填充因子保持一直那么填充因子就会失去意义.因为这时页即使不满也需要进行分页. 25 b)填充因子0和100是不同的    错误,由BOL的一句话可以看到    填充因子0

  • SQL Server误区30日谈 第17天 有关页校验和的误区

    其实我之前已经有文章详细解释了页校验和:How to tell if the IO subsystem is causing corruptions? 误区 #17:几个有关页校验和的误区 坊间流传的基本是错误的 17 a)页校验和(Page CheckSum)在从SQL Server 2000或7.0升级上来之后自动开启 其实不是,从旧的实例升级上来的数据库不会自动开启页校验和,除非你显式使用ALTER DATABASE databasename SET PAGE_VERIFY CHECKSU

  • SQL Server误区30日谈 第11天 镜像在检测到故障后瞬间就能故障转移

    误区 #11:镜像在检测到故障后瞬间就能故障转移 错误 数据库镜像的故障转移既可以自动发起,也可以手动发起. 在自动发起的情况下,是由镜像服务器执行故障转移操作(你没有看错,并不是由见证服务器来做故障转移的决定),在见证服务器和镜像服务器都发现无法和主体服务器交换信息(这个过程被称为"形成仲裁",译者注:也就是通过程序对集群进行监管,集群可用的依据来自监管程序的算法,比如根据:每个节点的配置,文件共享情况,磁盘访问情况,每个节点的可用性等来确定集群是否可用)并且镜像方式是同步时,可以进

  • SQL Server误区30日谈 第16天 数据的损坏和修复

    误区 #16:多个关于数据的损坏和修复误区 坊间流传的很多版本都不正确 我已经听过很多关于数据修复可以做什么.不可以做什么.什么会导致数据损坏以及损坏是否可以自行消失.其实我已经针对这类问题写过多篇博文,因此本篇博文可以作为"流言终结者"来做一个总结,希望你能有收获. 首先,对于数据修复可以做什么,不可以做什么,我已经写过一篇博文Misconceptions around database repair涵盖了13个误区-从不用DBCC CHECKDB是否能修复错误(当然不能)到REPA

  • SQL Server误区30日谈 第9天 数据库文件收缩不会影响性能

    误区 #9: 数据库文件收缩不会影响性能 错误! 收缩数据库文件唯一不影响性能的情况是文件末尾有剩余空间的情况下,收缩文件指定了TruncateOnly选项. 收缩文件的过程非常影响性能,这个过程需要移动大量数据从而造成大量IO,这个过程会被记录到日志从而造成日志暴涨,相应的,还会占去大量的CPU资源. 不仅在收缩的过程中影响性能,并且在文件收缩之后同样影响应能,收缩产生的大量日志会被事务日志传送,镜像,复制能操作重复执行.而空间不够时,文件还需要填0初始化从而影响性能(除非你开启的不用填零初始

  • SQL Server误区30日谈 第20天 破坏日志备份链之后，需要一个完整备份来重新开始日志链

    误区 #20:在破坏日志备份链之后,需要一个完整备份来重新开始日志链 错误 事务日志备份会备份自上次事务日志备份以来所有的事务日志(如果从来没有过日志备份的话,那就从上一次完整备份开始).有好几种类型的操作会中断事务日志的连续性,也就是说除非重新开始新的日志链,SQL Server无法再进行日志备份.下面这几种操作都有可能引起日志链断裂: 由完整恢复模式或大容量事务日志恢复模式转为简单恢复模式 从数据库镜像进行恢复 备份日志时指定了NO_LOG 或 WITH TRUNCATE_ONLY(还好在S

  • SQL Server误区30日谈 第2天 DBCC CHECKDB会导致阻塞

    误区 #2: DBCC CHECKDB会引起阻塞,因为这个命令默认会加锁 这是错误的! 在SQL Server 7.0以及之前的版本中,DBCC CHECKDB命令的本质是C语言实现的一个不断嵌套循环的代码并对表加表锁(循环嵌套算法时间复杂度是嵌套次数的N次方,作为程序员的你懂得),这种方式并不和谐,并且-.. 在SQL Server 2000时代,一个叫Steve Lindell的哥们(现在仍然在SQL Server Team)使用分析事务日志的方法来检查数据库的一致性的方式重写了DBCC C

  • SQL Server误区30日谈 第13天 在SQL Server 2000兼容模式下不能使用DMV

    误区 #13.在SQL Server 2000兼容模式下不能使用DMV 错误       对于兼容模式已经存在了很多误解.80的兼容模式的数据库是否意味着能够附加或恢复到SQL Server 2000数据库?当然不是.这只是意味着一些T-SQL的语法,查询计划的行为以及一些其它方面和SQL Server 2000中行为一样(当然,如果你设置成90兼容模式则和SQL Server 2005中一样). 在SQL Server 2008中,你可以使用ALTER DATABASE SET COMPATI

  • SQL Server误区30日谈 第6天 有关NULL位图的三个误区

    这样还能减少CPU缓存命中失效的问题(点击这个链接来查看CPU的缓存是如何工作的以及MESI协议).下面让我们来揭穿三个有关NULL位图的普遍误区. 误区 #6a:NULL位图并不是任何时候都会用到 正确 就算表中不存在允许NULL的列,NULL位图对于数据行来说会一直存在(数据行指的是堆或是聚集索引的叶子节点).但对于索引行来说(所谓的索引行也就是聚集索引和非聚集索引的非叶子节点以及非聚集索引的叶子节点)NULL位图就不是一直有效了. 下面这条语句可以有效的证明这一点: 复制代码 代码如下:

  • SQL Server误区30日谈 第8天 有关对索引进行在线操作的误区

    SQL Server误区30日谈 第8天 有关对索引进行在线操作的误区

    误区 #8: 在线索引操作不会使得相关的索引加锁 错误!     在线索引操作并不是想象的那么美好. 在线索引操作会在操作开始时和操作结束时对资源上短暂的锁.这有可能导致严重的阻塞问题. 在线索引操作开始时,会在被整理的资源上加一个共享的表锁,这个表锁在会在新的索引创建时.老索引进行版本扫描时一直持续. 但问题是,这个S锁会和表上的其它锁排成锁队列.这也就是意味着和S锁不兼容的其它锁在表上存在S锁或是表上的锁队列存在中包含S锁时,这类和S锁不兼容的锁操作也需要等待.这也意味着各种更新操作会被阻塞

  • SQL Server误区30日谈 第3天 即时文件初始化特性可以在SQL Server中开启和关闭

    SQL Server误区30日谈 第3天 即时文件初始化特性可以在SQL Server中开启和关闭

    本系列文章是我在sqlskill.com的PAUL的博客看到的,很多误区都比较具有典型性和代表性,原文来自T-SQL Tuesday #11: Misconceptions about.... EVERYTHING!!,经过我们团队的翻译和整理发布在AgileSharp和博客园上.希望对大家有所帮助. 误区 #3: 即时文件初始化特性可以在SQL Server中 a)开启 和 b)关闭 a)是不允许的  b)是允许的 即时文件初始化是一个在SQL Server 2005以及之上的版本鲜为人知的特

  • SQL Server误区30日谈 第28天 有关大容量事务日志恢复模式的误区

    误区 #28:有关大容量事务日志恢复模式的几个误区 28 a)常见的DML操作可以被"最小记录日志"    不是.在大容量事务日志恢复模式下只有一小部分批量操作可以被"最小记录日志",这类操作的列表可以在Operations That Can Be MinimallyLogged找到.这是适合SQL Server 2008的列表,对于不同的SQL Server版本,请确保查看正确的列表. 28 b)使用大容量事务日志恢复模式不会影响灾难恢复    首先,在上次事务日

  • SQL Server误区30日谈 第12天 TempDB的文件数和需要和CPU数目保持一致

    误区 #12:TempDB的文件数和需要和CPU数目保持一致 错误 哎,由于上述误区是微软"官方"的建议,并且还有大量博文坚持这个观点,这个误区已经是老生常谈. 但让人困惑的是SQL CAT团队给出的建议就是1:1,但这个建议是源自扩展方面的原理来说,而不是一个通用法则.因为他们所面对的大型客户数据量服务器和IO子系统都是大部分人没有机会遇到的. 每个实例仅仅允许有一个TempDb,但需要用到TempDB的地方却有很多,所以TempDB很容易成为性能瓶颈,我想大家数人都了解这一点,而大

  • SQL Server误区30日谈 第21天 数据损坏可以通过重启SQL Server来修复

    误区 #21:数据库损坏可以通过重启SQL Server或是Windows,或是附加和分离数据库解决 错误 SQL Server中没有任何一项操作可以修复数据损坏.损坏的页当然需要通过某种机制进行修复或是恢复-但绝不是通过重启动SQL Server,Windows亦或是分离附加数据库. 而实际上,如果你的数据库的损坏程度无法进行Crash Recovery的话(质疑状态),那么分离附加数据库将会是你做的最糟糕的决定.这个原理是由于附加数据库中包含Crash Recovery步骤,如果Crash

  • SQL Server误区30日谈 第14天 清除日志后会将相关的LSN填零初始化

    SQL Server误区30日谈 第14天 清除日志后会将相关的LSN填零初始化

    误区 #14.清除日志后会将相关的LSN填零初始化 错误     当日志文件在手动增长,自动增长和创建时都会进行填零初始化操作.但是请不要把这个过程和定期清除日志的过程搞混.日志截断仅仅意味着将一个或多个VLF标记为不活动以便被重复使用.在日志清除的过程中,并没有任何日志被清除或是填0."清除日志"和"截断日志"意思是一样的,但都属于用词不当,因为在这个过程中日志的大小不会有任何改变. 你可以在我的博客中看到有关日志文件填零初始化的博文:Search Engine

  • SQL Server误区30日谈 第10天 数据库镜像在故障发生后 马上就能发现

    误区10.数据库镜像在故障发生后,马上就能发现 错误 市面上大肆宣传数据库镜像技术可以在故障发生后,立即检测到错误并进行故障转移. 但事实并不是这样,检测到故障发生的速度要取决于故障的类型. 检测故障发生的最快的情况是,镜像中的主体实例崩溃,从而镜像服务器每秒一次的PING就无法返回值,从而知道主体服务器上不再有这个进程侦听相应的TCP端口,这种情况下,镜像服务器几乎瞬间就能发现故障. 检测到故障发生第二快的情况是主体服务器的操作系统崩溃.此时主体服务器不再响应镜像服务器的PING,从而在镜像服

  • SQL Server误区30日谈 第24天 26个有关还原(Restore)的误区

    本系列文章一直所没有触及的就是有关"还原(Restore)"的话题,因为一旦牵扯到这个话题就会涉及大量的误区,多到我无法通过一篇文章说完的地步.事实上,我希望用字母表的顺序为每一个误区进行编号,希望你看了不要昏昏欲睡.下面开始揭穿这26个误区. 误区 #24: 26个有关还原(Restore)的误区都是错误的 24 a)可以通过WITH STOPAT参数在完整备份和差异备份的基础上还原到特定时间点当然不能.虽然这个语法看上去貌似能的样子,但这个语法的最佳实践是你在进行日志还原到特定时间

  • SQL Server误区30日谈 第7天 一个实例多个镜像和日志传送延迟

    误区 #7:一个数据库可以存在多个镜像 错误 这个误区就有点老生常谈了.每一个主体服务器只允许一个镜像服务器.如果你希望存在多个主体服务器的副本,那么请使用事务日志传送,事务日志传送允许针对每一个主体存在多个辅助实例. 使用事务日志传送的一个优点是允许其中一个或多个辅助服务器存在延迟还原备份.这也是就是说对主体服务器进行日志备份(无论你喜欢与否,这几种高可用性技术各自有各自的术语): 数据库镜像:主体服务器-镜像服务器 事务日志传送:主要服务器-辅助服务器 复制:发布服务器-订阅服务器 当使用镜

  • SQL Server误区30日谈 第1天 正在运行的事务在服务器故障转移后继续执行

    误区 #1:在服务器故障转移后,正在运行的事务继续执行 这当然是错误的! 每次故障转移都伴随着某种形式的恢复.但是如果当正在执行的事务没有Commit时,由于服务器或实例崩溃导致连接断开,SQL Server可没有办法在故障转移后的服务器重新建立事务的上下文并继续执行事务-无论你使用的故障转移方式是集群,镜像,日志传送或是SAN复制. 对于故障转移集群来说,当故障转移发生后,一个SQL Server实例在另一个故障转移集群的节点启动.所有实例上的数据库都要经历Recovery阶段-也就是所有没有

  • SQL Server误区30日谈 第27天 使用BACKUP WITH CHECKSUM可以替代DBCC CheckDB

    误区 #27:使用BACKUP ... WITH CHECKSUM可以替代DBCC CheckDB错误    乍一看,由于BACKUP WITH CHECKSUM会检测所有分配出去的页的校验和的值,这个误区貌似是这么回事,但实际上并不是这么回事,原因如下:    由SQL Server 2000或是更早版本升上来的数据库page checksums必须开启,在开启后,并不是数据库中所有的页都会被叫上页校验和,当页损坏发生时,IO系统可不会区分损坏的页是有页校验和还是没有校验和的.所以使用BACK

  • SQL Server误区30日谈 第23天 有关锁升级的误区

    误区 #23: 锁升级的过程是由行锁升级到页锁,再由页锁升级到表锁错误    实际不是,在SQL Server 2005和之前的版本,页锁会直接升级到表锁.    在SQL Server 2005或SQL Server 2008,你可以通过如下跟踪标志改变锁升级的行为: 标志1211-完全禁止锁升级,但锁使用的内存会被限制在动态分配内存的60%,当超过这个值时,更多的锁将会伴随着内存溢出错误而失败. 标志1224-禁止锁升级,但内存使用超过40%时,会自动开启锁升级    如果标志1211和12

  • SQL Server误区30日谈 第29天 有关堆碎片的误区

    误区 #29:可以通过对堆建聚集索引再DROP后进行堆上的碎片整理Nooooooooooooo!!! 对堆建聚集索引再DROP在我看来是除了收缩数据库之外最2的事了.     如果你通过sys.dm_db_index_physical_stats(或是老版本的DBCC SHOWCONTIG)看到堆上有碎片,绝对不要通过建立聚集索引再删除聚集索引来整理堆碎片.好的做法应该是建立聚集索引之后不再删除,已经有非常多的资料阐述如何选择一个理想的聚集索引键--窄,很少变动,唯一,自增.Kimberly有一

  • SQL Server误区30日谈 第15天 CheckPoint只会将已提交的事务写入磁盘

    SQL Server误区30日谈 第15天 CheckPoint只会将已提交的事务写入磁盘

    误区 #15:CheckPoint只会将已提交的事务写入磁盘 错误 这个误区是由于太多人对日志和恢复系统缺少全面的了解而存在已久.CheckPoint会将自上次CheckPoint以来所有在内存中改变的页写回磁盘(译者注:也就是脏页),或是在上一个CheckPoint读入内存的脏页写入磁盘.无论事务是否已经提交,其所影响的页都会在Checkpoint时写回磁盘.但对于TempDB来说例外,因为TempDB的Checkpoint的事件周期中并不包含将脏页写回磁盘的步骤. 如果你想了解更多,请阅读下

  • SQL Server误区30日谈 第30天 有关备份的30个误区

    误区 #30:有关备份的30个误区全是错的在开始有关备份的误区之前,如果你对备份的基础没有了解,请看之前我在TechNet Magazine的文章:Understanding SQL Server Backups. 30-01)备份操作会导致阻塞不,备份不会导致对用户对象加锁,虽然备份对IO系统的负担导致看起来阻塞了,但实际上不会.唯一的特例是当备份包含到那些最小日志操作涉及到的数据区需要被加锁时,这个操作会阻塞CheckPoint,但DML操作永远不会受到备份操作的阻塞. 30-02)由完整恢

  • SQL Server误区30日谈 第5天 AWE在64位SQL SERVER中必须开启

    误区 #5: AWE在64位SQL SERVER中必须开启 错误!     在坊间流传的有关AWE的设置的各种版本让人非常困惑.比如说如何设置起作用,如何设置不起作用,在32位和64位上是否需要AWE等. 好吧,我来概括一下: 在64位系统(SQL SERVER 2005+版本) AWE是不需要的(即使是ON状态,也毫无影响) 开启"锁定内存页"使得缓冲池中的内存页不会被置换到虚拟内存中(实际上所有的Single Page Allocator分配和Stolen的内存都不会被置换) 当开

随机推荐