A SQL Server DBA myth a day: (26/30) nested transactions are real
来源:互联网 发布:建筑节能设计软件 编辑:程序博客网 时间:2024/05/19 16:22
(Look in the Misconceptions blog category for the rest of the month’s posts and check out the 60-page PDF with all the myths and misconceptions blog posts collected together when you join ourInsider list, plus my onlineMyths and Misconceptions training course on Pluralsight.)
Nested transactions are an evil invention designed to allow developers to make DBAs' lives miserable. In SQL Server, they are even more evil…
Myth #26:nested transactions are real in SQL Server.
FALSE!!!
Nested transactions do not actually behave the way the syntax would have you believe. I have no idea why they were coded this way in SQL Server – all I can think of is someone from the dim and distant past is continually thumbing their nose at the SQL Server community and going "ha – fooled you!!".
Let me explain. SQL Server allows you to start transactions inside other transactions – called nested transactions. It allows you to commit them and to roll them back.
The commit of a nested transaction has absolutely no effect – as the only transaction that really exists as far as SQL Server is concerned is the outer one. Can you say 'uncontrolled transaction log growth'? Nested transactions are a common cause of transaction log growth problems because the developer thinks that all the work is being done in the inner transactions so there's no problem.
The rollback of a nested transaction rolls back the entire set of transactions – as there is no such thing as a nested transaction.
Your developers should not use nested transactions.They are evil.
If you don't believe me, here's some code to show you what I mean. First off – create a database with a table that each insert will cause 8k in the log.
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
Test #1: Does rolling back a nested transaction only roll back that nested transaction?
BEGIN TRAN OuterTran;
GOINSERT INTO t1 DEFAULT Values;
GO 1000BEGIN TRAN InnerTran;
GOINSERT INTO t1 DEFAULT Values;
GO 1000SELECT @@TRANCOUNT, COUNT (*) FROM t1;
GO
I get back the results 2 and 2000. Now I'll roll back the nested transaction and it should only roll back the 1000 rows inserted by the inner transaction…
ROLLBACK TRAN InnerTran;
GOMsg 6401, Level 16, State 1, Line 1
Cannot roll back InnerTran. No transaction or savepoint of that name was found.
Hmm… from Books Online, I can only use the name of the outer transaction or no name. I'll try no name:
ROLLBACK TRAN;
GOSELECT @@TRANCOUNT, COUNT (*) FROM t1;
GO
And I get the results 0 and 0. AsBooks Online explains, ROLLBACK TRAN rolls back to the start of the outer transaction and sets@@TRANCOUNT to 0. All changes are rolled back. The only way to do what I want is to useSAVE TRAN and ROLLBACK TRAN to the savepoint name.
Test #2: Does committing a nested transaction really commit the changes made?
BEGIN TRAN OuterTran;
GOBEGIN TRAN InnerTran;
GOINSERT INTO t1 DEFAULT Values;
GO 1000COMMIT TRAN InnerTran;
GOSELECT COUNT (*) FROM t1;
GO
I get the result 1000, as expected. Now I'll roll back the outer transaction and all the work done by the inner transaction should be preserved…
ROLLBACK TRAN OuterTran;
GOSELECT COUNT (*) FROM t1;
GO
And I get back the result 0. Oops – committing the nested transaction did not make its changes durable.
Test #3: Does committing a nested transaction at least let me clear the log?
I recreated the database again before running this so the log was minimally sized to begin with, and the output fromDBCC SQLPERF below has been edited to only include theNestedXactsAreNotReal database.
BEGIN TRAN OuterTran;
GOBEGIN TRAN InnerTran;
GOINSERT INTO t1 DEFAULT Values;
GO 1000DBCC SQLPERF ('LOGSPACE');
GODatabase Name Log Size (MB) Log Space Used (%) Status
——————— ————- —————— ———–
NestedXactsAreNotReal 12.05469 95.81983 0
Now I'll commit the nested transaction, run a checkpoint (which will clear all possible transaction log in theSIMPLE recovery model), and check the log space again:
COMMIT TRAN InnerTran;
GOCHECKPOINT;
GODBCC SQLPERF ('LOGSPACE');
GODatabase Name Log Size (MB) Log Space Used (%) Status
——————— ————- —————— ———–
NestedXactsAreNotReal 12.05469 96.25324 0
Hmm – no change – in fact theLog Space Used (%) has increased slightly from writing out the checkpoint log records (seeHow do checkpoints work and what gets logged). Committing the nested transaction did not allow the log to clear. And of course not, because a rollback can be issued at any time which will roll back all the way to the start of the outer transaction – so all log records are required until the outer transaction commits or rolls back.
And to prove it, I'll commit the outer transaction and run a checkpoint:
COMMIT TRAN OuterTran;
GOCHECKPOINT;
GODBCC SQLPERF ('LOGSPACE');
GODatabase Name Log Size (MB) Log Space Used (%) Status
——————— ————- —————— ———–
NestedXactsAreNotReal 12.05469 26.4339 0
And it drops right down.
- A SQL Server DBA myth a day: (26/30) nested transactions are real
- SQL Server 2005 DBA Street Smarts: A Real World Guide to SQL Server 2005 Certification Skills
- c# Nested transactions are not supported
- T-SQL Nested procedure & transactions
- We are looking for an SQL Server DBA
- Joomla! (DAY 9) - Joomsport (DAY 7): Create a Real View 'Home VS Away'
- 《Troubleshooting SQL Server: A Guide for the Accidental DBA》电子书下载
- I would become a real dramer,not a day dreamer and night dreamer
- SQL Server Profiler only true fonts are supported. this is not a truetype font
- a day、 a week
- Day a day a day of past!
- 104 User SCOTT wants to back out the transactions on the REGIONS table in his schema. As a DBA, whic
- hunnu 11264 A Classic Myth: Flatland Superhero (凸包)
- hibernate4在执行sql时出现nested transactions not supported
- nested transactions not supported
- nested transactions not supported
- Encountered a duplicated sql alias [] during auto-discovery of a native-sql query; nested exception
- A-8SQL Server 触发器
- (转)排序算法的稳定与不稳定
- opencv——创建矩阵并显示
- iOS后台播放音乐
- IOS开发 IOS国际化
- 跳跃表以及C++实现
- A SQL Server DBA myth a day: (26/30) nested transactions are real
- 向量叉积
- HUSTOJ(敌兵布阵)
- block的一些用法
- cmd连接远程mysql
- 深入理解消息处理—LOOPER、HANDLER、THREAD
- NULL , '\0' , 0 本质及区别
- java学习笔记4--java 静态方法和实例方法的区别
- c++ 输出两位小数