Systems Engineering and RDBMS

Open Transaction Issue – DBCC OPENTRAN()

Posted by decipherinfosys on December 11, 2008

We have troubleshooting scripts for the production environments and one of such scripts is to check for the open transactions using DBCC OPENTRAN(). A client called 2 days ago and logged an issue that it was not recording the open transactions under certain scenarios. One of our team members started troubleshooting the issue and captured some data using SQL Server profiler. What he found was pretty interesting – let’s try to walk over that scenario using an example but before we do that – let me tell you a bit about what the application was doing in some of those scenarios when no open transaction was being reported by DBCC OPENTRAN() command. The application was updating a column with the same value as existed in that column – it was essentially retrieving the value using the primary key look-up, making some calculations and regardless of the fact that the value was the same, the update statement was still getting fired. The next statement in the transaction then ran into an error which the application did not handle and it left an open transaction. Here’s how we can simulate it:

Let’s create a table but do not create the primary key yet. Insert a record into it and then simulate the above condition by firing off an update statement based on COL1 but update the COL2 to the same value as in the table – you will note that you will get the intended behavior from the DBCC OPENTRAN() command and the @@TRANCOUNT will also be 1.

USE DECIPHERTEST
GO
CREATE TABLE dbo.OPEN_TRAN_TEST (COL1 INT IDENTITY, COL2 NVARCHAR(10));
INSERT INTO dbo.OPEN_TRAN_TEST (COL2) VALUES (N’TEST’);
BEGIN TRAN
UPDATE dbo.OPEN_TRAN_TEST
SET COL2 = N’TEST’
WHERE COL1 = 1

SELECT @@TRANCOUNT

DBCC OPENTRAN()

Transaction information for database ‘DECIPHERTEST’.

Oldest active transaction:
SPID (server process ID): 54
UID (user ID) : -1
Name : user_transaction
LSN : (50:89:1)
Start time : Dec 11 2008 6:16:16:427AM
SID : 0x010500000000000515000000222a1897a392cbc1ae1926d36a050000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Now, let’s rollback this transaction and add the Primary key constraint to it which is implemented by default as the clustered unique index:

ALTER TABLE OPEN_TRAN_TEST ADD CONSTRAINT PK_OPEN_TRAN_TEST PRIMARY KEY (COL1);

Now, let us follow through the same steps again:

BEGIN TRAN
UPDATE dbo.OPEN_TRAN_TEST
SET COL2 = N’TEST’
WHERE COL1 = 1

SELECT @@TRANCOUNT

/*Simulate an un-handled error condition*/
select 1/0

DBCC OPENTRAN()

This time you will see that even though the transaction count shows as 1, the DBCC OPENTRAN() shows that there are no open transactions:

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

The sysprocesses table will also show an open transaction but not DBCC OPENTRAN()

select * from master..sysprocesses where open_tran <> 0

DBCC OPENTRAN() looks into the transaction log to see which transactions are open. Since in the latter case, it is not finding any transactions in the txn log, it seems that there are optimizations which minimize logging to the txn log under conditions as the one mentioned above. We changed the troubleshooting script to use the sysprocesses table and other sets of commands to take care of this scenario.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: