Random Timeout Exceptions

  • Thread starter Thread starter Yuriy Solodkyy
  • Start date Start date
Y

Yuriy Solodkyy

Hi,

the following problem was raised several times in newsgroups, but I still
cannot find suitable solution.
Let say we have the following stored procedure which returns many records.
(this is only a sample, it does not do
anything meaningful)

-- ================================

create procedure a as begin

declare @a table (a int)

declare @i int set @i = 0


while @i < 11 begin
insert into @a(a) values (@i)
insert into @a select a from @a
set @i = @i + 1
end

select a.a from @a a , @a b

end
-- ================================

I have created it in pubs DB. If you run it in query analyzer you will get
results soon.

If you run the follwing C# program you also get it completed soon.
(You may have to change connection string.)

////////////////////////////////
using System;
using System.Data.SqlClient;

namespace TestApp {
class TestApp {

[STAThread]
static void Main(string[] args) {
using (SqlConnection c = new SqlConnection(@"Integrated
Security=SSPI;Initial Catalog=pubs;Data Source=localhost;")) {
c.Open();
using (SqlCommand m = new SqlCommand(@"execute a", c)) {
m.CommandTimeout = 0;
using (SqlDataReader r =
m.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) {
int i = 0;
for (;;) {
while (r.Read()) {
i++;
if ((i % 123456) == 0) Console.Write(".");
}
if (!r.NextResult()) break;
};
}
}
}
Console.WriteLine("completed!");
}

}
}
///////////////////////////

However, if you start two instances of this code in two simultaniously
running threads you randomly get exceptions
with message: "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is
not responding."

The code below illustrates this. It starts 2 threads. It is not enough to
get error on some computers, so increase
thread count to 3 or 4. If you have hypethreaded computer ot multiprocessor
system a chance of getting error is
higher.

"*" in application output means catching this exception.

////////////////////////
using System;
using System.Data.SqlClient;

namespace TestApp {
class TestApp {

[STAThread]
static void Main(string[] args) {
System.Threading.Thread t;
for(int j = 0; j < 2; j++) {
t = new System.Threading.Thread(new
System.Threading.ThreadStart(ThreadProc));
t.Start();
}
}

public static void ThreadProc() {
// repeats reading..
while (true) {
DoTest();
}
}

public static void DoTest() {
try {
using (SqlConnection c = new SqlConnection(@"Integrated
Security=SSPI;Initial Catalog=pubs;Data Source=localhost;")) {
c.Open();
using (SqlCommand m = new SqlCommand(@"execute a", c)) {
m.CommandTimeout = 0;
using (SqlDataReader r =
m.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) {
int i = 0;
for (;;) {
while (r.Read()) {
i++;
if ((i % 123456) == 0) Console.Write(".");
}
if (!r.NextResult()) break;
};
}
}
}
Console.WriteLine("completed!");
}
catch (Exception e){
if (e.Message == "Timeout expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.") {
Console.Write("*");
}
else {
Console.WriteLine("*************" + e.Message);
Console.WriteLine(e.StackTrace);
}
}
}


}
}

//////////////////////////////////

Usual output is:

................................**...........................................
.....
......................................................................*.*....
.....
.............................................................................
.....
............................................................*.....*..........
.....
.............................................................................
.....
.............................................................................
.....
...........................................*.................................
.....
...................completed!
.....................................................*.......................
.....
.............................*...............................................
.....
.............................................................................
.....
......................................................

Here "completed!" means that thread was able to read all data without
exception. I haven't seen this message on
hyperthread systems.
Does anybody know how to avoid getting this exception?

Thank you in advance,
Yuriy
 
It seems that the problem is actual only for some software configurations.
There are severs where it always fails and where it works fine.

Yuriy Solodkyy said:
Hi,

the following problem was raised several times in newsgroups, but I still
cannot find suitable solution.
Let say we have the following stored procedure which returns many records.
(this is only a sample, it does not do
anything meaningful)

-- ================================

create procedure a as begin

declare @a table (a int)

declare @i int set @i = 0


while @i < 11 begin
insert into @a(a) values (@i)
insert into @a select a from @a
set @i = @i + 1
end

select a.a from @a a , @a b

end
-- ================================

I have created it in pubs DB. If you run it in query analyzer you will get
results soon.

If you run the follwing C# program you also get it completed soon.
(You may have to change connection string.)

////////////////////////////////
using System;
using System.Data.SqlClient;

namespace TestApp {
class TestApp {

[STAThread]
static void Main(string[] args) {
using (SqlConnection c = new SqlConnection(@"Integrated
Security=SSPI;Initial Catalog=pubs;Data Source=localhost;")) {
c.Open();
using (SqlCommand m = new SqlCommand(@"execute a", c)) {
m.CommandTimeout = 0;
using (SqlDataReader r =
m.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) {
int i = 0;
for (;;) {
while (r.Read()) {
i++;
if ((i % 123456) == 0) Console.Write(".");
}
if (!r.NextResult()) break;
};
}
}
}
Console.WriteLine("completed!");
}

}
}
///////////////////////////

However, if you start two instances of this code in two simultaniously
running threads you randomly get exceptions
with message: "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is
not responding."

The code below illustrates this. It starts 2 threads. It is not enough to
get error on some computers, so increase
thread count to 3 or 4. If you have hypethreaded computer ot multiprocessor
system a chance of getting error is
higher.

"*" in application output means catching this exception.

////////////////////////
using System;
using System.Data.SqlClient;

namespace TestApp {
class TestApp {

[STAThread]
static void Main(string[] args) {
System.Threading.Thread t;
for(int j = 0; j < 2; j++) {
t = new System.Threading.Thread(new
System.Threading.ThreadStart(ThreadProc));
t.Start();
}
}

public static void ThreadProc() {
// repeats reading..
while (true) {
DoTest();
}
}

public static void DoTest() {
try {
using (SqlConnection c = new SqlConnection(@"Integrated
Security=SSPI;Initial Catalog=pubs;Data Source=localhost;")) {
c.Open();
using (SqlCommand m = new SqlCommand(@"execute a", c)) {
m.CommandTimeout = 0;
using (SqlDataReader r =
m.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) {
int i = 0;
for (;;) {
while (r.Read()) {
i++;
if ((i % 123456) == 0) Console.Write(".");
}
if (!r.NextResult()) break;
};
}
}
}
Console.WriteLine("completed!");
}
catch (Exception e){
if (e.Message == "Timeout expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.") {
Console.Write("*");
}
else {
Console.WriteLine("*************" + e.Message);
Console.WriteLine(e.StackTrace);
}
}
}


}
}

//////////////////////////////////

Usual output is:

................................**...........................................
....
Click to expand...
......................................................................*.*....
....
Click to expand...
.............................................................................
....
Click to expand...
............................................................*.....*..........
....
Click to expand...
.............................................................................
....
Click to expand...
.............................................................................
....
Click to expand...
...........................................*.................................
....
..................completed!
Click to expand...
.....................................................*.......................
....
Click to expand...
.............................*...............................................
....
Click to expand...
.............................................................................
....
.....................................................

Here "completed!" means that thread was able to read all data without
exception. I haven't seen this message on
hyperthread systems.
Does anybody know how to avoid getting this exception?

Thank you in advance,
Yuriy
Click to expand...
 
Hi,

Can anybody explain why I get exceptions with second connection string, but
I do not get them with first?

Integrated Security=SSPI;Initial Catalog=pubs;Data Source=localhost
Integrated Security=SSPI;Initial Catalog=pubs;Data Source=COMPUTERNAME

Yuriy

Yuriy Solodkyy said:
It seems that the problem is actual only for some software configurations.
There are severs where it always fails and where it works fine.

Yuriy Solodkyy said:
Hi,

the following problem was raised several times in newsgroups, but I still
cannot find suitable solution.
Let say we have the following stored procedure which returns many records.
(this is only a sample, it does not do
anything meaningful)

-- ================================

create procedure a as begin

declare @a table (a int)

declare @i int set @i = 0


while @i < 11 begin
insert into @a(a) values (@i)
insert into @a select a from @a
set @i = @i + 1
end

select a.a from @a a , @a b

end
-- ================================

I have created it in pubs DB. If you run it in query analyzer you will get
results soon.

If you run the follwing C# program you also get it completed soon.
(You may have to change connection string.)

////////////////////////////////
using System;
using System.Data.SqlClient;

namespace TestApp {
class TestApp {

[STAThread]
static void Main(string[] args) {
using (SqlConnection c = new SqlConnection(@"Integrated
Security=SSPI;Initial Catalog=pubs;Data Source=localhost;")) {
c.Open();
using (SqlCommand m = new SqlCommand(@"execute a", c)) {
m.CommandTimeout = 0;
using (SqlDataReader r =
m.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) {
int i = 0;
for (;;) {
while (r.Read()) {
i++;
if ((i % 123456) == 0) Console.Write(".");
}
if (!r.NextResult()) break;
};
}
}
}
Console.WriteLine("completed!");
}

}
}
///////////////////////////

However, if you start two instances of this code in two simultaniously
running threads you randomly get exceptions
with message: "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is
not responding."

The code below illustrates this. It starts 2 threads. It is not enough to
get error on some computers, so increase
thread count to 3 or 4. If you have hypethreaded computer ot multiprocessor
system a chance of getting error is
higher.

"*" in application output means catching this exception.

////////////////////////
using System;
using System.Data.SqlClient;

namespace TestApp {
class TestApp {

[STAThread]
static void Main(string[] args) {
System.Threading.Thread t;
for(int j = 0; j < 2; j++) {
t = new System.Threading.Thread(new
System.Threading.ThreadStart(ThreadProc));
t.Start();
}
}

public static void ThreadProc() {
// repeats reading..
while (true) {
DoTest();
}
}

public static void DoTest() {
try {
using (SqlConnection c = new SqlConnection(@"Integrated
Security=SSPI;Initial Catalog=pubs;Data Source=localhost;")) {
c.Open();
using (SqlCommand m = new SqlCommand(@"execute a", c)) {
m.CommandTimeout = 0;
using (SqlDataReader r =
m.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) {
int i = 0;
for (;;) {
while (r.Read()) {
i++;
if ((i % 123456) == 0) Console.Write(".");
}
if (!r.NextResult()) break;
};
}
}
}
Console.WriteLine("completed!");
}
catch (Exception e){
if (e.Message == "Timeout expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.") {
Console.Write("*");
}
else {
Console.WriteLine("*************" + e.Message);
Console.WriteLine(e.StackTrace);
}
}
}


}
}

//////////////////////////////////

Usual output is:
Click to expand...
................................**.................................................................................................................*.*.............................................................................................................................................*.....*...............................................................................................................................................................................................................*......................................................................................*....................................................*............................................................................................................................
....
.....................................................

Here "completed!" means that thread was able to read all data without
exception. I haven't seen this message on
hyperthread systems.
Does anybody know how to avoid getting this exception?

Thank you in advance,
Yuriy
Click to expand...
Click to expand...
 
Integrated Security=SSPI;Initial Catalog=pubs;Data Source=.

It does not work as well.

Is it a problem if SQL Server shared memory protocol or ADO.NET
implementation
?


Yuriy Solodkyy said:
Hi,

Can anybody explain why I get exceptions with second connection string, but
I do not get them with first?

Integrated Security=SSPI;Initial Catalog=pubs;Data Source=localhost
Integrated Security=SSPI;Initial Catalog=pubs;Data Source=COMPUTERNAME

Yuriy

Yuriy Solodkyy said:
It seems that the problem is actual only for some software configurations.
There are severs where it always fails and where it works fine.

Yuriy Solodkyy said:
Hi,

the following problem was raised several times in newsgroups, but I still
cannot find suitable solution.
Let say we have the following stored procedure which returns many records.
(this is only a sample, it does not do
anything meaningful)

-- ================================

create procedure a as begin

declare @a table (a int)

declare @i int set @i = 0


while @i < 11 begin
insert into @a(a) values (@i)
insert into @a select a from @a
set @i = @i + 1
end

select a.a from @a a , @a b

end
-- ================================

I have created it in pubs DB. If you run it in query analyzer you will get
results soon.

If you run the follwing C# program you also get it completed soon.
(You may have to change connection string.)

////////////////////////////////
using System;
using System.Data.SqlClient;

namespace TestApp {
class TestApp {

[STAThread]
static void Main(string[] args) {
using (SqlConnection c = new SqlConnection(@"Integrated
Security=SSPI;Initial Catalog=pubs;Data Source=localhost;")) {
c.Open();
using (SqlCommand m = new SqlCommand(@"execute a", c)) {
m.CommandTimeout = 0;
using (SqlDataReader r =
m.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) {
int i = 0;
for (;;) {
while (r.Read()) {
i++;
if ((i % 123456) == 0) Console.Write(".");
}
if (!r.NextResult()) break;
};
}
}
}
Console.WriteLine("completed!");
}

}
}
///////////////////////////

However, if you start two instances of this code in two simultaniously
running threads you randomly get exceptions
with message: "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is
not responding."

The code below illustrates this. It starts 2 threads. It is not
Click to expand...
Click to expand...
enough
to
get error on some computers, so increase
thread count to 3 or 4. If you have hypethreaded computer ot multiprocessor
system a chance of getting error is
higher.

"*" in application output means catching this exception.

////////////////////////
using System;
using System.Data.SqlClient;

namespace TestApp {
class TestApp {

[STAThread]
static void Main(string[] args) {
System.Threading.Thread t;
for(int j = 0; j < 2; j++) {
t = new System.Threading.Thread(new
System.Threading.ThreadStart(ThreadProc));
t.Start();
}
}

public static void ThreadProc() {
// repeats reading..
while (true) {
DoTest();
}
}

public static void DoTest() {
try {
using (SqlConnection c = new SqlConnection(@"Integrated
Security=SSPI;Initial Catalog=pubs;Data Source=localhost;")) {
c.Open();
using (SqlCommand m = new SqlCommand(@"execute a", c)) {
m.CommandTimeout = 0;
using (SqlDataReader r =
m.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) {
int i = 0;
for (;;) {
while (r.Read()) {
i++;
if ((i % 123456) == 0) Console.Write(".");
}
if (!r.NextResult()) break;
};
}
}
}
Console.WriteLine("completed!");
}
catch (Exception e){
if (e.Message == "Timeout expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.") {
Console.Write("*");
}
else {
Console.WriteLine("*************" + e.Message);
Console.WriteLine(e.StackTrace);
}
}
}


}
}

//////////////////////////////////

Usual output is:
Click to expand...
Click to expand...
................................**.................................................................................................................*.*.............................................................................................................................................*.....*...............................................................................................................................................................................................................*......................................................................................*....................................................*............................................................................................................................
Click to expand...
 
Back
Top