Kalen Delaney : Transactions

TSQL Tuesday #11:Rolling Back TRUNCATE TABLE

Kalen Delaney — Tue, 12 Oct 2010 20:02:00 GMT

“It ain't what people don't know that hurts them it's what they know that ain't so” -- commonly attributed to Mark Twain SQL Server has been around for a long time now, and people who learn one version well sometimes have monumental task when a new version...(read more)

Geek City: How Many Rows?

Kalen Delaney — Mon, 07 Dec 2009 17:59:00 GMT

In my last post, I told you I'd post something technical before I left for Sweden. I almost made it. I wrote this in the Seattle airport, but wasn't able to post it until I got to Stockholm. For as long as I've been working with SQL Server, I've been...(read more)

Geek City: Why I still need Sysprocesses

Kalen Delaney — Mon, 30 Jun 2008 03:48:00 GMT

I've said many times that my favorite new feature in SQL Server 2005 is the new metadata, in particular the new Dynamic Management Objects. When I have to do troubleshooting on a SQL Server 2000 system, it is worse than painful, not to have my favorite tools like sys.dm_tran_locks, sys.dm_exec_cached_plans and
sys.dm_exec_query_plan. By now, on the eve of the release of the version AFTER SQL Server 2005, my transition to the new metadata is almost complete.

With one notable exception...

I have always used sysprocesses constantly for troubleshooting information. Although most of the useful information that allows me to see what sessions are using lots of memory, or performing lots of I/O, can found in sys.dm_exec_sessions, there is one piece of information that isn't there. Sysprocesses contains a columns called open_tran which reflects the transaction nesting of each session. If a session issues four BEGIN TRAN commands, with no COMMITs or ROLLBACKs, their session will have an open_tran value in sysprocesses of 4. Any open_tran value greater than 0 might mean that a transaction is holding locks and blocking other processes, or it might be keeping the transaction log from being cleared. If you ever notice open_tran values in higher than 2 or 3, it's a pretty good indication that a developer doesn't know much about SQL Server transaction management. I use this value all the time, and once had to troubleshoot a system where dozens of connections had open_tran values in the 20s and 30s! (For more details about nesting transactions, see my earlier post.

So imagine my surprise when I discovered that the sys.dm_exec_sessions view, which is supposed to 'replace' sysprocesses in SQL Server 2005, has no column to provide this information! Another view, sys.dm_exec_requests, has a column called open_transaction_count, which you might think would be the same thing. And it actually is the same information, but the sys.dm_exec_requests view only returns rows for sessions that are currently active, i.e. currently running a query. The need for examining the open_tran value is greatest when looking at those sessions that aren't doing anything, but are just sitting there with an open transaction. Those are the processes that you need to troubleshoot. I had felt that the omission of open_tran (or open_transaction_count) from sys.dm_exec_sessions was just an oversight, and it would be 'fixed' in the next release. But it appears I am to be disappointed. I just checked sys.dm_exec_sessions in RC0, and there is still no open_transaction_count column.

So long live sysprocesses!

~Kalen

Geek City: How SQL Server Detects the Correct Versioned Rows

Kalen Delaney — Thu, 03 Apr 2008 19:16:00 GMT

Here is a question I just received from the feedback page on my web site:

I have finished the book <the storage engine> and like it very much. I am now reading <query tuning and optimization>
I know in the READ COMMITTED SNAPSHOT isolation, when a row is being modified in a transaction, it generates an old committed version so another transaction can read it without being blocked.
But I don’t know how SQL Server uses SNAPSHOT isolation to prevent Phantom Read being happening.
In SERIALIZABLE isolation the ranged key or the whole table is locked, but in SNAPSHOT isolation, it can NOT generate any committed version since the row does NOT exist at the moment. So how does it know that the newly inserted data should not be included in the second SELECT statement?

-Tom

Tom is correct in that INSERTS do not generate versioned rows. However, SQL Server is able to keep track of when each change was made under snapshot isolation. Once a database is enabled for snapshots, every rows inserted, deleted or modified gets an additional 14 bytes of overhead added to it. These bytes contain 2 pieces of information. First is a row pointer to the previous committed version of the row, which is stored in the version store in tempdb. This pointer is only used for deleted and updated rows, since there will be no previous values for newly inserted rows.

However, these extra bytes also include a value called XSN, or transaction sequence number, which you can think of like a timestamp for a database. Any database enabled for snapshot keeps an internal XSN value, that is incremented each time any change is made, or any snapshot select is performed. The metadata also keeps track of all active transactions, and what the XSN was when the transaction started. It uses the view sys.dm_tran_active_snapshot_database_transactions for this. So when you are reading data, SQL Server will look at the XSN number in each row, and not return any rows that have an XSN value greater than the XSN value at the time the transaction started.

I hope this helps!

~Kalen

www.InsideSQLServer.com
www.SQLServerDVD.com

Did you Know? Nesting Transactions

Kalen Delaney — Mon, 13 Aug 2007 22:20:00 GMT

Transact-SQL provides three different methods of transaction control: autocommit transactions, explicit transactions and implicit transactions.

An autocommit transaction is any individual INSERT, UPDATE or DELETE operation, no matter how many rows are affected. As soon as the statement is completed, it is committed, which means that all the corresponding log records, include a COMMIT TRAN log record, are written to the transaction log on disk (your .ldf file).

