Rollback Transaction Timing Out Framework 1.0 SQL Server 2000

[Chris B]

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 Markic]

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.

 

[Chris B]

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 B]

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 Markic]

Hi Chris,

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 ...

 

[Chris B]

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 Markic]

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.

 

[Chris B]

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 Saenz-Badillos[MS]]

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.

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 Markic]

Hi Angel,

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.

 

[Chris B]

Hi Angel,



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.

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 Saenz-Badillos[MS]]

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,