Rollback Transaction Timing Out Framework 1.0 SQL Server 2000

After aborting a long (around 10 minutes) and with numerous updates, the
Rollback function will raise an exception giving the message:

System.Data.SqlClient.SqlException: Timeout expired. The timeout period
elapsed prior to completion of the operation or the server is not
responding. -- at System.Data.SqlClient.SqlTransaction.Rollback()
If the transaction is not aborted, ie the Commit function is called, a
timeout does not occur.



In extracts from source below the timeout is set to 0 (for ever). Does
anyone have experience of the Rollback timing out?




......

addCon = new System.Data.SqlClient.SqlConnection();

......



System.Data.SqlClient.SqlCommand addCom = new
System.Data.SqlClient.SqlCommand();

Try

{

.....

addCom.Connection = addCon;

addCom.CommandTimeout = 0; //go on for ever

addCon.Open();

//Start Transaction

addCom.Transaction = addCon.BeginTransaction();

transactionStarted = true;

.......



catch(Exception e)

{

ImportController.errorLevel = errorLevelMajorError;

Audit("ERROR: Update PDS " + e.ToString());

if (transactionStarted)

{

errorLevelTransactionRolledBack =
errorLevelTransactionRolledBack;

addCom.Transaction.Rollback(); <<--- Times out here

}

addCon.Close();

addCom.Dispose();

}




Thanks in advance

Miha
Fri Dec 05 03:32:08 CST 2003

Hi Chris,

I see two ways to solve the problem.
a) Create a SqlCommand with command text "Rollback Transaction" and set
CommandTimeout to a time value you wish
b) use reflection to set connection.GetOptions().ConnectionTimeout to
appropriate value - I am not 100% sure that this value is used with rollback
though.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

"Chris B" <Chris.B@nospam.com> wrote in message
news:u85ULppuDHA.2448@TK2MSFTNGP09.phx.gbl...
> After aborting a long (around 10 minutes) and with numerous updates, the
> Rollback function will raise an exception giving the message:
>
> System.Data.SqlClient.SqlException: Timeout expired. The timeout period
> elapsed prior to completion of the operation or the server is not
> responding. -- at System.Data.SqlClient.SqlTransaction.Rollback()
> If the transaction is not aborted, ie the Commit function is called, a
> timeout does not occur.
>
>
>
> In extracts from source below the timeout is set to 0 (for ever). Does
> anyone have experience of the Rollback timing out?
>
>
>
>
> ......
>
> addCon = new System.Data.SqlClient.SqlConnection();
>
> ......
>
>
>
> System.Data.SqlClient.SqlCommand addCom = new
> System.Data.SqlClient.SqlCommand();
>
> Try
>
> {
>
> .....
>
> addCom.Connection = addCon;
>
> addCom.CommandTimeout = 0; //go on for ever
>
> addCon.Open();
>
> //Start Transaction
>
> addCom.Transaction = addCon.BeginTransaction();
>
> transactionStarted = true;
>
> .......
>
>
>
> catch(Exception e)
>
> {
>
> ImportController.errorLevel = errorLevelMajorError;
>
> Audit("ERROR: Update PDS " + e.ToString());
>
> if (transactionStarted)
>
> {
>
> errorLevelTransactionRolledBack =
> errorLevelTransactionRolledBack;
>
> addCom.Transaction.Rollback(); <<--- Times out here
>
> }
>
> addCon.Close();
>
> addCom.Dispose();
>
> }
>
>
>
>
> Thanks in advance
>
>
>

Chris
Fri Dec 05 04:49:43 CST 2003


"Miha Markic" <miha at rthand com> wrote in message
news:uLTxoKxuDHA.1996@TK2MSFTNGP12.phx.gbl...
> Hi Chris,
>
> I see two ways to solve the problem.
> a) Create a SqlCommand with command text "Rollback Transaction" and set
> CommandTimeout to a time value you wish
> b) use reflection to set connection.GetOptions().ConnectionTimeout to
> appropriate value - I am not 100% sure that this value is used with
rollback
> though.
>

