THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andrew Kelly

DBCC OPENTRAN() behavior

I recently was bitten by some not so obvious behavior with DBCC OPENTRAN() that I would like to share.  Basically this command is supposed to show you the oldest open transaction within the specified database or the current one if none is specified. If you run the example below you can see the expected behavior.

USE Tempdb ;
GO
CREATE TABLE [dbo].[T1]([Col1] int NOT NULL, [Col2] char(3) ) ;
GO
INSERT INTO [dbo].[T1] ([Col1], [Col2]) VALUES (101, 'abc');
GO
BEGIN TRAN ;

    UPDATE [dbo].[T1] SET [Col2] = 'abc' WHERE [Col1] = 101 ;
GO

DBCC OPENTRAN();

ROLLBACK TRAN;
GO
DROP TABLE [dbo].[T1];
GO 

 This should show you something similar to this:

    Oldest active transaction:
        SPID (server process ID): 52
        UID (user ID) : -1
        Name          : user_transaction
        LSN           : (22:248:502)
        Start time    : Sep 25 2007  5:28:59:700PM
        SID           : 0x010500000000000515000000864be9f541b8a3fc1f944d76e8030000
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

But now add a PK constraint into the mix and run it again. 

USE Tempdb ;
GO
CREATE TABLE [dbo].[T1]([Col1] int NOT NULL, [Col2] char(3) ) ;
GO
ALTER TABLE [dbo].[T1] ADD CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED ([Col1] ASC) ;

GO
INSERT INTO [dbo].[T1] ([Col1], [Col2]) VALUES (101, 'abc');
GO
BEGIN TRAN ;

    UPDATE [dbo].[T1] SET [Col2] = 'abc' WHERE [Col1] = 101 ;
GO

DBCC OPENTRAN();

ROLLBACK TRAN;
GO
DROP TABLE [dbo].[T1];
GO

You now get this:

   No active open transactions.
   DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 Wait a minute, what's going on here. Are you telling me that just by adding a PK I changed the transaction? Well yes & no. If you look closely at the UPDATE statement it is updating the value with the same value that it already had. If you substitute the UPDATE with the one below you will get the expected output from DBCC OPENTRAN().

    UPDATE [dbo].[T1] SET [Col2] = 'xyz' WHERE [Col1] = 101 ;
 

In reality the transaction is not logged even if we create a clustered index vs. the PK constraint. A Non-Clustered index has the same effect as no index. So what does this all mean?  Based on some assumptions that I and some fellow MVP's had regarding this along with some further testing I came to two conclusions.

1.  As expected the Clustered Index or PK allows SQL Server to pinpoint the row and optimize the work done. But it must also be deeper rooted than that when it comes to how it actually logs this information in the transaction log.  

2.  The Engine is smart enough to realize that no changes have actually been made and does not log anything into the transaction log when the column in the WHERE clause has a Clustered index or PK constraint.

 

Since DBCC OPENTRAN() looks into the Transaction Log for these open transactions we can assume that there are certain optimizations built into the engine that minimize logging under the right conditions. So if you ever wonder why OPENTRAN isn't returning what you expect you should look to see if this may be the reason.

 

 

 

 

Published Tuesday, September 25, 2007 5:25 PM by Andrew Kelly
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Roy Harvey said:

Strangely enough, an update trigger on the table still executes.

September 25, 2007 5:30 PM
 

Andrew Kelly said:

Yes, the general concensus is that things like triggers, @@ROWCOUNT etc. will still have to act as though the row was actually updated to be consistant. But I bet there are a bunch of places where a lot of work could be saved if there was an easy and consistant way to determine which type of update this was instead of comparing all the columns on Inserted and Deleted:).

September 25, 2007 6:11 PM
 

Ben Amada said:

Hi Andrew,

Although I believe I understand your point, isn't the UPDATE statement you say to use as the replacement to get the expected behavior from DBCC OPENTRAN() the same as the first UPDATE statement?  Both statements are updating Col2 to 'abc'.  Or am I not seeing something right?

Thanks, Ben.

September 25, 2007 8:09 PM
 

Linchi Shea said:

In either case, SELECT open_tran FROM master..sysprocesses returns the correct count.

September 25, 2007 8:33 PM
 

Hugo Kornelis said:

I must have missed the original discussion among MVP's, so I'll just add my 2 cents here.

I think that the REASON why the additiion of the key changes the update, is that WITH the key the optimizer knows you'll only touch a single row, so it will generate a plan to change rows one at a time (facilitating an easy detection of the "no actual change" situation). Without the key, the optimizer has to create a plan that updates all affected rows at once, and with such a plan, it'll be a whole lot harder to detect which rows were not actually changed so SQL Server doesn't even try to do it.

Raig Craig Freedmans blog for more information about how these kind of plans work.

September 26, 2007 1:34 AM
 

Hugo Kornelis said:

Addendum - Craig Freedmans blog can be found at http://blogs.msdn.com/craigfr/default.aspx

September 26, 2007 1:40 AM
 

Rhys Jones said:

Rather than running this in TempDB, try it in master. Now you see the results you would expect. I thought this might have had something to do with the recovery model of the database in question but I've just tried and it seems not.

September 26, 2007 3:57 AM
 

Andrew Kelly said:

Ben,   Yes the Update is the same but the 2nd batch added a PK constraint to the mix.

September 26, 2007 7:35 AM
 

Andrew Kelly said:

Rhys,  I did not try it in Master but I did try it in a user db with the same results as Tempdb. I will play around more when I have time but I just thought it was interesting that this behaviour existed but not documented AKAIK.

September 26, 2007 7:37 AM
 

Desi said:

1st:  UPDATE [dbo].[T1] SET [Col2] = 'abc' WHERE [Col1] = 101 ;  --no pk

2nd: UPDATE [dbo].[T1] SET [Col2] = 'abc' WHERE [Col1] = 101 ; --pk added

and than (in your comment) you ask to replace the 2nd with 3rd.

3rd: UPDATE [dbo].[T1] SET [Col2] = 'abc' WHERE [Col1] = 101 ;

which is exactly the same as 2nd.

????

September 26, 2007 12:00 PM
 

Andrew Kelly said:

OK I see. My mistake, I will correct it and It should have been this:

UPDATE [dbo].[T1] SET [Col2] = 'xyz' WHERE [Col1] = 101 ;

September 26, 2007 12:11 PM
 

Mike Tillett said:

If I'm not too late to comment (it seems even though I get my Database Weekly Update from SQLServerCentral 'on time', the news and blogs are usually 'old', not that this invalidates their usefulness).

Which version of SQL Server did you try this on Andrew?  I have just run the first two scripts and they both produce an open transaction.

My version is Microsoft SQL Server  2000 - 8.00.194

October 3, 2007 5:29 AM
 

Andrew Kelly said:

Mike,

I should have mentioned the version which is 2005 as listed below. I don't have a 2000 instance to try it on at the moment but it is interesting that the behavior is different. I guess this optimization was put in for 2005.

Microsoft SQL Server 2005 - 9.00.3159.00 (Intel X86)   Mar 23 2007 16:15:11  

Copyright (c) 1988-2005 Microsoft Corporation  

Developer Edition on Windows NT 6.0 (Build 6000: )

October 3, 2007 7:39 AM
 

Vern Rabe said:

Interesting. Add a rowversion column to the table, and the transaction logging occurs even with a PK.

October 8, 2007 12:59 PM
 

Andrew Kelly said:

I can see that adding the RowVersion column will force the logging to enforce the fact the rowversion value must change with each update. The internal optimizations do not change the fact the RowVersion should change during the update on matching rows.

October 8, 2007 1:08 PM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement