Connection timeout when using transactions

  • Thread starter Thread starter Natan Vivo
  • Start date Start date
N

Natan Vivo

I'm getting a timeout error while trying to select data from SQL server
2005 after opening a transaction in asn asp.net application.

It goes like this:

SqlConnection conn1 = new SqlConnection(...);
conn1.Open();
SqlTransaction trans = conn1.Open();

// do some inserts and updates using the transaction

SqlConnection conn2 = new SqlConnection(...);
conn2.Open();

// select using conn2 and decide what to do

conn2.Close();

// do more inserts and updates based on that decision

trans.Commit();
conn1.Commit();

It probably has something to do with the connection pool returning the
already open conn1 to connection2.

In the actual process, the select using the 2nd connection is being
opened in another method, and I don't want to overload the method to
receive the transaction.

Is there any other option? Is there any way to really open another
connection, or the connection pool to not return open connections being
used in transactions?

Thanks.
 
Natan said:
I'm getting a timeout error while trying to select data from SQL server
2005 after opening a transaction in asn asp.net application.

It goes like this:

SqlConnection conn1 = new SqlConnection(...);
conn1.Open();
SqlTransaction trans = conn1.Open();

// do some inserts and updates using the transaction

SqlConnection conn2 = new SqlConnection(...);
conn2.Open();

// select using conn2 and decide what to do

conn2.Close();

// do more inserts and updates based on that decision

trans.Commit();
conn1.Commit();

It probably has something to do with the connection pool returning the
already open conn1 to connection2.

In the actual process, the select using the 2nd connection is being
opened in another method, and I don't want to overload the method to
receive the transaction.

Is there any other option? Is there any way to really open another
connection, or the connection pool to not return open connections being
used in transactions?

Thanks.
Click to expand...

Have you changed the default CommandTimeout from the default? I think
the default for ASP/ASP.NET is 30 seconds.
 
Tracy said:
Have you changed the default CommandTimeout from the default? I think
the default for ASP/ASP.NET is 30 seconds.
Click to expand...

No, and it is not the problem. The whole process takes less than 1 second.

The thing is exactly what I told. If I open an second connection and
select something, while connection one is in transaction, it hangs and
gives me a connection timeout.

Can anyone reproduce this?
 
Natan said:
No, and it is not the problem. The whole process takes less than 1 second.

The thing is exactly what I told. If I open an second connection and
select something, while connection one is in transaction, it hangs and
gives me a connection timeout.

Can anyone reproduce this?
Click to expand...

Not knowing what selects you're running in connection 2, I'd have to say
you're probably deadlocking against the open transaction in connection 1.

http://realsqlguy.com/twiki/bin/view/RealSQLGuy/SimulatingADeadlock
 
Natan said:
No, and it is not the problem. The whole process takes less than 1 second.

The thing is exactly what I told. If I open an second connection and
select something, while connection one is in transaction, it hangs and
gives me a connection timeout.

Can anyone reproduce this?
Click to expand...

Yes, I had the exact same problem in an application of mine. In the
absence of finding a solution, I coded around it. Sorry I can't be of
more help, but you're not alone. :)

Neil B
 
Natan said:
I'm getting a timeout error while trying to select data from SQL server
2005 after opening a transaction in asn asp.net application.

It goes like this:

SqlConnection conn1 = new SqlConnection(...);
conn1.Open();
SqlTransaction trans = conn1.Open();

// do some inserts and updates using the transaction

SqlConnection conn2 = new SqlConnection(...);
conn2.Open();

// select using conn2 and decide what to do

conn2.Close();

// do more inserts and updates based on that decision

trans.Commit();
conn1.Commit();

It probably has something to do with the connection pool returning the
already open conn1 to connection2.
Click to expand...
No, that's not how the connection pool works. If you have two open
connections in your code, you'll be using two connections from the
connection pool.
In the actual process, the select using the 2nd connection is being
opened in another method, and I don't want to overload the method to
receive the transaction.

Is there any other option? Is there any way to really open another
connection, or the connection pool to not return open connections being
used in transactions?

From your description, it's almost certainly a deadlock on sql server.
Click to expand...
Without seeing the database code, it's going to be difficult to help
you out.

To confirm it's a deadlock, open a connection using query analyser,
start your code running, then run sp_who2. It'll show your second
connection being blocked by your first.

Damien
 
Tracy said:
Not knowing what selects you're running in connection 2, I'd have to say
you're probably deadlocking against the open transaction in connection 1.

http://realsqlguy.com/twiki/bin/view/RealSQLGuy/SimulatingADeadlock
Click to expand...

Thanks. It was really a deadlock, the select was selecting data from the
same table I had already update.

It seems I can solve this by using the right IsolationLevel when
begining the transaction.

I'll test it later. Thanks!
 
Natan said:
Thanks. It was really a deadlock, the select was selecting data from the
same table I had already update.

It seems I can solve this by using the right IsolationLevel when
begining the transaction.

I'll test it later. Thanks!
Click to expand...

Be careful that you're not reading "dirty" data, meaning data that is
inconsistent because it's been partially altered by a transaction...
 
Back
Top