Thanks Miha,
Perhaps my original post was not too clear. Cutting down the example I gave
previously gives below.
Probably my use of Transaction in this case is a little optimistic. Under
SQL Server 7 it tended to be the kiss of death to leave a transaction open
for any appreciable time, but I was hoping this was not the case for SQL
Server 2000. I will try solution (a) and reply here with the result. But I
think I will in long term write the data to temporary tables, picking up any
errors, not needing to use a transction. If no errors are found then
single deletion and insertion SQL statements can be used to move the data
over, perhaps just using a transaction here.




try
{
command.Connection = connection;
command.CommandTimeout = 0; //go on for ever
connection.Open();
command.Transaction = connection.BeginTransaction();
........
//~couple of million records being deleted
............
//~couple of million records being inserted individually
// any data errors found will throw an exception and hence try the
rollback
........
command.Transaction.Commit() ; <<--- This will succeed after 30
minutes, say.
}
catch()
{
command.Transaction.Rollback(); <<--- This will time out after 5
minutes say
}

Chris
Fri Dec 05 12:12:43 CST 2003

I have examined this in more detail. Although an exception is raised, the
transaction is rolled back.

"Timeout expired. The timeout period elapsed prior to completion of the
operation or the server is not responding."

I can post full code here if anyone wishes to reproduce this.

Miha
Fri Dec 05 12:31:57 CST 2003

Hi Chris,

"Chris B" <Chris.B@nospam.com> wrote in message
news:Og8cQs1uDHA.1060@TK2MSFTNGP12.phx.gbl...
> I have examined this in more detail. Although an exception is raised, the
> transaction is rolled back.
>
> "Timeout expired. The timeout period elapsed prior to completion of the
> operation or the server is not responding."
>
> I can post full code here if anyone wishes to reproduce this.

I guess one would have to populate database with lot of records to repro
this ...

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Chris
Fri Dec 05 15:38:51 CST 2003


"Miha Markic" <miha at rthand com> wrote in message
news:ui1HT41uDHA.1340@TK2MSFTNGP09.phx.gbl...
> I guess one would have to populate database with lot of records to repro
> this ...

The test I run populated a table with 280 000 records. Then run a
transaction to (delete all, insert about 200 000 records) and then perform
the Rollback. This then gives the Exception, but the Rollback has in fact
succeeded. The test table is a single column so test can be run in 10
minutes or so.
I will be looking at your suggestion (A) but not till Monday at work.

Chris

Miha
Fri Dec 05 15:50:13 CST 2003


"Chris B" <anonymous@btinternet.com> wrote in message
news:%23l7wtg3uDHA.620@TK2MSFTNGP10.phx.gbl...
>
> "Miha Markic" <miha at rthand com> wrote in message
> news:ui1HT41uDHA.1340@TK2MSFTNGP09.phx.gbl...
> > I guess one would have to populate database with lot of records to repro
> > this ...
>
> The test I run populated a table with 280 000 records. Then run a
> transaction to (delete all, insert about 200 000 records) and then perform
> the Rollback. This then gives the Exception, but the Rollback has in fact
> succeeded.

Yes, probably it is just client app that gives up waiting.

The test table is a single column so test can be run in 10
> minutes or so.
> I will be looking at your suggestion (A) but not till Monday at work.

Hmmm. OK. :)

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

Chris
Mon Dec 08 12:50:33 CST 2003

Miha, yes your suggestion (A) -- executing the SQL statement "ROLLBACK
TRANSACTION" did work.
Thanks.

Below is source if anyone is interested to observe the two rollbacks. (you
need to give it connection string, tableName, columnName)
I used a table with single column, varChar(24) set as a Key.

Running on Friday I could achieve the timeout with 288500 records in table
then trying to rollback after removing and adding 200000 records. Today,
with a cleaner machine, I run again from 288500 to start, adding 2000
records on each timer event, but no timeout occurred at 200000. Running it
up to 800000 records a timeout occurred on rollback (although it has rolled
back). Repeating this but then using your rollback technique no timeout
occurred.


using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.Data;

using SQL = System.Data.SqlClient;

namespace TransactionTest

{

/// <summary>

/// Summary description for Form1.

/// </summary>

public class Form1 : System.Windows.Forms.Form

{

private SQL.SqlCommand com = null;

private int countTick = 0;

private int iterateCount = 100;

private System.Data.SqlClient.SqlConnection con;

private System.Windows.Forms.Button button1;

private System.Windows.Forms.TextBox textBox1;

private System.Windows.Forms.Button button2;

private System.Windows.Forms.Button button3;

private System.Windows.Forms.Timer timer1;

private System.Windows.Forms.TextBox textBox2;

private System.Windows.Forms.TextBox textBox3;

private System.Windows.Forms.Button button4;

private System.ComponentModel.IContainer components;

public Form1()

{

//

// Required for Windows Form Designer support

//

InitializeComponent();

//

// TODO: Add any constructor code after InitializeComponent call

//

}

/// <summary>

/// Clean up any resources being used.

/// </summary>

protected override void Dispose( bool disposing )

{

if( disposing )

{

if (components != null)

{

components.Dispose();

}

}

base.Dispose( disposing );

}

#region Windows Form Designer generated code

/// <summary>

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

/// </summary>

private void InitializeComponent()

{

this.components = new System.ComponentModel.Container();

this.con = new System.Data.SqlClient.SqlConnection();

this.button1 = new System.Windows.Forms.Button();

this.textBox1 = new System.Windows.Forms.TextBox();

this.button2 = new System.Windows.Forms.Button();

this.button3 = new System.Windows.Forms.Button();

this.timer1 = new System.Windows.Forms.Timer(this.components);

this.textBox2 = new System.Windows.Forms.TextBox();

this.textBox3 = new System.Windows.Forms.TextBox();

this.button4 = new System.Windows.Forms.Button();

this.SuspendLayout();

//

// con

//

this.con.ConnectionString = "you must supply this";

//

// button1

//

this.button1.Location = new System.Drawing.Point(40, 16);

this.button1.Name = "button1";

this.button1.Size = new System.Drawing.Size(88, 24);

this.button1.TabIndex = 0;

this.button1.Text = "Start";

this.button1.Click += new System.EventHandler(this.button1_Click);

//

// textBox1

//

this.textBox1.Location = new System.Drawing.Point(40, 56);

this.textBox1.Name = "textBox1";

this.textBox1.Size = new System.Drawing.Size(144, 20);

this.textBox1.TabIndex = 1;

this.textBox1.Text = "";

//

// button2

//

this.button2.Location = new System.Drawing.Point(40, 120);

this.button2.Name = "button2";

this.button2.Size = new System.Drawing.Size(144, 24);

this.button2.TabIndex = 2;

this.button2.Text = "Rollback";

this.button2.Click += new System.EventHandler(this.button2_Click);

//

// button3

//

this.button3.Location = new System.Drawing.Point(40, 208);

this.button3.Name = "button3";

this.button3.Size = new System.Drawing.Size(144, 24);

this.button3.TabIndex = 3;

this.button3.Text = "Commit";

this.button3.Click += new System.EventHandler(this.button3_Click);

//

// timer1

//

this.timer1.Interval = 250;

this.timer1.Tick += new System.EventHandler(this.timer1_Tick);

//

// textBox2

//

this.textBox2.Location = new System.Drawing.Point(40, 88);

this.textBox2.Name = "textBox2";

this.textBox2.Size = new System.Drawing.Size(144, 20);

this.textBox2.TabIndex = 4;

this.textBox2.Text = "";

//

// textBox3

//

this.textBox3.Location = new System.Drawing.Point(152, 16);

this.textBox3.Name = "textBox3";

this.textBox3.Size = new System.Drawing.Size(56, 20);

this.textBox3.TabIndex = 5;

this.textBox3.Text = "100";

//

// button4

//

this.button4.Location = new System.Drawing.Point(40, 160);

this.button4.Name = "button4";

this.button4.Size = new System.Drawing.Size(144, 24);

this.button4.TabIndex = 6;

this.button4.Text = "MM Rollback";

this.button4.Click += new System.EventHandler(this.button4_Click);

//

// Form1

//

this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);

this.ClientSize = new System.Drawing.Size(480, 273);

this.Controls.AddRange(new System.Windows.Forms.Control[] {

this.button4,

this.textBox3,

this.textBox2,

this.button3,

this.button2,

this.textBox1,

this.button1});

this.Name = "Form1";

this.Text = "Form1";

this.ResumeLayout(false);

}

#endregion

/// <summary>

/// The main entry point for the application.

/// </summary>

[STAThread]

static void Main()

{

Application.Run(new Form1());

}

private void button1_Click(object sender, System.EventArgs e)

{

com = new SQL.SqlCommand();

com.Connection = con;

com.CommandTimeout = 0;

con.Open();

com.Transaction = con.BeginTransaction();

com.CommandText = "Delete from [tableName]";

int rowsChanged = com.ExecuteNonQuery();

textBox1.Text = "Rows removed = " + rowsChanged.ToString();

countTick = 0;

iterateCount = int.Parse(textBox3.Text);

timer1.Enabled = true;

}

private void timer1_Tick(object sender, System.EventArgs e)

{

for (int i = 0; i < iterateCount; ++i)

{

++countTick;

com.CommandText = "Insert into [tableName] ([columnName]) VALUES ('K" +
countTick.ToString() + "')";

com.ExecuteNonQuery();

}

textBox2.Text = "Record Count = " + countTick.ToString();


}

private void button3_Click(object sender, System.EventArgs e)

{

try

{

timer1.Enabled = false;

com.Transaction.Commit();

con.Close();

com.Dispose();

}

catch(Exception ex)

{

MessageBox.Show("Error: " + ex.ToString());

}

}

private void button2_Click(object sender, System.EventArgs e)

{

try

{

timer1.Enabled = false;

com.Transaction.Rollback();

con.Close();

com.Dispose();

}

catch(Exception ex)

{

MessageBox.Show("Error: " + ex.ToString());

}

}

private void button4_Click(object sender, System.EventArgs e)

{

try

{

timer1.Enabled = false;

com.CommandText = "Rollback Transaction";

com.ExecuteNonQuery();

con.Close();

com.Dispose();

}

catch(Exception ex)

{

MessageBox.Show("Error: " + ex.ToString());

}


}

}

}

Angel
Wed Dec 10 21:31:46 CST 2003

Chris,
Good stuff! thanks for the repro code I will set this up locally. Like Miha
said it looks like we are waiting in the client from something from the
server to let us know the rollback happened, this is taking more than 30
seconds so we throw a timeout. I have no idea what we could be waiting for
since Rollback does not return anything, sounds like a bug.

--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

"Chris B" <Chris.B@nospam.com> wrote in message
news:OkK0TvbvDHA.2456@TK2MSFTNGP12.phx.gbl...
> Miha, yes your suggestion (A) -- executing the SQL statement "ROLLBACK
> TRANSACTION" did work.
> Thanks.
>
> Below is source if anyone is interested to observe the two rollbacks. (you
> need to give it connection string, tableName, columnName)
> I used a table with single column, varChar(24) set as a Key.
>
> Running on Friday I could achieve the timeout with 288500 records in table
> then trying to rollback after removing and adding 200000 records. Today,
> with a cleaner machine, I run again from 288500 to start, adding 2000
> records on each timer event, but no timeout occurred at 200000. Running
it
> up to 800000 records a timeout occurred on rollback (although it has
rolled
> back). Repeating this but then using your rollback technique no timeout
> occurred.
>
>
> using System;
>
> using System.Drawing;
>
> using System.Collections;
>
> using System.ComponentModel;
>
> using System.Windows.Forms;
>
> using System.Data;
>
> using SQL = System.Data.SqlClient;
>
> namespace TransactionTest
>
> {
>
> /// <summary>
>
> /// Summary description for Form1.
>
> /// </summary>
>
> public class Form1 : System.Windows.Forms.Form
>
> {
>
> private SQL.SqlCommand com = null;
>
> private int countTick = 0;
>
> private int iterateCount = 100;
>
> private System.Data.SqlClient.SqlConnection con;
>
> private System.Windows.Forms.Button button1;
>
> private System.Windows.Forms.TextBox textBox1;
>
> private System.Windows.Forms.Button button2;
>
> private System.Windows.Forms.Button button3;
>
> private System.Windows.Forms.Timer timer1;
>
> private System.Windows.Forms.TextBox textBox2;
>
> private System.Windows.Forms.TextBox textBox3;
>
> private System.Windows.Forms.Button button4;
>
> private System.ComponentModel.IContainer components;
>
> public Form1()
>
> {
>
> //
>
> // Required for Windows Form Designer support
>
> //
>
> InitializeComponent();
>
> //
>
> // TODO: Add any constructor code after InitializeComponent call
>
> //
>
> }
>
> /// <summary>
>
> /// Clean up any resources being used.
>
> /// </summary>
>
> protected override void Dispose( bool disposing )
>
> {
>
> if( disposing )
>
> {
>
> if (components != null)
>
> {
>
> components.Dispose();
>
> }
>
> }
>
> base.Dispose( disposing );
>
> }
>
> #region Windows Form Designer generated code
>
> /// <summary>
>
> /// Required method for Designer support - do not modify
>
> /// the contents of this method with the code editor.
>
> /// </summary>
>
> private void InitializeComponent()
>
> {
>
> this.components = new System.ComponentModel.Container();
>
> this.con = new System.Data.SqlClient.SqlConnection();
>
> this.button1 = new System.Windows.Forms.Button();
>
> this.textBox1 = new System.Windows.Forms.TextBox();
>
> this.button2 = new System.Windows.Forms.Button();
>
> this.button3 = new System.Windows.Forms.Button();
>
> this.timer1 = new System.Windows.Forms.Timer(this.components);
>
> this.textBox2 = new System.Windows.Forms.TextBox();
>
> this.textBox3 = new System.Windows.Forms.TextBox();
>
> this.button4 = new System.Windows.Forms.Button();
>
> this.SuspendLayout();
>
> //
>
> // con
>
> //
>
> this.con.ConnectionString = "you must supply this";
>
> //
>
> // button1
>
> //
>
> this.button1.Location = new System.Drawing.Point(40, 16);
>
> this.button1.Name = "button1";
>
> this.button1.Size = new System.Drawing.Size(88, 24);
>
> this.button1.TabIndex = 0;
>
> this.button1.Text = "Start";
>
> this.button1.Click += new System.EventHandler(this.button1_Click);
>
> //
>
> // textBox1
>
> //
>
> this.textBox1.Location = new System.Drawing.Point(40, 56);
>
> this.textBox1.Name = "textBox1";
>
> this.textBox1.Size = new System.Drawing.Size(144, 20);
>
> this.textBox1.TabIndex = 1;
>
> this.textBox1.Text = "";
>
> //
>
> // button2
>
> //
>
> this.button2.Location = new System.Drawing.Point(40, 120);
>
> this.button2.Name = "button2";
>
> this.button2.Size = new System.Drawing.Size(144, 24);
>
> this.button2.TabIndex = 2;
>
> this.button2.Text = "Rollback";
>
> this.button2.Click += new System.EventHandler(this.button2_Click);
>
> //
>
> // button3
>
> //
>
> this.button3.Location = new System.Drawing.Point(40, 208);
>
> this.button3.Name = "button3";
>
> this.button3.Size = new System.Drawing.Size(144, 24);
>
> this.button3.TabIndex = 3;
>
> this.button3.Text = "Commit";
>
> this.button3.Click += new System.EventHandler(this.button3_Click);
>
> //
>
> // timer1
>
> //
>
> this.timer1.Interval = 250;
>
> this.timer1.Tick += new System.EventHandler(this.timer1_Tick);
>
> //
>
> // textBox2
>
> //
>
> this.textBox2.Location = new System.Drawing.Point(40, 88);
>
> this.textBox2.Name = "textBox2";
>
> this.textBox2.Size = new System.Drawing.Size(144, 20);
>
> this.textBox2.TabIndex = 4;
>
> this.textBox2.Text = "";
>
> //
>
> // textBox3
>
> //
>
> this.textBox3.Location = new System.Drawing.Point(152, 16);
>
> this.textBox3.Name = "textBox3";
>
> this.textBox3.Size = new System.Drawing.Size(56, 20);
>
> this.textBox3.TabIndex = 5;
>
> this.textBox3.Text = "100";
>
> //
>
> // button4
>
> //
>
> this.button4.Location = new System.Drawing.Point(40, 160);
>
> this.button4.Name = "button4";
>
> this.button4.Size = new System.Drawing.Size(144, 24);
>
> this.button4.TabIndex = 6;
>
> this.button4.Text = "MM Rollback";
>
> this.button4.Click += new System.EventHandler(this.button4_Click);
>
> //
>
> // Form1
>
> //
>
> this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
>
> this.ClientSize = new System.Drawing.Size(480, 273);
>
> this.Controls.AddRange(new System.Windows.Forms.Control[] {
>
> this.button4,
>
> this.textBox3,
>
> this.textBox2,
>
> this.button3,
>
> this.button2,
>
> this.textBox1,
>
> this.button1});
>
> this.Name = "Form1";
>
> this.Text = "Form1";
>
> this.ResumeLayout(false);
>
> }
>
> #endregion
>
> /// <summary>
>
> /// The main entry point for the application.
>
> /// </summary>
>
> [STAThread]
>
> static void Main()
>
> {
>
> Application.Run(new Form1());
>
> }
>
> private void button1_Click(object sender, System.EventArgs e)
>
> {
>
> com = new SQL.SqlCommand();
>
> com.Connection = con;
>
> com.CommandTimeout = 0;
>
> con.Open();
>
> com.Transaction = con.BeginTransaction();
>
> com.CommandText = "Delete from [tableName]";
>
> int rowsChanged = com.ExecuteNonQuery();
>
> textBox1.Text = "Rows removed = " + rowsChanged.ToString();
>
> countTick = 0;
>
> iterateCount = int.Parse(textBox3.Text);
>
> timer1.Enabled = true;
>
> }
>
> private void timer1_Tick(object sender, System.EventArgs e)
>
> {
>
> for (int i = 0; i < iterateCount; ++i)
>
> {
>
> ++countTick;
>
> com.CommandText = "Insert into [tableName] ([columnName]) VALUES ('K" +
> countTick.ToString() + "')";
>
> com.ExecuteNonQuery();
>
> }
>
> textBox2.Text = "Record Count = " + countTick.ToString();
>
>
> }
>
> private void button3_Click(object sender, System.EventArgs e)
>
> {
>
> try
>
> {
>
> timer1.Enabled = false;
>
> com.Transaction.Commit();
>
> con.Close();
>
> com.Dispose();
>
> }
>
> catch(Exception ex)
>
> {
>
> MessageBox.Show("Error: " + ex.ToString());
>
> }
>
> }
>
> private void button2_Click(object sender, System.EventArgs e)
>
> {
>
> try
>
> {
>
> timer1.Enabled = false;
>
> com.Transaction.Rollback();
>
> con.Close();
>
> com.Dispose();
>
> }
>
> catch(Exception ex)
>
> {
>
> MessageBox.Show("Error: " + ex.ToString());
>
> }
>
> }
>
> private void button4_Click(object sender, System.EventArgs e)
>
> {
>
> try
>
> {
>
> timer1.Enabled = false;
>
> com.CommandText = "Rollback Transaction";
>
> com.ExecuteNonQuery();
>
> con.Close();
>
> com.Dispose();
>
> }
>
> catch(Exception ex)
>
> {
>
> MessageBox.Show("Error: " + ex.ToString());
>
> }
>
>
> }
>
> }
>
> }
>
>