If you want to be able to combine multiple statements into a single transaction, or if you want to be able to conditionally rollback the transaction, you can use explicit transaction control, with the BEGIN TRANSACTION (or BEGIN TRAN) and COMMIT TRANSACTION (or COMMIT TRAN) control statements. For example, to make sure both T1 and T2 are updated, or neither one is updated, you can use the following pseudo-code:

BEGIN TRAN
    UPDATE T1 ...
    UPDATE T2 ...
COMMIT TRAN

If there is a system failure after T1 is updated, the transaction will not be committed. It will only be committed if both updates can succeed.

Also, by using explicit transactions, you can decide to rollback a transaction, after checking for an error condition or for the number of rows affected:

BEGIN TRAN
    UPDATE T1 ...
    IF @@error > 0 ROLLBACK TRAN
        ELSE COMMIT TRAN

The third type of transaction control, implicit transactions, is not considered a default behavior and is part of the product only to support compatibility with other database products. I'll say a couple of things about it shortly.

Here is a little quiz I frequently give my students. Suppose I have the following batch:

BEGIN TRAN
UPDATE T1
BEGIN TRAN
UPDATE T2
BEGIN TRAN
UPDATE T3
COMMIT TRAN

The quiz question is: What gets committed?

I usually get a variety of answers including: All the updates, none of the updates, the update to T1, the update to T3. (Nobody suggests the update to T2.)

It turns out the answer is: None of the above.

SQL Server keeps an internal counter of how many times BEGIN TRAN has been executed, and you need to execute the same number of COMMIT TRANs to get the real commit to take place. Each BEGIN TRAN increments this internal counter, and each COMMIT TRAN decrements it. Only when the counter gets to 0, will the log records be written out to disk and the transaction will be truly committed. You can look at the value of this counter with the function @@trancount. You can use this function to also see that when you do a ROLLBACK, the counter is immediately set all the way back to 0.

BEGIN TRAN
SELECT @@trancount
BEGIN TRAN
SELECT @@trancount
BEGIN TRAN
SELECT @@trancount

ROLLBACK TRAN
SELECT @@trancount

So why would you want to have nested transactions, if it really doesn't give you any advantage? The answer to that will have to wait for a future post. This post is about the relationship of nesting transactions to the third type of transaction control: implicit transactions.

The name 'implicit transactions' is a bit of a misnomer. It is only the BEGIN TRAN that is implicit; the COMMIT TRAN must always be explicit. This means that no transaction will be committed until a COMMIT TRAN is issued. Transactions will begin any time an INSERT, UPDATE, DELETE or SELECT is executed (as well as a few other statements, such as CREATE and DROP, that you can read about in the Books Online) as long as there is not already an open transaction.

As mentioned, implicit transaction mode is not SQL Server's default. You have to request implicit transaction mode either with a SET statement:

SET IMPLICIT_TRANSACTIONS ON;

or by setting the user_options configuration option to have the 2-bit set to 1. You can read about sp_configure 'user options' in the Books Online. Although I don't recommend using implicit transactions, if you're going to use them, I recommend having all sessions use them, but setting the configuration option. To have some sessions using implicit transactions and requiring a COMMIT for every individual INSERT, UPDATE and DELETE and to have other sessions not requiring that closure, seems to be asking for trouble.

So what happens if you use a BEGIN TRAN when you are in implicit transaction mode? I used to think that the BEGIN TRAN would just be ignored, but it turns out I was wrong. Think about what would happen if you had a normal, implicit mode transaction like this:

UPDATE T1
SET ...
COMMIT TRAN

and then suppose someone executes this batch from within an explicit transaction, and put the BEGIN/COMMIT around it:

BEGIN TRAN
UPDATE T1
SET ...
COMMIT TRAN
COMMIT TRAN

If the BEGIN TRAN was ignored, the first COMMIT would would set @@trancount to 0 and the second would give an error. You can see this behavior by just executing COMMIT TRAN all by itself. SQL Server generates this message:

Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

So if you are in implicit transaction mode, issuing a BEGIN TRAN (which you really shouldn't do) sets @@trancount to 2. This really surprised me when I first noticed it, but then I realized the point of it. When you perform any DML operation, since you are already in a transaction, @@trancount will not be incremented. When you issue the COMMIT for your DML, @@trancount will be decremented to 1. When you issue the COMMIT TRAN to match the BEGIN TRAN, @@trancount will decrement to 0 and the transaction will really be committed. Here's a full script to illustrate the behavior of @@trancount with implicit transactions:

SET IMPLICIT_TRANSACTIONS OFF;
GO
IF EXISTS (SELECT * FROM sys.objects
WHERE name = 'T1' AND type = 'U')
DROP TABLE T1;
GO
CREATE TABLE T1 (col1 int);
GO
INSERT INTO T1 SELECT 1;
GO

SET IMPLICIT_TRANSACTIONS ON;
GO
BEGIN TRAN;
SELECT @@trancount;
UPDATE T1
SET col1 = col1 + 1;
COMMIT TRAN;
SELECT @@trancount;
COMMIT TRAN;
SELECT @@trancount;
GO
SET IMPLICIT_TRANSACTIONS OFF;
GO

Have fun!

~Kalen