The code was part of a rather large program so it wasn't easy to extract
just the DB pieces. However, I was able to build a sample program that
illustrates the issue. During this process I discovered that the problem
isn't what I thought it was.
What's happening is that in some cases my transaction is getting
destroyed if there was an error inserting a record in to the database.
The reason the record fails to load actually affects whether or not my
transaction is destroyed. For example, if the record fails to load due to
a field being too large, the transaction stays alive. If it fails to load
due to a Date conversion it is destroyed.
I built a test (included below) that illustrates this. The test program
will try to do two transactions with each including 3 records. Each
transaction has 1 insert attempt that is invalid. I would expect the
results of the two transactions to be the same. The 2 valid records
should load and the 1 invalid record shouldn't. However, as you'll see
the transaction that tries to load an invalid date only loads 1 record.
I'm not sure if this was by design or if it's a bug. I'd suspect a bug as
it seems that the results should be more consistent than this.
I've tried this on SQL Server 2005 SP3 and SQL Server 2008 Experss.
First Create a database with this table.
/****** Object: Table [dbo].[TransactionTest] Script Date: 03/05/2009
13:25:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TransactionTest](
[id] [int] NOT NULL,
[created_date] [datetime] NOT NULL,
[somedata] [varchar](100) NOT NULL,
[moredata] [varchar](20) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Then run the following app to see this issue in action.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
namespace TransactionTest
{
class Program
{
static void Main(string[] args)
{
string connectionString = "Data Source=localhost;Initial
Catalog=Test;Integrated Security=True;";
Console.WriteLine("Testing inserts with a truncation error");
SingleTransactionTruncation(connectionString);
Console.WriteLine("");
Console.WriteLine("Testing inserts with a date conversion
issue");
SingleTransactionDate(connectionString);
}
private static void SingleTransactionTruncation(string
connectionString)
{
using (SqlConnection connection = new
SqlConnection(connectionString))
{
connection.Open();
SqlTransaction transaction =
connection.BeginTransaction();
InsertRecord(connection, transaction, "insert into
[TransactionTest] ([id], [created_date], [somedata], [moredata]) values
('1', '1/1/1900', 'Truncation', 'More Data')");
//This one will fail due to a string truncation issue
InsertRecord(connection, transaction, "insert into
[TransactionTest] ([id], [created_date], [somedata], [moredata]) values
('2', '1/1/1900', 'Truncation', 'Data that is too long to fit in the
field so will error on insert')");
InsertRecord(connection, transaction, "insert into
[TransactionTest] ([id], [created_date], [somedata], [moredata]) values
('3', '1/1/1900', 'Truncation', 'More Data')");
try
{
transaction.Commit();
Console.WriteLine("Commit Succeded");
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
transaction.Dispose();
}
}
private static void InsertRecord(SqlConnection connection,
SqlTransaction transaction, string insertQuery)
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.Transaction = transaction;
command.CommandText = insertQuery;
try
{
command.ExecuteNonQuery();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
}
}
private static void SingleTransactionDate(string connectionString)
{
using (SqlConnection connection = new
SqlConnection(connectionString))
{
connection.Open();
SqlTransaction transaction =
connection.BeginTransaction();
InsertRecord(connection, transaction, "insert into
[TransactionTest] ([id], [created_date], [somedata], [moredata]) values
('1', '1/1/1900', 'Date', 'More Data')");
//This one will fail due to a Date Conversion issue
InsertRecord(connection, transaction, "insert into
[TransactionTest] ([id], [created_date], [somedata], [moredata]) values
('2', 'An Invalid Date', 'Date', 'More Data')");
InsertRecord(connection, transaction, "insert into
[TransactionTest] ([id], [created_date], [somedata], [moredata]) values
('3', '1/1/1900', 'Date', 'More Data')");
try
{
transaction.Commit();
Console.WriteLine("Commit Succeded");
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
transaction.Dispose();
}
}
}
}
Andrew Faust
Colbert Zhou said:
Hello Andrew,
Thanks for using Microsoft Newsgroup Support Service, this is Colbert
Zhou
[MSFT] and I will be working on this issue with you.
The issue we are facing here is not the same one as that KB article
describes. From the KB article, we can see that exception is thrown
at System.Data.SqlClient.SqlTransaction.ZombieCheck()
at System.Data.SqlClient.SqlTransaction.get_IsolationLevel()
But, the exception here is thrown
at System.Data.SqlClient.SqlTransaction.ZombieCheck()
at System.Data.SqlClient.SqlTransaction.Commit()
This is why that that hotfix does not work for our issue. Actually, many
different situations may leads to this exception message.
I write some codes in my side to have a quick test, but cannot reproduce
the same issue when calling the tran.Commit(). So, I am think this may
be
related to how we write the codes. For better supporting this case,
would
you mind posting your codes in newsgroup so that I can have a test
locally?
After the issue is reproduced in our environment, we can do some future
investigations to find the root cause and a possible solution/workaround
for this. Thanks for your understanding.
By the way, actually there is a SQL variable @@TRANCOUNT which can be
used
to trace the number of active transactions for the current connection.
The
increment/decrement rules for @@TRANCOUNT is,
BEGIN TRANSACTION statement increments @@TRANCOUNT by 1,
ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0,
ROLLBACK TRANSACTION savepoint_name does not affect @@TRANCOUNT,
COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1
So I also suggest you also log the @@TRANCOUNT to analyze the problem.
The
exception is thrown from Commit() method usually means that the
@@TRANCOUNT
may be already decrements to 0 before we call Commit(). Does this make
sense to you?
Wish this helps! Have a nice day, Andrew!
Best regards,
Colbert Zhou (
[email protected], remove 'online.')
Microsoft Online Community Support
Delighting our customers is our #1 priority. We welcome your comments
and
suggestions about how we can improve the support we provide to you.
Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent
issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each
follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach
the
most efficient resolution. The offering is not appropriate for
situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are
best
handled working with a dedicated Microsoft Support Engineer by
contacting
Microsoft Customer Support Services (CSS) at
http://support.microsoft.com/select/default.aspx?target=assistance&ln=en-us.
==================================================
This posting is provided "AS IS" with no warranties, and confers no
rights.