Miha
Thu Dec 11 03:16:19 CST 2003

Hi Angel,

"Angel Saenz-Badillos[MS]" <angelsa@online.microsoft.com> wrote in message
news:OsNWLd5vDHA.3196@TK2MSFTNGP11.phx.gbl...
> Chris,
> Good stuff! thanks for the repro code I will set this up locally. Like
Miha
> said it looks like we are waiting in the client from something from the
> server to let us know the rollback happened, this is taking more than 30
> seconds so we throw a timeout. I have no idea what we could be waiting for
> since Rollback does not return anything, sounds like a bug.

Yes, there is nothing to wait for. However, I think that rollback is just a
normal command and the framework waits (because the command isn't async)
until rollback finishes which is correct behaviour.
IMO it isn't really a bug - it is just that connection lacks of
CommandTimeout property for Transaction related stataments.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com

Chris
Thu Dec 11 09:50:17 CST 2003


"Miha Markic" <miha at rthand com> wrote in message
news:edFfxd8vDHA.3416@tk2msftngp13.phx.gbl...
> Hi Angel,
>
> "Angel Saenz-Badillos[MS]" <angelsa@online.microsoft.com> wrote in message
> news:OsNWLd5vDHA.3196@TK2MSFTNGP11.phx.gbl...
> > Chris,
> > Good stuff! thanks for the repro code I will set this up locally. Like
> Miha
> > said it looks like we are waiting in the client from something from the
> > server to let us know the rollback happened, this is taking more than 30
> > seconds so we throw a timeout. I have no idea what we could be waiting
for
> > since Rollback does not return anything, sounds like a bug.
>
> Yes, there is nothing to wait for. However, I think that rollback is just
a
> normal command and the framework waits (because the command isn't async)
> until rollback finishes which is correct behaviour.
> IMO it isn't really a bug - it is just that connection lacks of
> CommandTimeout property for Transaction related stataments.
>
> --
> Miha Markic - RightHand .NET consulting & software development
> miha at rthand com
>
>

Thanks for your help Miha. Yes it probably isn't a bug, in that the
rolllback still takes place. It just gives an appearance of a bug,
especially to clients :-(
So it is useful to have an update available with no great effort :-)

Chris

Angel
Thu Dec 11 12:25:56 CST 2003

Hello Miha, first time we post on the same thread but I have to say that you
are doing a fantastic job on this newsgroup! Thank you.

I believe that we are hardcoding the transaction timeout to the Connection
Timeout (default 15 seconds), so adding "connect timeout=<your value>" to
the connection string should allow you to work around this problem. Looking
at the problem I now believe that the reason we are waiting for the server
is to find out if an exception is thrown, we may not be able to do anything
about this without introducing a breaking change.

Chris, using the BeginTransaction api call and then using ExecuteNonQuery to
roll back the transaction may not be the best solution, the problem is that
we are keeping track of the transaction. When you call BeginTransaction we
up a counter, when you close the connection we believe that a transaction is
still active and attempt to clean up after it. This actually forces an extra
roundtrip to the server on close for SqlClient. Either use the api calls for
both begin and rollback or (probably better for your scenario) use the
ExecuteNonQuery for both begin and rollback.

Hope this helps,
--
Angel Saenz-Badillos [MS] Managed Providers
This posting is provided "AS IS", with no warranties, and confers no
rights.Please do not send email directly to this alias.
This alias is for newsgroup purposes only.

"Chris B" <Chris.B@nospam.com> wrote in message
news:#uxjm4$vDHA.2340@TK2MSFTNGP12.phx.gbl...
>
> "Miha Markic" <miha at rthand com> wrote in message
> news:edFfxd8vDHA.3416@tk2msftngp13.phx.gbl...
> > Hi Angel,
> >
> > "Angel Saenz-Badillos[MS]" <angelsa@online.microsoft.com> wrote in
message
> > news:OsNWLd5vDHA.3196@TK2MSFTNGP11.phx.gbl...
> > > Chris,
> > > Good stuff! thanks for the repro code I will set this up locally. Like
> > Miha
> > > said it looks like we are waiting in the client from something from
the
> > > server to let us know the rollback happened, this is taking more than
30
> > > seconds so we throw a timeout. I have no idea what we could be waiting
> for
> > > since Rollback does not return anything, sounds like a bug.
> >
> > Yes, there is nothing to wait for. However, I think that rollback is
just
> a
> > normal command and the framework waits (because the command isn't async)
> > until rollback finishes which is correct behaviour.
> > IMO it isn't really a bug - it is just that connection lacks of
> > CommandTimeout property for Transaction related stataments.
> >
> > --
> > Miha Markic - RightHand .NET consulting & software development
> > miha at rthand com
> >
> >
>
> Thanks for your help Miha. Yes it probably isn't a bug, in that the
> rolllback still takes place. It just gives an appearance of a bug,
> especially to clients :-(
> So it is useful to have an update available with no great effort :-)
>
> Chris
>
>
>